Skip to content
Advertisement

In case expression else part is not getting executed and giving wrong output

I have two tables one for transactional and the other one is a reference table. I need to get the output based on columns col_ans and col_pos_id and need to fetch the value from the reference table as per the below requirement.

I need to check col_id equals 7 only. And all operation needs to happen on this col_id only. In my dataset suppose for e_id 1 we have col_id as 7 (and this 7 will always be there) then need to check col_pos_id if this is 29 then need to fetch the value for this id from the reference table and concatenate another value with the logic as if col_id is 45 then need to fetch col_ans column value ELSE need to print only the col_pos_id value.

Expected Output:

My attempt:-

Here, the only else part is getting executed and not getting the expected result.

Advertisement

Answer

The issue is due to scoping the variables. In the sub-query nested inside the CASE expression, you use (SELECT col_ans FROM test WHERE t.col_id = 45) and t.col_id refers to the t alias from the outer query where the only matching row has a col_id value of 7 and so 7 = 45 is false and the nested sub-query will never match any rows. What you need to do is either remove the t. from that sub-query so that the col_id is from the local scope or give the sub-query its own alias and use that:

or:

Which, for the sample data, both output:

E_ID OUTPUT
1 demo-test

db<>fiddle here

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement