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

CREATE TABLE test (
    e_id        NUMBER(10),
    col_id      NUMBER(10),
    col_ans     VARCHAR2(30),
    col_pos_id  NUMBER(10)
);

INSERT INTO test VALUES(1,7,null,29);
INSERT INTO test VALUES(1,45,'test',30);
INSERT INTO test VALUES(1,13,'test1',31);
INSERT INTO test VALUES(1,14,'test2',32);
INSERT INTO test VALUES(1,15,'test3',33);

--reference table
CREATE TABLE lookup_table (
    col_id     NUMBER(10),
    col_value  VARCHAR2(20)
);
INSERT INTO lookup_table VALUES(29,'demo');

DB version: Oracle 18c

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:

+------+-------------+--+
| e_id |   output    |  |
+------+-------------+--+
|    1 | demo - test |  |
+------+-------------+--+

My attempt:-

SELECT t.e_id,
CASE 
WHEN t.col_id = 7 AND t.col_pos_id= 29 THEN 
rt.col_value|| '-'|| (SELECT col_ans FROM test WHERE t.col_id = 45)
ELSE
rt.col_value
END output
FROM test t
JOIN lookup_table rt ON(rt.col_id = t.col_pos_id AND t.col_id = 7 );

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

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:

SELECT t.e_id,
       CASE 
       WHEN t.col_pos_id= 29
         -- AND t.col_id = 7 /* will always be true due to the JOIN condition */
       THEN rt.col_value || '-' || (SELECT col_ans FROM test x WHERE x.col_id = 45)
       ELSE rt.col_value
       END AS output
FROM   test t
       INNER JOIN lookup_table rt
       ON (rt.col_id = t.col_pos_id AND t.col_id = 7 );

or:

SELECT t.e_id,
       rt.col_value || x.col_ans AS output
FROM   test t
       INNER JOIN lookup_table rt
       ON (rt.col_id = t.col_pos_id AND t.col_id = 7 )
       LEFT OUTER JOIN (SELECT '-' || col_ans AS col_ans FROM test WHERE col_id = 45) x
       ON (t.col_pos_id= 29);

Which, for the sample data, both output:

E_ID	OUTPUT
1	demo-test

db<>fiddle here