Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
planetoneautomation
Posting Yak Master
105 Posts |
Posted - 2012-11-14 : 17:57:55
|
I am getting input from the user when sql is executed. If input is 'Y' then I want to do an INNER JOIN.If input is not 'Y' then I want to do a LEFT JOIN.I've experimented with CASE / WHEN / THEN but can't get it to work.For example this:CASE WHEN @INCLUDE_NORUNS@ = 'Y' THEN INNER JOIN LINK AS LT ON LN_ENTITY_ID = ST_ID AND LN_ENTITY_TYPE = 'STEP' WHEN @INCLUDE_NORUNS@ <> 'Y' THEN LEFT JOIN LINK AS LT ON LN_ENTITY_ID = ST_ID AND LN_ENTITY_TYPE = 'STEP'END Gets "Invalid parameter bindings".Any ideas? |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-11-14 : 18:18:07
|
Without more detail I can think of two options:1. Use a flow control structure; and IF..ELSE..END with two queries: inner join and left join.2. Use a left join and then use the proper predicate to restrict the data appropriatly. (probably not the most performant) |
|
|
planetoneautomation
Posting Yak Master
105 Posts |
Posted - 2012-11-14 : 18:33:46
|
I've tried but I can't get it right syntactically (if it can be done at all in this context).For example:IF @INCLUDE_NORUNS@ = 'Y' THEN INNER JOIN LINK AS LT ON LN_ENTITY_ID = ST_ID AND LN_ENTITY_TYPE = 'STEP'ELSE IF @INCLUDE_NORUNS@ <> 'Y' THEN LEFT JOIN LINK AS LT ON LN_ENTITY_ID = ST_ID AND LN_ENTITY_TYPE = 'STEP'END IF Yield error: "Incorrect syntax near keyword THEN"I've read through dozens of posts on this and other forums and can't find anything like what I'm trying to do. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-14 : 19:39:24
|
What Lamprey meant is the two options shown below. SQL syntax does not let you use a case expression to choose the type of join as you are attempting to do.---- 1IF (@INCLUDE_NORUNS@ = 'Y')BEGIN -- entire query that uses left join starting with select hereENDELSEBEGIN -- entire query that uses inner join starting with select hereEND--- 2 ....FROM STTABLE ST LEFT JOIN LINK AS LT ON LT.LN_ENTITY_ID = ST.ST_ID AND LN.LN_ENTITY_TYPE = 'STEP'WHERE @INCLUDE_NORUNS@ = 'Y' OR ( LT.LN_ENTITY_ID = ST.ST_ID AND LN.LN_ENTITY_TYPE = 'STEP' ); |
|
|
planetoneautomation
Posting Yak Master
105 Posts |
Posted - 2012-11-15 : 07:27:22
|
Got it thank.Unfortunately, the query I'm building is using an HP Quality Center feature called Excel Reports that has a query builder that executes SELECTs and puts results in Excel.Because this feature is available to any old user, Quality Center disables certain features including the use of conditional logic along with things dangerous for a typical user to use such as INSERT, UPDATE, and DELETE.So, I'll have to have two queries - one that uses INNER and one that uses LEFT.But at least now I know how to do conditional logic if the occasion arises in the future so thanks. |
|
|
|
|
|