In case of nested transactions, will the @.@.TRANCOUNT value be always 0
if the entire transaction is rolled back at the very end?
Thanks,
ArpanIf I understand the question correctly, you are wondering what the value of
@.@.TRANCOUNT will be when you issue a ROLLBACK TRANSACTION at some point in
the processing before a COMMIT. If this is the question, then @.@.TRANCOUNT's
value will be 0.
"Arpan" wrote:
> In case of nested transactions, will the @.@.TRANCOUNT value be always 0
> if the entire transaction is rolled back at the very end?
> Thanks,
> Arpan
>|||Thanks, Shahryar, for your response. I know that ROLLBACK at some point
of time before a COMMIT statement will set @.@.TRANCOUNT to 0 but will
@.@.TRANCOUNT's value ALWAYS be 0 at the END OF A TRANSACTION assuming
that the transaction isn't COMMITted at the end?
Thanks,
Regards,
Arpan
Showing posts with label case. Show all posts
Showing posts with label case. Show all posts
Friday, March 23, 2012
Monday, March 12, 2012
Nested case?
IS it possible to use nested case statement in TSQL,if yes what is the syntax of it
I m using SQL 2005
thanx
its possible...here is an example/syntax...
declare @.var1 int
declare @.var2 int
set @.var1 =1
set @.var2 =1
select
CASE @.var1
WHEN 1
THEN(
CASE @.var2
WHEN 1 THEN(
100)
ELSE 99
END)
ELSE 98
END
|||thanx very much.... this is the thing i asked for
Nested CASE statements Problem
I can't get the syntax right on my nested CASE statements nor have I found anything on the web pertaining to nested SQL CASE statements:
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
ERRORS:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
ERRORS:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'
I think there are two problems:
(a) You have a THEN {something} & {a new CASE statement} ==> this needs to be seperated with an ELSE
(b) Each CASE WHEN needs an END
I was not able to completely modify your T-SQL...but your changes should look similar to:
select case when 1=1 then
case when 2=2 then 1 else
case when 3=3 then 2 else 1
end
end
end
Peter
|||I think I probably should just use IF statements inside my first CASE statement|||Well, this throws an error also.
The error is:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'IF'.
Msg 147, Level 15, State 1, Line 5
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Msg 147, Level 15, State 1, Line 9
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0
BEGIN
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
END
ELSE
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
|||
yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .
case 1 and Case 4 of your outer else are the same. . .
should it have read:
ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
now if your first test in the outer case:
if ArgA > ArgB then this is never true:
ArgA < 0 AND ArgB > 0
then wouldn't all of the first half of your outer case logic translate to
if ArgA>ArgB then ArgA - Abs(ArgB)
now the else part:
if ArgB < ArgA then this is never true:
ArgA > 0 AND ArgB < 0
then wouldn't all of the else half of your outer case logic translate to
ArgB - abs(ArgA)
so simply put, wont this do it -
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
|||
I ended up having to take out each CASE after my first CASE in my nested CASE statement which if you think about it is the correct syntax
|||typo on my last statement (change is bolditalic)
yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .
case 1 and Case 4 of your outer else are the same. . .
should it have read:
ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
now if your first test in the outer case:
if ArgA > ArgB then this is never true:
ArgA < 0 AND ArgB > 0
then wouldn't all of the first half of your outer case logic translate to
if ArgA>ArgB then ArgA - Abs(ArgB)
now the else part:
if ArgB > ArgA then this is never true:
ArgA > 0 AND ArgB < 0
then wouldn't all of the else half of your outer case logic translate to
ArgB - abs(ArgA)
so simply put, wont this do it -
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
|||
sth is illogic in your code man, some cases are just useless, you can delete them, because there is no way that they occur.
otherwise, just take off the case words from inside (to have a correct syntax) 
Nested CASE statements Problem
I can't get the syntax right on my nested CASE statements nor have I found anything on the web pertaining to nested SQL CASE statements:
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
ERRORS:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
ERRORS:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'CASE'
I think there are two problems:
(a) You have a THEN {something} & {a new CASE statement} ==> this needs to be seperated with an ELSE
(b) Each CASE WHEN needs an END
I was not able to completely modify your T-SQL...but your changes should look similar to:
select case when 1=1 then
case when 2=2 then 1 else
case when 3=3 then 2 else 1
end
end
end
Peter
|||I think I probably should just use IF statements inside my first CASE statement|||Well, this throws an error also.
The error is:
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'IF'.
Msg 147, Level 15, State 1, Line 5
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Msg 147, Level 15, State 1, Line 9
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE WHEN SUM(rf.rmstranamt) > SUM(rf10.rmstranamt10) Then
IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0
BEGIN
SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
END
ELSE
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0
BEGIN
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
END
IF SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0
BEGIN
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
END
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
|||
yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .
case 1 and Case 4 of your outer else are the same. . .
should it have read:
ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
now if your first test in the outer case:
if ArgA > ArgB then this is never true:
ArgA < 0 AND ArgB > 0
then wouldn't all of the first half of your outer case logic translate to
if ArgA>ArgB then ArgA - Abs(ArgB)
now the else part:
if ArgB < ArgA then this is never true:
ArgA > 0 AND ArgB < 0
then wouldn't all of the else half of your outer case logic translate to
ArgB - abs(ArgA)
so simply put, wont this do it -
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
|||
I ended up having to take out each CASE after my first CASE in my nested CASE statement which if you think about it is the correct syntax
|||typo on my last statement (change is bolditalic)
yeah the case syntax is buggered, look at the SIGN function. . .but first lets look at this. . .
case 1 and Case 4 of your outer else are the same. . .
should it have read:
ELSE
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) < 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) < 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf10.rmstranamt10) + (rf.rmstranamt)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) > 0 Then
SUM(rf.rmstranamt) + SUM(rf10.rmstranamt10)
CASE WHEN SUM(rf.rmstranamt) > 0 AND SUM(rf10.rmstranamt10) < 0 Then
now if your first test in the outer case:
if ArgA > ArgB then this is never true:
ArgA < 0 AND ArgB > 0
then wouldn't all of the first half of your outer case logic translate to
if ArgA>ArgB then ArgA - Abs(ArgB)
now the else part:
if ArgB > ArgA then this is never true:
ArgA > 0 AND ArgB < 0
then wouldn't all of the else half of your outer case logic translate to
ArgB - abs(ArgA)
so simply put, wont this do it -
SELECT rm.rmsacctnum AS [Rms Acct Num],
SUM(rf.rmstranamt) AS [TranSum],
SUM(rf10.rmstranamt10) AS [10Sum],
CASE sign( SUM(rf.rmstranamt) - SUM(rf10.rmstranamt10))
when 1 Then SUM(rf.rmstranamt) - Abs(SUM(rf10.rmstranamt10))
else SUM(rf10.rmstranamt10) - Abs(SUM(rf.rmstranamt)) END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
|||
sth is illogic in your code man, some cases are just useless, you can delete them, because there is no way that they occur.
otherwise, just take off the case words from inside (to have a correct syntax)
Nested case prediction query question
I have a question about what is possible with a prediction query
against a nested table. Say I have a basic customer-product case and nested table mining model like so:
Mining Model DT_CustProd
(
[Id] ,
[Gender] ,
[Age]
[Products] Predict
(
[ProductName] ,
[Quantity]
)
)
Using Microsoft_Decision_Trees
I can write a query to find the probability of product (and quantity) A like so:
SELECT (select * from Predict(Products,INCLUDE_STATISTICS)
where ProductName = 'A' )
FROM DT_CustProd
NATURAL PREDICTION JOIN
(SELECT 'M' AS [Gender],
27 AS [AGE] ) AS t
What if I know that the query customer (M,27) in question has purchased product B, how can I use that in the prediction join to predict product A? The fact that product B was purchased might influence the prediction, right?
Yes, the fact that B was purchased will likely influence the prediction and the model (by marking the table as Predict) actually uses existing products information in predicting new products. The changed query should look like below (a generalized example, for a customer that bought B and C):
SELECT (select * from Predict(Products,INCLUDE_STATISTICS)
where ProductName = 'A' )
FROM DT_CustProd
NATURAL PREDICTION JOIN
(SELECT 'M' AS [Gender],
27 AS [AGE],
(SELECT 'B' AS ProductName, 2 AS Quantity UNION
SELECT 'C' AS ProductName, 1 AS Quantity
) AS Products
) AS t
nested case - is there an simpler way to do this?
QtyPcntCompleted should never exceed 100 but I have no control over
what is in PostedQty or ForecastQty.
So if the calculation exceeds 100 I set it to 100.
What I'm wondering is can the nested CASE be replaced by a function?
QtyPcntCompleted = CASE WHEN ForecastQty = 0 THEN 0 ELSE CASE WHEN
(((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN 100
ELSE ((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty END END
thanks in advance for your feedbackI don't think you need nested CASE statements here
SELECT 'QtyPcntCompleted'=
CASE
WHEN ForecastQty = 0 THEN CAST(0 AS decimal(5,2))
WHEN (((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN
CAST(100 AS decimal(5,2))
ELSE CAST((((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) AS
decimal(5,2))
END
All the possible returned values from the CASE construct have to be of the
same data type, so I would cast the 0 and 100 values into decimal(5,2).
Otherwise, a fractional percentage may get truncated or rounded because the
CASE statement selected a datatype of integer.
"Gerard" wrote:
> QtyPcntCompleted should never exceed 100 but I have no control over
> what is in PostedQty or ForecastQty.
> So if the calculation exceeds 100 I set it to 100.
> What I'm wondering is can the nested CASE be replaced by a function?
> --
> QtyPcntCompleted = CASE WHEN ForecastQty = 0 THEN 0 ELSE CASE WHEN
> (((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN 100
> ELSE ((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty END END
> --
> thanks in advance for your feedback
>|||Thanks Mark,
much appreciated
Gerard
what is in PostedQty or ForecastQty.
So if the calculation exceeds 100 I set it to 100.
What I'm wondering is can the nested CASE be replaced by a function?
QtyPcntCompleted = CASE WHEN ForecastQty = 0 THEN 0 ELSE CASE WHEN
(((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN 100
ELSE ((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty END END
thanks in advance for your feedbackI don't think you need nested CASE statements here
SELECT 'QtyPcntCompleted'=
CASE
WHEN ForecastQty = 0 THEN CAST(0 AS decimal(5,2))
WHEN (((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN
CAST(100 AS decimal(5,2))
ELSE CAST((((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) AS
decimal(5,2))
END
All the possible returned values from the CASE construct have to be of the
same data type, so I would cast the 0 and 100 values into decimal(5,2).
Otherwise, a fractional percentage may get truncated or rounded because the
CASE statement selected a datatype of integer.
"Gerard" wrote:
> QtyPcntCompleted should never exceed 100 but I have no control over
> what is in PostedQty or ForecastQty.
> So if the calculation exceeds 100 I set it to 100.
> What I'm wondering is can the nested CASE be replaced by a function?
> --
> QtyPcntCompleted = CASE WHEN ForecastQty = 0 THEN 0 ELSE CASE WHEN
> (((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty) > 100 THEN 100
> ELSE ((PostedQty + @.QuantityFulfilled) * 100) / ForecastQty END END
> --
> thanks in advance for your feedback
>|||Thanks Mark,
much appreciated
Gerard
Nested CASE
Hello,
I've got a SP that selects the best price from a table that has all info collected into it. Selecting the price is easy, I use COALESCE.
But I want to have a column next to it that contains which price that was choosen. I used CASE and nested it... worked fine until I reached the 10th level, there is a limit there.
"Case expressions may only be nested to level 10."
I'm sure som people will puke when they see this code and I'm open to suggestions on how to do it in another way. I can always do it in two queries, but it should be possible to do it in one.
I was looking at IF, THEN, ELSE, but I don't find any way to use it in a query, just to determine WHICH query will be run.
Here is my SP (how can I get it in a nice grey area like som people post?):
CREATE PROCEDURE dbo.ProcCOST_SET_TC AS
/* Empty TC table */
truncate table dbo.COST_TC
/* Collect info */
INSERT INTO dbo.COST_TC
SELECT REGION,PROJECT,CPN,
COALESCE (
Contract_usd,
SITEINPUT_sitecontract_usd,
SITEINPUT_lastPO_usd,
SITEINPUT_lastreceipt_usd,
SITEINPUT_other_usd,
SITEINPUT_wac_usd,
SYSTEM_Min_ContractPrice_usd,
SYSTEM_Min_OpenOrder_usd,
SYSTEM_Last_Receipt_usd,
SYSTEM_Min_WAC_usd,
[BP Q-1]
),
Case Contract_usd WHEN IsNull(Contract_USD,0) THEN 'Contract' ELSE
Case SITEINPUT_sitecontract_usd WHEN IsNull(SITEINPUT_sitecontract_usd,0) THEN 'SITEINPUT Site Contract' ELSE
Case SITEINPUT_lastPO_usd WHEN IsNull(SITEINPUT_lastPO_usd,0) THEN 'SITEINPUT Last PO' ELSE
Case SITEINPUT_lastreceipt_usd WHEN IsNull(SITEINPUT_lastreceipt_usd,0) THEN 'SITEINPUT Last Receipt' ELSE
Case SITEINPUT_other_usd WHEN IsNull(SITEINPUT_other_usd,0) THEN 'SITEINPUT Other' ELSE
Case SITEINPUT_wac_usd WHEN IsNull(SITEINPUT_wac_usd,0) THEN 'SITEINPUT WAC' ELSE
Case SYSTEM_Min_ContractPrice_usd WHEN IsNull(SYSTEM_Min_ContractPrice_usd,0) THEN 'Min Contract Price' ELSE
Case SYSTEM_Min_OpenOrder_usd WHEN IsNull(SYSTEM_Min_OpenOrder_usd,0) THEN 'Min Open Order' ELSE
Case SYSTEM_Last_Receipt_usd WHEN IsNull(SYSTEM_Last_Receipt_usd,0) THEN 'Last Receipt' ELSE
Case SYSTEM_Min_WAC_usd WHEN IsNull(SYSTEM_Min_WAC_usd,0) THEN 'Min WAC' ELSE
Case [BP Q-1] WHEN IsNull([BP Q-1],0) THEN 'BP Q-1' ELSE
'NO DATA' END END END END END END END END END END END
FROM COST_AllInfo
GO
---
Suggestions (don't hit me to hard)?Use the [ code] and [ /code] markers before and after your code so that vBulletin will treat it as code. I've put a space after the opening bracket so that they'll be visible in my posting, you must remove the spaces for them to take affect.
Turn them all into one CASE, something like (untested, of course):CREATE PROCEDURE dbo.ProcCOST_SET_TC AS
/* Empty TC table */
truncate table dbo.COST_TC
/* Collect info */
INSERT INTO dbo.COST_TC
SELECT REGION,PROJECT,CPN,
COALESCE (
Contract_usd,
SITEINPUT_sitecontract_usd,
SITEINPUT_lastPO_usd,
SITEINPUT_lastreceipt_usd,
SITEINPUT_other_usd,
SITEINPUT_wac_usd,
SYSTEM_Min_ContractPrice_usd,
SYSTEM_Min_OpenOrder_usd,
SYSTEM_Last_Receipt_usd,
SYSTEM_Min_WAC_usd,
[BP Q-1]
),
Case Contract_usd WHEN IsNull(Contract_USD,0) THEN 'Contract'
WHEN SITEINPUT_sitecontract_usd WHEN IsNull(SITEINPUT_sitecontract_usd,0) THEN 'SITEINPUT Site Contract'
WHEN SITEINPUT_lastPO_usd WHEN IsNull(SITEINPUT_lastPO_usd,0) THEN 'SITEINPUT Last PO'
WHEN SITEINPUT_lastreceipt_usd WHEN IsNull(SITEINPUT_lastreceipt_usd,0) THEN 'SITEINPUT Last Receipt'
WHEN SITEINPUT_other_usd WHEN IsNull(SITEINPUT_other_usd,0) THEN 'SITEINPUT Other'
WHEN SITEINPUT_wac_usd WHEN IsNull(SITEINPUT_wac_usd,0) THEN 'SITEINPUT WAC'
WHEN SYSTEM_Min_ContractPrice_usd WHEN IsNull(SYSTEM_Min_ContractPrice_usd,0) THEN 'Min Contract Price' ELSE
WHEN SYSTEM_Min_OpenOrder_usd WHEN IsNull(SYSTEM_Min_OpenOrder_usd,0) THEN 'Min Open Order'
WHEN SYSTEM_Last_Receipt_usd WHEN IsNull(SYSTEM_Last_Receipt_usd,0) THEN 'Last Receipt'
WHEN SYSTEM_Min_WAC_usd WHEN IsNull(SYSTEM_Min_WAC_usd,0) THEN 'Min WAC'
WHEN [BP Q-1] WHEN IsNull([BP Q-1],0) THEN 'BP Q-1'
ELSE 'NO DATA' END
FROM COST_AllInfo
RETURN
GO-PatP|||This CASE is tested and works to my satisfaction... Looks a lot cleaner too than my first atempt.
CASE
WHEN isnull(VPA_average_USD,0) > 0 THEN 'VPA'
WHEN isnull(SITEINPUT_sitecontract_usd,0) > 0 THEN 'SITEINPUT Site Contract'
WHEN isnull(SITEINPUT_lastPO_usd,0) > 0 THEN 'SITEINPUT Last PO'
WHEN isnull(SITEINPUT_lastreceipt_usd,0) > 0 THEN 'SITEINPUT Last Receipt'
WHEN isnull(SITEINPUT_other_usd,0) > 0 THEN 'SITEINPUT Other'
WHEN isnull(SITEINPUT_wac_usd,0) > 0 THEN 'SITEINPUT WAC'
WHEN isnull(SYSTEM_Min_ContractPrice_usd,0) > 0 THEN 'Min Contract Price'
WHEN isnull(SYSTEM_Min_OpenOrder_usd,0) > 0 THEN 'Min Open Order'
WHEN isnull(SYSTEM_Last_Receipt_usd,0) > 0 THEN 'Last Receipt'
WHEN isnull(SYSTEM_Min_WAC_usd,0) > 0 THEN 'Min WAC'
ELSE 'NO DATA'
END
Thanks for pointing me in the right direction!|||How do you figure your COALESCE statement is going to return the best price? I don't know what "best price" means (lowest? highest? best for whom?), but I'm sure COALESCE doesn't know either. It just returns the first non-null value from your parameter list. So Contract_usd will be returned because it is the first value, not the "best" value.|||The "Best Price" is order by priority, not by value. So the order of the columns in the COALESCE determines the priority order.
The first priority is "VPA_average_USD", if there is one, use it no matter what the value is. (I see I used the actual column name instead of "Contract_usd" I used in my first post)
The last priority is the price used last quarter.
I've got a SP that selects the best price from a table that has all info collected into it. Selecting the price is easy, I use COALESCE.
But I want to have a column next to it that contains which price that was choosen. I used CASE and nested it... worked fine until I reached the 10th level, there is a limit there.
"Case expressions may only be nested to level 10."
I'm sure som people will puke when they see this code and I'm open to suggestions on how to do it in another way. I can always do it in two queries, but it should be possible to do it in one.
I was looking at IF, THEN, ELSE, but I don't find any way to use it in a query, just to determine WHICH query will be run.
Here is my SP (how can I get it in a nice grey area like som people post?):
CREATE PROCEDURE dbo.ProcCOST_SET_TC AS
/* Empty TC table */
truncate table dbo.COST_TC
/* Collect info */
INSERT INTO dbo.COST_TC
SELECT REGION,PROJECT,CPN,
COALESCE (
Contract_usd,
SITEINPUT_sitecontract_usd,
SITEINPUT_lastPO_usd,
SITEINPUT_lastreceipt_usd,
SITEINPUT_other_usd,
SITEINPUT_wac_usd,
SYSTEM_Min_ContractPrice_usd,
SYSTEM_Min_OpenOrder_usd,
SYSTEM_Last_Receipt_usd,
SYSTEM_Min_WAC_usd,
[BP Q-1]
),
Case Contract_usd WHEN IsNull(Contract_USD,0) THEN 'Contract' ELSE
Case SITEINPUT_sitecontract_usd WHEN IsNull(SITEINPUT_sitecontract_usd,0) THEN 'SITEINPUT Site Contract' ELSE
Case SITEINPUT_lastPO_usd WHEN IsNull(SITEINPUT_lastPO_usd,0) THEN 'SITEINPUT Last PO' ELSE
Case SITEINPUT_lastreceipt_usd WHEN IsNull(SITEINPUT_lastreceipt_usd,0) THEN 'SITEINPUT Last Receipt' ELSE
Case SITEINPUT_other_usd WHEN IsNull(SITEINPUT_other_usd,0) THEN 'SITEINPUT Other' ELSE
Case SITEINPUT_wac_usd WHEN IsNull(SITEINPUT_wac_usd,0) THEN 'SITEINPUT WAC' ELSE
Case SYSTEM_Min_ContractPrice_usd WHEN IsNull(SYSTEM_Min_ContractPrice_usd,0) THEN 'Min Contract Price' ELSE
Case SYSTEM_Min_OpenOrder_usd WHEN IsNull(SYSTEM_Min_OpenOrder_usd,0) THEN 'Min Open Order' ELSE
Case SYSTEM_Last_Receipt_usd WHEN IsNull(SYSTEM_Last_Receipt_usd,0) THEN 'Last Receipt' ELSE
Case SYSTEM_Min_WAC_usd WHEN IsNull(SYSTEM_Min_WAC_usd,0) THEN 'Min WAC' ELSE
Case [BP Q-1] WHEN IsNull([BP Q-1],0) THEN 'BP Q-1' ELSE
'NO DATA' END END END END END END END END END END END
FROM COST_AllInfo
GO
---
Suggestions (don't hit me to hard)?Use the [ code] and [ /code] markers before and after your code so that vBulletin will treat it as code. I've put a space after the opening bracket so that they'll be visible in my posting, you must remove the spaces for them to take affect.
Turn them all into one CASE, something like (untested, of course):CREATE PROCEDURE dbo.ProcCOST_SET_TC AS
/* Empty TC table */
truncate table dbo.COST_TC
/* Collect info */
INSERT INTO dbo.COST_TC
SELECT REGION,PROJECT,CPN,
COALESCE (
Contract_usd,
SITEINPUT_sitecontract_usd,
SITEINPUT_lastPO_usd,
SITEINPUT_lastreceipt_usd,
SITEINPUT_other_usd,
SITEINPUT_wac_usd,
SYSTEM_Min_ContractPrice_usd,
SYSTEM_Min_OpenOrder_usd,
SYSTEM_Last_Receipt_usd,
SYSTEM_Min_WAC_usd,
[BP Q-1]
),
Case Contract_usd WHEN IsNull(Contract_USD,0) THEN 'Contract'
WHEN SITEINPUT_sitecontract_usd WHEN IsNull(SITEINPUT_sitecontract_usd,0) THEN 'SITEINPUT Site Contract'
WHEN SITEINPUT_lastPO_usd WHEN IsNull(SITEINPUT_lastPO_usd,0) THEN 'SITEINPUT Last PO'
WHEN SITEINPUT_lastreceipt_usd WHEN IsNull(SITEINPUT_lastreceipt_usd,0) THEN 'SITEINPUT Last Receipt'
WHEN SITEINPUT_other_usd WHEN IsNull(SITEINPUT_other_usd,0) THEN 'SITEINPUT Other'
WHEN SITEINPUT_wac_usd WHEN IsNull(SITEINPUT_wac_usd,0) THEN 'SITEINPUT WAC'
WHEN SYSTEM_Min_ContractPrice_usd WHEN IsNull(SYSTEM_Min_ContractPrice_usd,0) THEN 'Min Contract Price' ELSE
WHEN SYSTEM_Min_OpenOrder_usd WHEN IsNull(SYSTEM_Min_OpenOrder_usd,0) THEN 'Min Open Order'
WHEN SYSTEM_Last_Receipt_usd WHEN IsNull(SYSTEM_Last_Receipt_usd,0) THEN 'Last Receipt'
WHEN SYSTEM_Min_WAC_usd WHEN IsNull(SYSTEM_Min_WAC_usd,0) THEN 'Min WAC'
WHEN [BP Q-1] WHEN IsNull([BP Q-1],0) THEN 'BP Q-1'
ELSE 'NO DATA' END
FROM COST_AllInfo
RETURN
GO-PatP|||This CASE is tested and works to my satisfaction... Looks a lot cleaner too than my first atempt.
CASE
WHEN isnull(VPA_average_USD,0) > 0 THEN 'VPA'
WHEN isnull(SITEINPUT_sitecontract_usd,0) > 0 THEN 'SITEINPUT Site Contract'
WHEN isnull(SITEINPUT_lastPO_usd,0) > 0 THEN 'SITEINPUT Last PO'
WHEN isnull(SITEINPUT_lastreceipt_usd,0) > 0 THEN 'SITEINPUT Last Receipt'
WHEN isnull(SITEINPUT_other_usd,0) > 0 THEN 'SITEINPUT Other'
WHEN isnull(SITEINPUT_wac_usd,0) > 0 THEN 'SITEINPUT WAC'
WHEN isnull(SYSTEM_Min_ContractPrice_usd,0) > 0 THEN 'Min Contract Price'
WHEN isnull(SYSTEM_Min_OpenOrder_usd,0) > 0 THEN 'Min Open Order'
WHEN isnull(SYSTEM_Last_Receipt_usd,0) > 0 THEN 'Last Receipt'
WHEN isnull(SYSTEM_Min_WAC_usd,0) > 0 THEN 'Min WAC'
ELSE 'NO DATA'
END
Thanks for pointing me in the right direction!|||How do you figure your COALESCE statement is going to return the best price? I don't know what "best price" means (lowest? highest? best for whom?), but I'm sure COALESCE doesn't know either. It just returns the first non-null value from your parameter list. So Contract_usd will be returned because it is the first value, not the "best" value.|||The "Best Price" is order by priority, not by value. So the order of the columns in the COALESCE determines the priority order.
The first priority is "VPA_average_USD", if there is one, use it no matter what the value is. (I see I used the actual column name instead of "Contract_usd" I used in my first post)
The last priority is the price used last quarter.
Monday, February 20, 2012
Need to use MID function in SQL
When I try to use the MID statement in a SQL view, it reports 'function not recognized'. Is there some other way to execute the following?
CASE WHEN Mid(SearchID , 4 , 1) = '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END.
I have a column with two data set possibilities: aaa-bbbbb and aaaa-bbbbb. I only want the data to the left of the dash.
Thanks.
Ernie
You have to combine sql sever string function
like "left" and "right" to achive you requirements
I think the equivalent of vb mid function is the "substring" function
This example shows how to return only a portion of a character string. From the authors table, this query returns the last name in one column with only the first initial in the second column.
USE pubs SELECT au_lname, SUBSTRING(au_fname, 1, 1) FROM authors ORDER BY au_lname create table #test (SearchID varchar(49))
insert into #test values('aaa-bbbbb')
insert into #test values('aaaa-bbbbb')
one way using ParseName
Select ParseName(Replace(SearchID , '-', '.'), 2)
from #test
and another using left and charindex
select distinct LEFT(SearchID ,CHARINDEX('-',SearchID )-1 )
from #test
and a third using case substring and left
select CASE substring(SearchID , 4 , 1) when '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END
from #test
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Subscribe to:
Posts (Atom)