July 14, 2004 at 5:04 pm
I'm trying to calculate a value and use that calculated value in a CASE statement without repeating it for each WHEN statement because of it's size and not wanting to cause processing to bog down. I've attemted the T-SQL below but get an error and the first inequality statement. Any help would be appreciated.
SUM
(CAST(ISNULL(o.Qty, 0) AS Decimal(25, 6))) + (CAST(ISNULL(pn.OnHand, 0) As Decimal(25, 6)))
/ (CAST(ISNULL(pn.QFD, 0) AS Decimal(25, 6)) / 3) AS MonthsOfStock,
CASE MonthsOfStock
WHEN < 1 THEN 10
WHEN >= 1 And < 3 THEN 8
WHEN >= 3 And < 5 THEN 6
WHEN >= 5 And < 8 THEN 4
WHEN >= 8 And < 13 THEN 2
WHEN >= 13 THEN 1
July 14, 2004 at 5:22 pm
I'm nowhere near a SQL box to try this, but you can't use it that way can you?
Syntactically, your CASE statement looks wrong. Shouldn't it be...
CASE WHEN MonthsOfStock < 10 then 1 etc....END
Howver, I don't think you can reference a clac'd field like that - you could calculate through a function maybe?
fnMonthsOfStock(qty,onhand, qfd)
and reference that instead?
CASE when fnMonthsOfStock(o.qty,pn.onhand,pn.qfd) <1 then...etc..
Just a possible solution, not neccessarily correct - past midnight here.
July 15, 2004 at 12:11 am
I feel the problem lies the way in which the case function has been implemente. There are 2 ways of implementing the case statement, simple case and searched case. Not too sure of the syntax. But I guess it shld be
CASE MonthsOfStock
WHEN MonthsOfStock = 1 And < 3 THEN 8..
Let me now if this helps.
July 15, 2004 at 8:58 am
I knew the posted code had a problem but I made multiple attempts to get it functional prior to posting the question. BOL was a source I accessed but frequently it covers a statement with sample examples that don't hit the mark which my issues. The example provided:
CASE MonthsOfStock
WHEN MonthsOfStock < 1 THEN 10
WHEN MonthsOfStock >= 1 And < 3 THEN 8..
I tried unsuccessfully as on one of my earlier attempts.
July 15, 2004 at 10:55 am
You're not going to be able to reference MonthsOfStock in the above example because as far as the case statement is concerned it doesn't exist - you could put the query without the case statement into a subselect and then put the case statement in the outer select to get around that.
July 16, 2004 at 5:05 am
Guys may be the calculated column is not availabe inside the CASE..soo instead of referring it by the name recalculate it everytime inside every condition or use a function for every where.
hope im right ..if not correct me too
July 16, 2004 at 7:24 am
You have to nest the selects and calculate the columns you want to use for other columns in the inner select. eg
select *,
CASE MonthsOfStock
WHEN < 1 THEN 10
WHEN >= 1 And < 3 THEN 8
WHEN >= 3 And < 5 THEN 6
WHEN >= 5 And < 8 THEN 4
WHEN >= 8 And < 13 THEN 2
WHEN >= 13 THEN 1
END as field
from
(select SUM(CAST(ISNULL(o.Qty, 0) AS Decimal(25, 6))) + (CAST(ISNULL(pn.OnHand, 0) As Decimal(25, 6)))
/ (CAST(ISNULL(pn.QFD, 0) AS Decimal(25, 6)) / 3) AS MonthsOfStock
from table1) n1
Perhaps a clearer example is:
select *, col2 * 3 as col3
from
(select *, (col1 -4) as col2
from
(select a*v/4 as col1, col5
from table1
) n1
) n2
where col3 is based on calculated column col2, which is based on calculated column col1.
I hope this is clear
Peter
July 16, 2004 at 8:44 am
hey scking,
jt-75 is right ...
here is the query that jt-75 is talking about:
SELECT
Calculated_Value =
CASE
WHEN MonthsOfStock < 1 THEN 10
WHEN MonthsOfStock >= 1 And MonthsOfStock < 3 THEN 8
WHEN MonthsOfStock >= 3 And MonthsOfStock < 5 THEN 6
WHEN MonthsOfStock >= 5 And MonthsOfStock < 8 THEN 4
WHEN MonthsOfStock >= 8 And MonthsOfStock < 13 THEN 2
ELSE 1
END
FROM
(
SELECT
SUM(CAST(ISNULL(o.Qty, 0) AS Decimal(25, 6))) + (CAST(ISNULL(pn.OnHand, 0) As Decimal(25, 6))) / (CAST(ISNULL(pn.QFD, 0) AS Decimal(25, 6)) / 3) AS MonthsOfStock, ....
)
JP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply