Please check the below query -
Query :
with sd as (
select 1 id, 'abc' col from dual
union
select 2 id, 'xyz' col from dual
union
select 3 id, 'pqr' col from dual
),
t as (
select 1 id, '123' col2 from dual
union
select 2 id, '233' col2 from dual
union
select 4 id, '456' col2 from dual
)
SELECT id,
col2,
(select 'ok' from dual where SD_EXIST='Y') CHECK_CON --,SD_EXIST
FROM (
select id,
col2,
case when exists(select 1 from sd where sd.id=t.id) then 'Y' ELSE NULL END AS SD_EXIST
from t
) ;
--------------------------------------------------------------------
There are 2 views defined - sd and t.
Final query inline View should give below :
ID COL2 SD_EXIST
1 123 Y
2 233 Y
4 456 -
But the output of final query CHECK_CON column changes
– if SD_EXIST is included in projection of select, CHECK_CON is null (for id 456) - Here the query behavior is working fine.
- if SD_EXIST is commented (not included in projection of select), CHECK_CON is ok (for id 456)- Here output is different and shows weird behavior of the query.
Can you explain the working here?
