CASE trouble - SQL Server Forums

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)

CASE trouble

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-11 : 04:16:33

Hi

I try to do this...

CASE WHEN @ProductID = 91 THEN
IF @DesiredQty > @MaxItemsToOrder
SET @Qty = @MaxItemsToOrder
ELSE
SET @Qty = (@CurrentQty + @Quantity)
CASE WHEN @ProductID = 92 THEN
IF @DesiredQty > @MaxItemsToOrder
SET @Qty = @MaxItemsToOrder
ELSE
SET @Qty = (@CurrentQty + @Quantity)
ELSE
SET @Qty = (@CurrentQty + @Quantity)
END

But it does not work, how can I modify it so it work?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 04:19:15

basically you can only use CASE ... WHEN in side a query and use IF ... ELSE outside a query. You are mixing these up.

KH
[spoiler]Time is always against us[/spoiler]

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-11 : 04:34:25

Is there any way that I can accomplish what I try here?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 04:46:00

just rewrite and and don't use CASE .. WHEN. Why are you using CASE . . WHEN ?

KH
[spoiler]Time is always against us[/spoiler]

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-11 : 05:03:33

Beacuse I was not sure on how to do this, but I changed it like this now...

IF @ProductID = 91
IF @DesiredQty > @MaxItemsToOrder
SET @Qty = @MaxItemsToOrder
ELSE
SET @Qty = (@CurrentQty + @Quantity)
IF @ProductID = 92
IF @DesiredQty > @MaxItemsToOrder
SET @Qty = @MaxItemsToOrder
ELSE
SET @Qty = (@CurrentQty + @Quantity)
ELSE
SET @Qty = (@CurrentQty + @Quantity)

But that does not seem to work either

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 05:10:27

not sure how your logic goes . . . but use BEGIN . . .END to enclosed your IF BLOCK
something like this. . .
note: i don't know what is your logic here but i just put in some BEGIN . .END to show you how to enclosed it


IF @ProductID = 91
BEGIN

    IF @DesiredQty > @MaxItemsToOrder
        SET @Qty = @MaxItemsToOrder
    ELSE
        SET @Qty = (@CurrentQty + @Quantity)
END

IF @ProductID = 92
BEGIN
    IF @DesiredQty > @MaxItemsToOrder
        SET @Qty = @MaxItemsToOrder
    ELSE
        SET @Qty = (@CurrentQty + @Quantity)
END
ELSE
BEGIN
        SET @Qty = (@CurrentQty + @Quantity)
END

KH
[spoiler]Time is always against us[/spoiler]

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-11 : 05:13:14

This does not work....

DECLARE @ProductID int
DECLARE @CurrentQty Int
DECLARE @Qty Int
DECLARE @MaxItemsToOrder Int
DECLARE @DesiredQty Int
DECLARE @Quantity Int

SET @Quantity = 4
SET @CurrentQty = 5
SET @ProductID = 91
SET @Qty = 5

SELECT @MaxItemsToOrder = ROUND(300 / 50,1)
SET @DesiredQty = (@CurrentQty + @Quantity)

IF @ProductID = 91
BEGIN

IF @DesiredQty > @MaxItemsToOrder
SET @Qty = @MaxItemsToOrder
ELSE
SET @Qty = (@CurrentQty + @Quantity)
END

IF @ProductID = 92
BEGIN
IF @DesiredQty > @MaxItemsToOrder
SET @Qty = @MaxItemsToOrder
ELSE
SET @Qty = (@CurrentQty + @Quantity)
END
ELSE
BEGIN
SET @Qty = (@CurrentQty + @Quantity)
END

PRINT @Qty
PRINT @DesiredQty
PRINT @MaxItemsToOrder

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 05:18:16

maybe you can explain to use what are you trying to achieve here ?

what is the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-11 : 05:28:27

first I want to check if the product ID is either 91 or 92, if so then I need to check that the user does not try to add a higher qty than the @MaxItemsToOrder is set to. But if the Product ID dithers from 91 or 92 then the Qty should just be whatever the user prefer. In this case the current qty is set to 5, and the user try to add 4 items, then I would like the @Qty to be set to @MaxItemsToOrder ( IF @DesiredQty > @MaxItemsToOrder
SET @Qty = @MaxItemsToOrder), beacuse he try to order more items than he is allowed to, otherwise the @Qty should be (SET @Qty = (@CurrentQty + @Quantity))

Hope this make sence

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 05:36:43

[code]DECLARE @ProductID int
DECLARE @CurrentQty int
DECLARE @Qty int
DECLARE @MaxItemsToOrder int
DECLARE @DesiredQty int
DECLARE @Quantity int

SET @Quantity = 4
SET @CurrentQty = 5
SET @ProductID = 91
SET @Qty = 5

SELECT @MaxItemsToOrder = ROUND(300 / 50,1)
SET @DesiredQty = (@CurrentQty + @Quantity)

IF @ProductID IN ( 91 , 92 )
BEGIN
IF @DesiredQty > @MaxItemsToOrder
SET @Qty = @MaxItemsToOrder
ELSE
SET @Qty = (@CurrentQty + @Quantity)
END
ELSE
BEGIN
SET @Qty = (@CurrentQty + @Quantity)
END

PRINT @Qty
PRINT @DesiredQty
PRINT @MaxItemsToOrder[/code]

KH
[spoiler]Time is always against us[/spoiler]

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-09-11 : 05:38:42

Just what I was after, Thanks a lot!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-11 : 08:37:46

[code]SET @Qty = CASE
WHEN @ProductID IN (91, 92) AND @DesiredQty > @MaxItemsToOrder THEN @MaxItemsToOrder
ELSE @CurrentQty + @Quantity
END[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources