I am getting a syntax error and not sure why near my select. I want to bring back fee1 if the 2nd When is true
CASE WHEN Len(c.FeeSchedule) < 3 THEN
CONVERT(int, c.feeSchedule)
WHEN Len(c.FeeSchedule) > 3 THEN
SELECT fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code AND m.original BETWEEN fd.LowLimit AND fd.HighLimit
ELSE
CONVERT(int, c.feeSchedule)
END AS FeeSchedule,
Select Error in Case
akqajohn
You can't do a select there because you need exactly one value. Perhaps using COALESCE and TOP 1 would work as in
CASE WHEN Len(c.FeeSchedule) < 3 THEN
CONVERT(int, c.feeSchedule)
WHEN Len(c.FeeSchedule) > 3 THEN
COALESCE(
(SELECT TOP 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code AND m.original BETWEEN fd.LowLimit AND fd.HighLimit), "")
ELSE
CONVERT(int, c.feeSchedule)
END AS FeeSchedule,
But if that doesn't work, I'm not sure how you'd fix it, you probably can't do it this way.
TnTico
Thus it would be:
CASE WHEN Len(c.FeeSchedule) < 3 THEN CONVERT(int, c.feeSchedule)
WHEN Len(c.FeeSchedule) > 3 THEN
fd.Fee1
END,
Clarity Consulting (http://www.claritycon.com)
Bernd VanSkiver
OK - I did say it was a guess :-)
The core problem is that you can't put a sub query in a case, because your when should only return one value. It looks like you can't do it at all, unless someone else has a better idea.
Don Peterson
Hi,
Shughes is right. Join in the table if you you need values from that table.
If you don't have a relation to the records for all of your records use a left join. It seems to be what you are trying to accomplish with your case statement.
Regards
nrs251
but that query will never return more than one result. There is never a case where it may be between that range in 2 records' Lowlimt and Highlimit
so are you saying even so, SQL doesn't like a select like that
ArtusKG
There is a simple solution to this problem. You cannot haver a subquery within a CASE statement, regardless of whether it returns 1 value or not. Also, almost always never use TOP unless when producing reports. Otherwise, you cannot guarantee what value you with get returned if the data changes, or the db schema changes.
There are two simple solutions
1. JOIN the table in the CASE statement to the end of your query, then just select the Fee1 column in the CASE statement
2. If you are only ever going to use one value from that table, regardless of what row you are in, then instead of 1, just get the value first and store it in a variable, then use that in the CASE statement instead.
Much simpler. HTH
Clarity Consulting
FiftyFeet
CASE WHEN Len(c.FeeSchedule) < 3 THEN
CONVERT(int, c.feeSchedule)
WHEN Len(c.FeeSchedule) > 3 THEN
COALESCE((SELECT TOP 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code
AND m.original BETWEEN fd.LowLimit AND fd.HighLimit), "")
ELSE
CONVERT(int, c.feeSchedule)
END AS FeeSchedule,
Errors:
Msg 1038, Level 15, State 3, Procedure IT_Create_CurrentMonth_DCR_Data2, Line 26
Cannot use empty object or column names. Use a single space if necessary.
Msg 156, Level 15, State 1, Procedure IT_Create_CurrentMonth_DCR_Data2, Line 97
Incorrect syntax near the keyword 'Select'.
Msg 170, Level 15, State 1, Procedure IT_Create_CurrentMonth_DCR_Data2, Line 97
Line 97: Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Procedure IT_Create_CurrentMonth_DCR_Data2, Line 100
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure IT_Create_CurrentMonth_DCR_Data2, Line 191
Incorrect syntax near the keyword 'END'.
blahmoo64
CASE WHEN Len(c.FeeSchedule) < 3 THEN
CONVERT(int, c.feeSchedule)
WHEN Len(c.FeeSchedule) > 3 THEN
(SELECT fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code AND m.original BETWEEN fd.LowLimit AND fd.HighLimit)
ELSE
CONVERT(int, c.feeSchedule)
END AS FeeSchedule,
CASE WHEN Len(c.FeeSchedule) <= 3 THEN
CONVERT(int, c.feeSchedule)
ELSE
(SELECT fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code AND m.original BETWEEN fd.LowLimit AND fd.HighLimit)
END AS FeeSchedule,
--
Hugo Kornelis, SQL Server MVP
devalapa_k
I'm definately saying that even if the system were to parse your database and see this is the case, it would not be guarenteed to be the case in the future. TOP 1 can specify that you want only one value, you should always use it when you assume only one value will come back.
But I'm still not sure you can use a query in a case, just if you can, it would need to look like that.