Using a Case to decide on the join statement?

Hot Topic

Creating MS Access datasource
design of the classes of data access layer
Trigger on enable / disable SQL User
SSIS Tutorial - Package configuration problem
is it possible to have variant condition clause in procedure?
SQL Server 2005 incorrectly claims I don't meet minimum service pack level requirements?
DTS in SQL 2005 Standard
Debugging Custom Report Data Processing extension
MDAC 2.8 Fatal Setup Error. This setup does not support installing on this operating system
Plug-in algorithm documentation and sample code

SQL Server

save data selected in cube browser tab
sql server express loses ability to write to database
IDW15: Execute package using Execute Package Task
Data Mining - Scalar Mining Structure Column Data type error...
Reporting Services using IBM DB2 Data Provider for .NET framework 2.0
How do I set ApplicationName through ODBC call so that SQL Profiler can see it properly
Deleting Parameter From Preview Pain
When comes Sql26k?
Errors in the OLAP storage engine:
Cycling Through Subreports

Using a Case to decide on the join statement?

I'm trying to join a table and based on the value of a given column I would join using the column in question, however if the column is NULL then I want to make the join without the column in the join. so I think I want to do something like this:

Case E.a

when NULL

then LEFT JOIN EPD ON EPD.b = D.b

AND EPD.SD = (SELECT MAX(E1.SD) FROM E1

WHERE E1.b = EPD.b AND E1.a = EPD.a AND E1.SD <= T.WD)

Else

LEFT JOIN EPD

ON EPD.a = D.a

and EPD.b = E.b

AND EPD.SD = (SELECT MAX(E1.SD) FROM E1

WHERE E1.a = EPD.a AND E1.b = EPD.b AND

E1.SD <= T.WD)

end

however T-Sql does not seem to like my Case statement mixed into my From/join clauses.

Thanks,

Mark E. Johnson

Answer this question

Using a Case to decide on the join statement?

  • bigjimslade

    the best way I can think of is.

    LEFT JOIN EPD

    ON ISNULL(EPD.a, D.a) = D.a

    and EPD.b = E.b

    AND EPD.SD = (SELECT MAX(E1.SD) FROM E1

    WHERE E1.a = EPD.a AND E1.b = EPD.b AND

    E1.SD <= T.WD)



  • Koo Ofori

    CASE is not a control of flow statement it is an expression. So you cannot do conditional execution like this. You will have to either write different SELECT statements using IF for the control of flow or incorporate the conditions into the same SELECT statement using CASE expression in the predicates for example.

  • Andreas Haeusler

    I ended up using coalesce and the test column passing in the other variables for the join and made it one join instead of two.

    Thank you for the responses and I'll play with them as well to see if it will help me be more efficient.

    Thanks,

    Mark
  • Using a Case to decide on the join statement?
Answer this question