CODEWITHSUNDEEP
sqloracle-databasejoinsubqueryalias
Walentyna Juszkiewicz
Walentyna Juszkiewicz

Reputation: 197

Table alias is not recognized in subquery Oracle

I have SELECT statement with LEFT JOIN and joined tables are sub-queries. And Oracle could not recognize alias of the first sub-query in the second one. It works for DB2 but does not work for Oracle. How I can implement it or rewrite my query?

SELECT *                  
 FROM                                                      
 (SELECT     E.C3 AS  COLUMN3        
    , E.C4 AS  COLUMN4        
   FROM TBL_1 D                            
    , TBL_2 E                            
WHERE D.C6  =  E.C6 ) B                                                              
LEFT  JOIN  TABLE   
(SELECT  C.C1                          
 FROM    TBL_3  C                   
 WHERE                                                  
 C.C7            = 'hello'      
 AND B.C3            = C.C8                            
UNION ALL                                                 
 SELECT  C.C1                          
    FROM    TBL_3  C                
    WHERE                                               
    C.C7          = 'world'      
    AND B.C4          =  C.C8                       
    )            A               
ON 1 = 1

Oracle error message: ORA-00904: "B"."C3": invalid identifier

Upvotes: 0

Views: 1550

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

You have a syntax error. This:

select * from (select col1 from tbl_Ajoin) A
left join table (select col2 from tbl_Bjoin where A.col1 = tbl_Bjoin.col1) B     
ON 1 = 1

should be this:

select * from (select col1 from tbl_Ajoin) A
left join (select col2 from tbl_Bjoin where A.col1 = tbl_Bjoin.col1) B     
ON 1 = 1

or more specifically, this:

left join table (select

should not have the word table. It should be this:

left join (select

Upvotes: 1

Siyual
Siyual

Reputation: 16917

You can simplify this query to the following, removing the sub-queries:

Select      A.Col1, B.Col2
From        tbl_AJoin   A
Left Join   tbl_BJoin   B   On  A.col1 = B.col1

Upvotes: 3

Related Questions