two right outer joins

[sqluser999]

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,

[tamilselvan]

You don't need OUTER JOIN on T3
because of these filter
T3.CUSTACCT = 'abc123' AND
T3.ORDER_STATUS = 1
Tamil