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 someone please let me know, how to write this statement in case expression.