Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Unanswered: Oracle 8 vs Oracle 9 Joins

    Just when I thought I was getting somewhere, I'm presented with an Oracle 8 feature that could very well cause me to have stroke.

    SQL that works great on Ora9:


    select bi.id from behvrl_indctrs bi left join prfcncs p on p.id = bi.prfcnc_id where p.id is null

    on Ora8

    ERROR at line 1:
    ORA-00933: SQL command not properly ended

    ----------

    I've grown so accustomed to leveraging the use of the LEFT join across most RDBMS system that this is a potential show-stopper in terms of our ability to deliver on Oracle 8.

    I also understand that joins can be done without the JOIN keyword(s); so my above statement would look something like

    select bi.id from behvrl_indctrs bi, prfcncs p WHERE p.id = bi.prfcnc_id ...

    it's that IS NULL part that has me swearing. I know this seems brain dead, but I can't remember for life of me the way around this.

    I'll mail a cookie to whoever can answer this.

    Paul

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: Oracle 8 vs Oracle 9 Joins

    Originally posted by skinny
    Just when I thought I was getting somewhere, I'm presented with an Oracle 8 feature that could very well cause me to have stroke.

    SQL that works great on Ora9:


    select bi.id from behvrl_indctrs bi left join prfcncs p on p.id = bi.prfcnc_id where p.id is null

    on Ora8

    ERROR at line 1:
    ORA-00933: SQL command not properly ended

    ----------

    I've grown so accustomed to leveraging the use of the LEFT join across most RDBMS system that this is a potential show-stopper in terms of our ability to deliver on Oracle 8.

    I also understand that joins can be done without the JOIN keyword(s); so my above statement would look something like

    select bi.id from behvrl_indctrs bi, prfcncs p WHERE p.id = bi.prfcnc_id ...

    it's that IS NULL part that has me swearing. I know this seems brain dead, but I can't remember for life of me the way around this.

    I'll mail a cookie to whoever can answer this.

    Paul
    I think you want this:

    Code:
    SELECT bi.id 
    from behvrl_indctrs bi, prfcncs p
    where p.id  = bi.prfcnc_id (+)
    Since PRFCNCS table has null values, I assume you want everything from the P table, and any matching BI values.

    If you read left to right, select all from P, and any matching from BI. Try it out and hope this was right - sometimes hard to visualize without testing so I hope I have the plus on the right side.

  3. #3
    Join Date
    Jan 2004
    Posts
    6
    Close but I need the opposite. What I'm looking for is the BEHVRL_INDCTRS that have no PRFCNCS record so I can delete them as to get the constraints to go on.

    This was a side effect of unconstrainted data (my fault, forgot to add the constraint) from MSSQL, and basically, I need to figure out which PRFCNCS.ID no longer exist without having to do them one at a time .

    In MSSQL, my original statement returned

    select bi.id, p.id from behvrl_indctrs bi left join prfcncs p on p.id = bi.prfcnc_id where p.id is null

    bi.id p.id
    265 <NULL>
    266 <NULL>

    ....

    then I'd do a

    DELETE FROM PRFCNCS where ID in (select bi.id, p.id from behvrl_indctrs bi left join prfcncs p on p.id = bi.prfcnc_id where p.id is null)


    and everyone was happy.


    Until Oracle 8


    Cookie is still up for grabs.

  4. #4
    Join Date
    Jan 2004
    Posts
    6
    Got it!

    Just a matter of fooling with the (+), which was an operator that I never used before.

    ss659 input was very useful:

    SELECT bi.id
    from behvrl_indctrs bi, prfcncs p
    where p.id = bi.prfcnc_id (+)


    to make it work


    SELECT bi.id
    from behvrl_indctrs bi, prfcncs p
    where p.id (+) = bi.prfcnc_id and p.id is null


    (I just like to update my requests for assistance with the solution, in case I need to reference it again)

    Now I have a mitt-full of SQL that needs changing.

    P


    p.s. SS659: you've earned a cookie: PM with your address and we'll ship one out.
    Last edited by skinny; 02-27-04 at 12:14.

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by skinny

    p.s. SS659: you've earned a cookie: PM with your address and we'll ship one out.
    Funny b/c I am so used to the Oracle 8 syntax, if I had to use the Oracle 9 join syntax Id be in the same boat.

    Make it thin-mints and you got a deal!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •