Case expression error - 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 expression error

Author

Topic

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-01-05 : 08:18:34

hi can anyone help me correct the following code?

CASE WHEN fdCostRole.invstdoneUM <> inUserDefinedLi.PrimQtyUM THEN
        ( ELSE CASE WHEN inUserDefinedLi.PrimQtyUM = bcConvFactor.ToUM
              THEN (inUserDefinedLi.PrimQty * bcConvFactor.ToFactor)
         ELSE CASE WHEN inUserDefinedLi.PrimQtyUM = bcConvFactor.FromUM
              THEN (inUserDefinedLi.PrimQty / bcConvFactor.ToFactor)
         ELSE inUserDefinedLi.PrimQty
	 END )
END AS TEST

Should I be using an IF Statement instead?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-05 : 08:25:53

What do you want to do for this condition fdCostRole.invstdoneUM <> inUserDefinedLi.PrimQtyUM ?

Madhivanan

Failing to plan is Planning to fail

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-01-05 : 08:32:16

I want it to step into the brackets and determine which logic to apply depending on which UM the inUserDefinedLi.PrimQtyUM matches

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-05 : 08:45:10

Try this


CASE WHEN fdCostRole.invstdoneUM <> inUserDefinedLi.PrimQtyUM THEN
        (CASE WHEN inUserDefinedLi.PrimQtyUM = bcConvFactor.ToUM
              THEN (inUserDefinedLi.PrimQty * bcConvFactor.ToFactor)
         ELSE CASE WHEN inUserDefinedLi.PrimQtyUM = bcConvFactor.FromUM
              THEN (inUserDefinedLi.PrimQty / bcConvFactor.ToFactor)
         ELSE inUserDefinedLi.PrimQty
	 END )
END AS TEST

Madhivanan

Failing to plan is Planning to fail

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-01-05 : 09:06:13

I'm getting an 'incorrect syntax near ')'. error at this bit of code

CASE WHEN fdCostRole.invstdoneUM <> inUserDefinedLi.PrimQtyUM THEN
        (CASE WHEN inUserDefinedLi.PrimQtyUM = bcConvFactor.ToUM
              THEN (inUserDefinedLi.PrimQty * bcConvFactor.ToFactor)
         ELSE CASE WHEN inUserDefinedLi.PrimQtyUM = bcConvFactor.FromUM
              THEN (inUserDefinedLi.PrimQty / bcConvFactor.ToFactor)
         ELSE inUserDefinedLi.PrimQty
	 END ) --here
END AS TEST

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-05 : 09:39:55

[code]CASE WHEN fdCostRole.invstdoneUM <> inUserDefinedLi.PrimQtyUM THEN
(CASE WHEN inUserDefinedLi.PrimQtyUM = bcConvFactor.ToUM
THEN (inUserDefinedLi.PrimQty * bcConvFactor.ToFactor)
WHEN inUserDefinedLi.PrimQtyUM = bcConvFactor.FromUM
THEN (inUserDefinedLi.PrimQty / bcConvFactor.ToFactor)
ELSE inUserDefinedLi.PrimQty
END ) --here
END AS TEST
[/code]

Madhivanan

Failing to plan is Planning to fail

eljapo4
Posting Yak Master

100 Posts

Posted - 2011-01-05 : 11:49:29

Thank you for your help on this Madhivanan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-06 : 04:33:27