SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 USING CONDITIONAL JOIN?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

89 Posts
Posted - 11/14/2012 :  17:57:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3831 Posts
Posted - 11/14/2012 :  18:18:07  Show Profile  Reply with Quote
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
Yak Posting Veteran

89 Posts
Posted - 11/14/2012 :  18:33:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 11/14/2012 :  19:39:24  Show Profile  Reply with Quote
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
Yak Posting Veteran

89 Posts
Posted - 11/15/2012 :  07:27:22  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000