CODEWITHSUNDEEP
sqloracle-databaseora-00933
Amandeep
Amandeep

Reputation: 47

Join with table and sub query in oracle

I don't understand what is wrong with this query. It is giving me a compile time error of "command not ended properly".

The inner query is giving me four records.

select WGN3EVENTPARTICIPANT.EVENTUID 
from 
    (Select WGN_V_ADDRESS_1.ADDRESSUID1 as add1,
            WGN_V_ADDRESS_1.ADDRESSUID2 as add2 
     from WGN3USER inner join 
          WGN_V_ADDRESS_1 on WGN_V_ADDRESS_1.USERID=wgn3user.USERID 
      where WGN3USER.USERNAME='FIRMWIDE\khuraj'
    ) as ta
    ,WGN3EVENTPARTICIPANT 
 where (ta.ADDRESSUID1=WGN3EVENTPARTICIPANT.ADDRESSUID1) AND    
       (ta.ADDRESSUID2=WGN3EVENTPARTICIPANT.ADDRESSUID2)

I am running it in Oracle.

Upvotes: 0

Views: 1781

Answers (4)

dani herrera
dani herrera

Reputation: 51665

You should change where clause to use fields alias:

 where (ta.add1=WGN3EVENTPARTICIPANT.ADDRESSUID1) AND    
       (ta.add2=WGN3EVENTPARTICIPANT.ADDRESSUID2)

EDITED

Try to remove as reserved word in table aliases:

) as ta -> ) ta

Upvotes: 1

Vikram Jain
Vikram Jain

Reputation: 5588

select WGN3EVENTPARTICIPANT.EVENTUID 
from 
    (Select WGN_V_ADDRESS_1.ADDRESSUID1 as add1,
            WGN_V_ADDRESS_1.ADDRESSUID2 as add2 
     from WGN3USER inner join 
          WGN_V_ADDRESS_1 on WGN_V_ADDRESS_1.USERID=wgn3user.USERID 
      where WGN3USER.USERNAME='FIRMWIDE\khuraj'
    ) as ta
    left outer join WGN3EVENTPARTICIPANT on 
 ta.ADDRESSUID1=WGN3EVENTPARTICIPANT.ADDRESSUID1 AND
       ta.ADDRESSUID2=WGN3EVENTPARTICIPANT.ADDRESSUID2

Upvotes: 0

APC
APC

Reputation: 146239

As others have noted, your original code sample contains several bugs. However the one which causes the specific error you're reporting (ORA-00933) is this.

In Oracle AS is only used for column aliases. So you need to alias your inline view like this:

... where WGN3USER.USERNAME='FIRMWIDE\khuraj'
    )  ta

Upvotes: 1

diederikh
diederikh

Reputation: 25271

'\' is an escape character which needs to be escaped:

Change 'FIRMWIDE\khuraj' in 'FIRMWIDE\\khuraj'

And the outer where-clause references the wrong aliases: ta.ADDRESSUID1 instead of ta.add1 and ta.ADDRESSUID2 instead of ta.add2

Upvotes: 0

Related Questions