Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
TABLE1 COLUMNS(ID; NAME; AGE; DOB; CDE; CODE1; CODE2)

TABLE2 COLUMNS(CDE; DESCRIPTION)

TABLE3 COLUMNS(CDE; DESCRIPTION)

SELECT TABLE1.NAME, TABLE1.AGE, TABLE2.DESCRIPTION,
CASE WHEN TABLE1.CODE1 = NULL THEN NULL
ELSE (SELECT TABLE3.DESCRIPTION FROM TABLE3 WHERE TABLE1.CODE1 = TABLE3.CDE)
END CODE1,
CASE WHEN TABLE1.CODE2 = NULL THEN NULL
ELSE (SELECT TABLE3.DESCRIPTIONFROM TABLE3 WHERE TABLE1.CODE2 = TABLE3.CDE)
END CODE2
FROM TABLE1,TABLE2
WHERE TABLE1.cde = TABLE2.cde AND (TABLE1.ID = :ID)
Posted
Updated 3-Mar-15 13:19pm
v4
Comments
PIEBALDconsult 3-Mar-15 16:34pm    
I would never put a SELECT within a CASE. Try a JOIN.

1 solution

Doesn't have anything to do with the Case statement, you haven't supplied the value for the Bind variable :ID in the last row.

And Piebald is right, you'd probably do better with some left outer joins
SQL
SELECT  TABLE1.NAME
       ,TABLE1.AGE
       ,TABLE2.DESCRIPTION
       ,T1.DESCRIPTION
       ,T2.DESCRIPTION
FROM    TABLE1
JOIN    TABLE2
    ON  TABLE1.cde = TABLE2.cde
left OUTER JOIN TABLE3 t1
    ON  TABLE1.CODE1 = T1.CDE
left OUTER JOIN TABLE3 t2
    ON  TABLE1.CODE2 = T2.CDE
WHERE   TABLE1.ID = :ID
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900