Hi,
It depends on the type value from the left join of tbl5 to determine which table needs to be joined. The syntax I have keeps generating an error from the case statement, but I am unsure why. Could you please assist me with this syntax
left join (select distinct tie,doc,order,type from mytb5) tbl5 ON cis.tie = ca.tie and tie.doc = ca.doc
left join
case
when tbl5.type =10 then mytb1 tbl ON tb1.order = ca.order
when tbl5.type =50 then mytb2 tb2 ON wh.order = ca.order
end
The table name cannot be dynamic in SQL Server.
You would instead need to do something like this:
LEFT OUTER JOIN mytb1 tb1 ON tbl5.type = 10 AND tb1.order = ca.order
LEFT OUTER JOIN mytb2 tb1 ON tbl5.type = 50 AND tb2.order = ca.order
Hi,
Thank you for your response. Okay, I have removed the dynamic query. What is the correct syntax for the case statement here?
Since you're doing LEFT joins, you don't need a CASE. The "type = " condition in the JOIN will make sure you only get a match is one is appropriate for the type value in the tbl5 row.