Access WITH..AS clause's column in WHERE clause in Oracle

user_1434397 0
Viewed 52

I have 2 tables :

Table_A

USERCODE|USERMOBILE|USERACCOUNT
001     |0123236556|ACC001
002     |0222222222|ACC002

Table_B

USERCODE|USERMOBILE
001     |0123236556
001     |0444444444
002     |0555555555

I want following output, which filter by Table_A.ACCOUNT

For example USERACCOUNT = ACC001

USERCODE|USERMOBILE
001     |0123236556
001     |0123236556
001     |0444444444

I try with following query but not work :

with tbl as
(select USERCODE,USERMOBILE from Table_A WHERE USERACCOUNT='ACC001' ) 
select * from tbl
union
select USERCODE,USERMOBILE from Table_B WHERE  Table_B.USERCODE = tbl.USERCODE

Error : Invalid identifier : tbl.USERCODE

Are there any way to achive my expected result?

common-table-expression union oracle
2 Answers

When you use UNION ALL can't refer to the 1st query from the 2nd query.
Use the CTE:

WITH cte(USERCODE, USERMOBILE) AS
(SELECT USERCODE, USERMOBILE FROM Table_A WHERE USERACCOUNT = 'ACC001')
SELECT USERCODE, USERMOBILE FROM cte
UNION ALL
SELECT USERCODE, USERMOBILE 
FROM Table_B 
WHERE USERCODE = (SELECT USERCODE FROM cte)

If the CTE may return more than 1 rows, change to:

WHERE USERCODE IN (SELECT USERCODE FROM cte)
user_104988280

There is no way to achieve the desired result you have to use Join -

SELECT USERCODE, USERMOBILE
  FROM Table_A
 WHERE USERACCOUNT='ACC001'
 UNION
SELECT USERCODE, USERMOBILE
  FROM Table_B B
  JOIN Table_A A ON B.USERCODE = A.USERCODE
user_36277560
Related