-
two right outer joins
I am having difficulty with the following query. I am at a client site and do not have admin priv.
I need to do two outer joins to get the non matching values from the tables. My base table is IV_BA_ETL_STAGE2 , and I have to verify information against the other two tables. If there isn't a match, i need to be able to track that row.
anyway here is the query
SELECT COUNT(*) FROM
((ETL_STAGE2 T1 RIGHT OUTER JOIN INTERFACES T2
ON
RTRIM (UPPER (T2.NAME )) = RTRIM (T1.TXT_COMPONENT ) AND
RTRIM (T1.TXT_HOSTNAME ) = UPPER (RTRIM (T2.DEVICE_NAME_____ )))
RIGHT OUTER JOIN SITES T3
ON
UPPER (T2.DEVICE) = UPPER (T3.DEVICE ))
WHERE
T3.CUSTACCT = 'abc123' AND
T3.ORDER_STATUS = 1
When executed from sqlplus , I get the following error message:
ERROR at line 6:
ORA-01114: IO error writing block to file 201 (block # 30676)
ORA-27063: skgfospo: number of bytes read/written is incorrect
Additional information: 98304
Additional information: 122880
ORA-01114: IO error writing block to file 201 (block # 30676)
ORA-27063: skgfospo: number of bytes read/written is incorrect
Additional information: 98304
Additional information: 122880
re-written, using an inner join on the second join, returns a value.
SELECT COUNT(*) FROM
((ETL_STAGE2 T1 RIGHT OUTER JOIN INTERFACES T2
ON
RTRIM (UPPER (T2.NAME )) = RTRIM (T1.TXT_COMPONENT ) AND
RTRIM (T1.TXT_HOSTNAME ) = UPPER (RTRIM (T2.DEVICE_NAME_____ )))
INNER JOIN SITES T3
ON
UPPER (T2.DEVICE) = UPPER (T3.DEVICE ))
WHERE
T3.CUSTACCT = 'abc123' AND
T3.ORDER_STATUS = 1
The result is:
COUNT(*)
----------
1581
Notice that the difference is that the failed query uses an OUTER JOIN whereas the successful query uses an INNER JOIN.
Can this query be re-written? is there anything I can do from a coding perspective?
Thanks,
-
You don't need OUTER JOIN on T3
because of these filter
T3.CUSTACCT = 'abc123' AND
T3.ORDER_STATUS = 1
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|