help with case statement on join criteria

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

SQL Server 2005 Forums

Transact-SQL (2005)

help with case statement on join criteria

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2010-09-06 : 14:07:00

Hi

I am trying to perform a join to basically say if the date is 28/12 or after, then join it to an FX rate to use either the same year's rate or the rate the following year (as we can take a 4 day difference)

Now I try to incorporate a CASE statement to deal with this but get an error, my case statement looks like this:

AND CASE
WHEN
DATEPART(MM,A.DATE) = '12' AND DATEPART(DD,A.DATE) >= '28'
THEN (DATEPART(YYYY,A.DATE) = C.RATE_YEAR OR DATEPART(YYYY,A.DATE) = C.RATE_YEAR + 1)
ELSE
DATEPART(YYYY,A.DATE) = C.RATE_YEAR
END

Error: Incorrect syntax near '='.

Can anybody tell me what is incorrect with the above?

Thanks!

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2010-09-07 : 05:57:49

Tried it again with:

AND (
CASE
WHEN DATEPART(MM,A.DATE) = 12 AND DATEPART(DD,A.DATE) >= 28 THEN DATEPART(YYYY,A.DATE) = C.RATE_YEAR
ELSE
DATEPART(YYYY,A.DATE) = C.RATE_YEAR
END
OR
CASE
WHEN DATEPART(MM,A.DATE) = 12 AND DATEPART(DD,A.DATE) >= 28 THEN DATEPART(YYYY,A.DATE) = C.RATE_YEAR + 1
ELSE
DATEPART(YYYY,A.DATE) = C.RATE_YEAR
END
)

but still get the same error (Error: Incorrect syntax near '='.)

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-09-07 : 07:04:22

First of all I am not able to see any difference in then part and else part of first case statement?
Second what is condition you want to apply -

You can only use case statement in where clause like below -

WHERE <Expr1> = CASE WHEN <Condition1> THEN <Expr1> ELSE <Expr3>

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-09-07 : 08:35:16

the problem is the "OR" on the right hand side of the CASE statement

you can't say

when X then A OR B

you can say
when A OR B then X

Sachin.Nand

2937 Posts

Posted - 2010-09-07 : 08:48:48

quote:
Originally posted by dnf999

Tried it again with:

AND (
CASE
WHEN DATEPART(MM,A.DATE) = 12 AND DATEPART(DD,A.DATE) >= 28 THEN DATEPART(YYYY,A.DATE) = C.RATE_YEAR
ELSE
DATEPART(YYYY,A.DATE) = C.RATE_YEAR
END
OR
CASE
WHEN DATEPART(MM,A.DATE) = 12 AND DATEPART(DD,A.DATE) >= 28 THEN DATEPART(YYYY,A.DATE) = C.RATE_YEAR + 1
ELSE
DATEPART(YYYY,A.DATE) = C.RATE_YEAR
END
)

but still get the same error (Error: Incorrect syntax near '='.)

I am not to sure but wont just this suffice your requirement?


AND ( 

CASE DATEPART(YYYY,A.DATE)
   WHEN DATEPART(MM,A.DATE) = 12 AND DATEPART(DD,A.DATE) >= 28 THEN  C.RATE_YEAR 
   ELSE 
C.RATE_YEAR + 1
   END

)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2010-09-07 : 08:50:00

Idera, yes your solution is what I need. I tried to put a join condition within the case statement that was causing me problems.

Thanks all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources