Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Query returning no rows when I'm expecting 2

GregVFeb 8 2024

Hi,

I dont't know if I'm properly awaken but I'm expecting the following query to return 2 rows, but it's returning none.

with t(criteria_id, val)
    as (select 11, 123 from dual 
        union all
        select 22, 456 from dual
       ),
    v(rule_id, val, result)
    as (select 1, 123, 50 from dual
        union all
        select 2, 456, 100 from dual   
       )
select *
from t
join v on (t.criteria_id = 11 and v.rule_id = 1 and v.val = t.val)
join v on (t.criteria_id = 22 and v.rule_id = 2 and v.val = t.val)  ;

If I comment out each join individually I get each row. My DB version is 19.14 EE.

Am I missing something obvious here?

This post has been answered by BluShadow on Feb 8 2024
Jump to Answer
Comments
Post Details
Added on Feb 8 2024
5 comments
88 views