Help with Case Statement
- I have the following code in which I need to check something in the ELSE. The problem is how to form it correctly. I could use a cursor I guess to transverse through the records.
In the Else, I want to:
1) Check the count of the results of my statement. IF > 1 then check to see if m.original is between lowlimit and highlimit for any of those records found
2) If m.original is between the lowlimit and highlimit, then select Fee1 from FeeScheduleDetails
m.original and m.FeeSchedule is out here somewhere, just know this...it's part of the query that you don't see
CASE WHEN Len(c.FeeSchedule) < 3 then
CONVERT(int, c.feeSchedule)
ELSE
Select Count(*) FROM FeeScheduleDetails fd
INNER JOIN Master m ON m.FeeSchedule = fd.code
IF Count(*) > 3
Check to see if m.original is BETWEEN fd.lowlimit AND fd.highlimit
If yes, then bring me back fee1, if no then just bring me back m.FeeSchedule
END AS FeeSchedule
Master
---------
FeeSchedule
FeeScheduleDetails
----------------------
Code
LowLimit
HighLimit
Fee1
-
It may help to get your question answered if you post the entire query. Table DDL and sample data would also be helpful.
-
Yo ushould be able to Nest you CASE WHEN statements in a similar fashion that that below.
Hopefully I've provided you with enough of an example to allow you to get it working.
CASE WHEN Len(c.FeeSchedule) 1
THEN
CASE WHEN m.original is BETWEEN fd.lowlimit AND fd.highlimit THEN
bring me back fee1
ELSE
just bring me back m.FeeSchedule
END
ELSE
just bring me back m.FeeSchedule
END
END AS FeeSchedule
You could also include the (SELECT COUNT(*) ...) as a part of the FROM structure so as to make the CASE WHEN a little easier to read.
HTH
-
1. Embedding a "Select Count(*) " as a sub-SELECT within a SELECT will yield horrible performance. It's essentially a cursor-like operation.
2. Overloading the meaning of a data element is always a problem.
>>Len(c.FeeSchedule) < 3
FeeSchedule should contain a FeeSchedule code and should not be overloaded with some special meaning depending on how many characters it contains.
http://www.dmreview.com/article_sub.cfm?articleId=1040185
As stated in the previous post, full query & DDL are pretty much essential to solving this.
-
Ok, for now I tried this but get errors:
CASE WHEN Len(c.FeeSchedule) < 3 then
CONVERT(int, c.feeSchedule)
ELSE
CASE WHEN (Select Count(*) FROM FeeScheduleDetails fd INNER JOIN Master m ON m.FeeSchedule = fd.code) > 3 THEN
CASE WHEN m.original BETWEEN fd.lowlimit AND fd.highlimit THEN
CONVERT(int, fd.fee1)
ELSE
CONVERT(int, c.feeSchedule)
END
ELSE
CONVERT(int, c.feeSchedule)
END
END AS FeeSchedule
Errors:
Msg 156, Level 15, State 1, Procedure IT_Create_CurrentMonth_DCR_Data2, Line 101
Incorrect syntax near the keyword 'Select'.
Msg 170, Level 15, State 1, Procedure IT_Create_CurrentMonth_DCR_Data2, Line 101
Line 101: Incorrect syntax near ')'.
Msg 156, Level 15, State 1, Procedure IT_Create_CurrentMonth_DCR_Data2, Line 104
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure IT_Create_CurrentMonth_DCR_Data2, Line 195
Incorrect syntax near the keyword 'END'.
-
I'd be inclined to move the select statment as I mentioned before
something like
CASE WHEN Len(c.FeeSchedule) 3 THEN
CASE WHEN m.original BETWEEN fd.lowlimit AND fd.highlimit THEN
CONVERT(int, fd.fee1)
ELSE
CONVERT(int, c.feeSchedule)
END
ELSE
CONVERT(int, c.feeSchedule)
END
END AS FeeSchedule
FROM
Some Tables you select from
JOIN (Select Count(*) AS ItemCount, m.FeeSchedule, fd.code FROM FeeScheduleDetails fd INNER JOIN Master m ON m.FeeSchedule = fd.code) AS CountTable ct
etc
Then if you are still getting the errors you can start to dissect the wuery by cutting parts of it out so you know exactly where the error is.
The error may be based on how the case when statement handles a select cause internally.
-
Following on from Steve and PW's suggestions, I'd definately re-organise the query to remove the SELECT from the case and place it in to the FROM clause
Basically your criteria can be evaluated as
if the length of c.FeeSchedule > 3 and the count from FeeScheduleDetails is > 3 and the value of Original is between lowlimit and highlimit then use fee1 else use feeSchedule
No guarantees this will work but it should give you a start
SELECT fd.Fee1
FROM FeeScheduleDetails fd
INNER JOIN Master m
ON m.FeeSchedule = fd.code
GROUP BY fd.code
HAVING COUNT ( * ) > 3
AND m.original BETWEEN fd.lowlimit AND fd.highlimit
--------------------
Colt 45 - the original point and click interface
-
Thanks both of you!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply