two right outer joins
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: two right outer joins

  1. #1
    Join Date
    May 2006
    Posts
    4

    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,

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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
  •  



Click Here to Expand Forum to Full Width