Select statement in Case statement in Oracle

Question

  SELECT (CASE WHEN T.ID  = ( SELECT cte.REFERENCE FROM trans cte WHERE T.ID  
 = CTE.PARENT_ID) THEN cte.REFERENCE ELSE null END) AS name 
 FROM   trans T

Example: I am picking one transaction value as an example. In trans table whose ID=1 then in the same table I need to look for PARENT_ID=1. when I look for parent_ID=1 then it's ID value will be different. This is not ID=Parent_ID. once I look for parent_ID=1 then print its corresponding reference value as name. I tried above sql statement in oracle, but it didn't work. Could you please let me know, how to write this statement in case statement.

Answer 1

Instead of a subquery, why not try a self-join?

SELECT CASE
         WHEN nvl(t1.id,-1) = nvl(t2.reference, -1) THEN t2.reference
         ELSE 1
       END     AS number_col
  FROM trans t LEFT JOIN trans t2 ON (t.id = t2.parent_id);

You can also try it as a subquery without a case statement

SELECT t.id,
       NVL ((SELECT t2.reference
               FROM trans t2
              WHERE t.id = t2.parent_id AND t.id = t2.reference AND ROWNUM = 1),
            1)    AS number_val
  FROM trans t