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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 USING CONDITIONAL JOIN?

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)
Go to Top of Page

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.
Go to Top of Page

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.
---- 1
IF (@INCLUDE_NORUNS@ = 'Y')
BEGIN
-- entire query that uses left join starting with select here
END
ELSE
BEGIN
-- entire query that uses inner join starting with select here
END

--- 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'
);
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -