How to use Case or If statement in Inner Join ?

Hi All,

I am in need for to write the following query for a stored procedure in SQL Server 2000. Please observe the T-SQL code first.
Please disregard the numbers and Product Numbers, they are not the correct data. I used them for ease of understanding. But the query is identical.

Code Block

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
CASE WHEN @.vchSubscription = 'Weekly' THEN
INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' ) -- Category and SubCategory
OR PM.chProductNumber IN (
'weekly1', 'Weekly2', 'Weekly3', 'Weekly4' )
)
AND C.dtInsertDate > = @.dtIssueDate

CASE WHEN @.vchSubscription = 'Monthly' THEN

INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' )
OR PM.chProductNumber IN (
'Jan', 'Feb', 'Mar', ....'Dec')
)
AND C.dtInsertDate > = @.dtIssueDate

END

GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8

my requirement is join the same table, but depending on the subscription type i have to join to different product numbers.
I hope you understand. I have been trying this since yesterday, but no luck.

Any help would be greatly appreciated.Maybe this:

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
INNER JOIN ProductMaster PM
ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' ) -- Category and SubCategory
OR
( @.vchSubscription = 'Weekly' AND PM.chProductNumber IN (
'weekly1', 'Weekly2', 'Weekly3', 'Weekly4' )
)
OR
( @.vchSubscription = 'Monthly' AND PM.chProductNumber IN (
'Jan', 'Feb', 'Mar', ....'Dec')
)
)
AND C.dtInsertDate > = @.dtIssueDate
GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8|||

MadhuBabu,

You can use different statements or dynamic sql. You can not use "case" to control program flow. It is a function, so it has to return something when used.

if @.vchSubscription = 'Weekly'

select ...

else

if @.vchSubscription = 'Monthly'

select ...

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB|||

Hi Dale J

Thank you very much for the answer. Well, just before 30 minutes I figured another less impressive method which is agreed on. following is the code for that

Code Block

DECLARE @.dtIssueDate DATETIME
SET @.dtIssueDate = '2007-10-01'

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' )
OR PM.chProductNumber IN (
'week1', 'week2', .. 'week4' ,
'Jan', 'Feb', ... , 'Dec'
)
)
AND PM.tiRecordStatus = 1
AND C.dtInsertDate > = @.dtIssueDate
GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8

/*
Remove Weekly Subscribers
*/
IF @.vchJournal = 'Monthly'
BEGIN
DELETE FROM #Tble WHERE iOwnerId IN (
SELECT DISTINCT C.iOwnerId FROM CustomerProduct C
INNER JOIN ProductMaster PM ON pm.chProductNumber = C.chProductNumber
AND PM.chProductNumber IN ('week1', 'week2', .., 'week4')
AND PM.tiRecordStatus = 1
AND C.dtInsertDate > = @.dtIssueDate
)
END
ELSE IF @.vchJournal = 'Weekly' /* Remove Monthly Subscribers */
BEGIN
DELETE FROM #Tble WHERE iOwnerId IN (
SELECT DISTINCT C.iOwnerId FROM CustomerProduct C
INNER JOIN ProductMaster PM ON pm.chProductNumber = C.chProductNumber
AND PM.chProductNumber IN ('Jan', 'Feb', ... , 'Dec')
AND PM.tiRecordStatus = 1
AND C.dtInsertDate > = @.dtIssueDate
)
END

But for sure, your code is more optimal, and am implementing it now...
Many Many Thanks.