Nested Case Statement - 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)

Nested Case Statement

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-12-10 : 08:52:11

Can someone please help with the syntax on the nested case statement below?

SELECT
	inInvAdjLi.FiscalDate,
	inInvAdjLi.RescSite,
	inInvAdjLi.LocnArea,
	inInvAdjLi.Resc     AS Resource,

	CASE inInvAdjLi.AdjActyType WHEN 2
	        CASE 
		    WHEN inInvAdjLi.PrimQtyUM <> inInvAdjLi.OrigPrQtyUM THEN
		         (inInvAdjLi.PrimQty * bcConvFactor.ToFactor)
		    ELSE inInvAdjLi.PrimQty
	        END
	     THEN (inInvAdjLi.PrimQty - inInvAdjLi.OrigPrQty)
	     ELSE 	inInvAdjLi.PrimQty
	   END AS VarianceQty,

	inInvAdjLi.PrimQtyUM 	AS UM,
	inInvAdjLi.OrigPrQtyUM,
	inInvAdjLi.UnitID						AS UnitID,
	inInvAdjLi.ReasonCode						AS ReasonCode,

FROM	inInvAdjLi	(NOLOCK)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-12-10 : 08:59:12

Try indenting properly...it will become a lot more clear if you do. I'd fix your query but I didn't really get the logic behind it so heres an indentation example instead:


SELECT 
  CASE 
    WHEN x = y THEN 'something'
    WHEN x > y THEN 
      CASE WHEN x <> z THEN '...' ELSE END
    ELSE 'something else'
  END

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-10 : 09:08:14

Think it's

CASE WHEN inInvAdjLi.AdjActyType = 2 and inInvAdjLi.PrimQtyUM <> inInvAdjLi.OrigPrQtyUM
THEN inInvAdjLi.PrimQty * bcConvFactor.ToFactor
ELSE inInvAdjLi.PrimQty
END AS VarianceQty,

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-10 : 09:13:01

With the edit I don't know what's intended.
You can't have
case when <exp> case whenthen case when
it has to be
case when <exp> then case when ..

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-12-10 : 09:18:42

I've 2 case expressions on the field inInvAdjLi.PrimQty and these are:
1.

  CASE inInvAdjLi.AdjActyType WHEN 2
	     THEN (inInvAdjLi.PrimQty - inInvAdjLi.OrigPrQty)
	     ELSE 	inInvAdjLi.PrimQty
	   END AS VarianceQty,

the above is part of the original code and i'm trying to add the following case expression:
2.

  CASE 
	       WHEN inInvAdjLi.PrimQtyUM <> inInvAdjLi.OrigPrQtyUM THEN
		    (inInvAdjLi.PrimQty * bcConvFactor.ToFactor)
	       ELSE inInvAdjLi.PrimQty
	   END

this bit of code looks at the PrimQtyUM which can be KG for example and if that doesn't match the OrigPrQtyUM then there is a conversion look up table to multiply the PrimQty by however many items/kg there are. hope that makes sense.

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-12-10 : 09:21:58

quote:
Originally posted by nigelrivett

Think it's

CASE WHEN inInvAdjLi.AdjActyType = 2 and inInvAdjLi.PrimQtyUM <> inInvAdjLi.OrigPrQtyUM
THEN inInvAdjLi.PrimQty * bcConvFactor.ToFactor
ELSE inInvAdjLi.PrimQty
END AS VarianceQty,

Nigel doing this does make sense but from the original case expression

WHEN inInvAdjLi.AdjActyType = 2 THEN
(inInvAdjLi.PrimQty - inInvAdjLi.OrigPrQty)

this clause doesn't feature in the above logic you supplied

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-10 : 09:37:28

maybe
CASE WHEN inInvAdjLi.AdjActyType = 2
then inInvAdjLi.PrimQty - inInvAdjLi.OrigPrQty
when inInvAdjLi.PrimQtyUM <> inInvAdjLi.OrigPrQtyUM
THEN inInvAdjLi.PrimQty * bcConvFactor.ToFactor
ELSE inInvAdjLi.PrimQty
END AS VarianceQty,

Not sure whether the <> test is meant to be part of the = 2 test or not.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eljapo4
Posting Yak Master

100 Posts

Posted - 2010-12-15 : 11:45:00

this case expression now reads like:


	(CASE 
		WHEN inInvAdjLi.PrimQtyUM <> inInvAdjLi.OrigPrQtyUM THEN
			(inInvAdjLi.PrimQty * bcConvFactor.ToFactor)
			--ELSE (inInvAdjLi.PrimQty - inInvAdjLi.OrigPrQty)
			WHEN inInvAdjLi.AdjActyType = 2
			THEN (inInvAdjLi.PrimQty - inInvAdjLi.OrigPrQty)
		ELSE inInvAdjLi.PrimQty
	END) AS VarianceQty1,

I'm trying to say when the PrimQtyUM and OrigPrQtyUM are not equal do the conversion else (inInvAdjLi.PrimQty - inInvAdjLi.OrigPrQty) then I wish to proceed with the nested case. I'm getting a syntax error by trying to insert that first ELSE logic. Can someone please help me correct this nested case expression?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources