This works for me in SQL Server but in ORACLE the subquery does not return any rows, i guess its the binding to parent query part. Any thoughts of another way I could get the result I want?
Select T.idperson , CASE WHEN T.type = 'C' THEN (SELECT name from Customers where C.idcustomer = T.idperson) ELSE (SELECT name from Providers where idprovider = T.idperson) END Name from myTable T
Select T.idperson , CASE WHEN T.type = 'C' THEN c.name ELSE p.name END AS Name from myTable t LEFT OUTER JOIN Customers c ON c.idcustomer = T.idperson LEFT OUTER JOIN Providers p ON p.idprovider = T.idperson ;
user546629 wrote:Sure, but don't take my word for it: try it and see.
Can we use case in a subquery?