Join with table and sub query in oracle

Question

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.

APC · Accepted Answer · 2012-10-31 11:03:38Z

1

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

answered Oct 31, 2012 at 11:03

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dani herrera · Accepted Answer · 2012-10-31 11:27:23Z

1

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

edited Oct 31, 2012 at 11:27

answered Oct 31, 2012 at 10:59

dani herrera

52k11 gold badges133 silver badges188 bronze badges

Comments

diederikh · Accepted Answer · 2012-10-31 11:01:49Z

0

'\' 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

answered Oct 31, 2012 at 11:01

diederikh

25.3k5 gold badges39 silver badges50 bronze badges

1 Comment

dani herrera Over a year ago

but OP says that subquery runs ok ... ?

Vikram Jain · Accepted Answer · 2012-10-31 11:05:06Z

0

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

answered Oct 31, 2012 at 11:05

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Collectives™ on Stack Overflow

Join with table and sub query in oracle

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related