if chgb =0, use value from chgA

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

General SQL Server Forums

New to SQL Server Programming

if chgb =0, use value from chgA

Author

Topic

Alan1018
Starting Member

13 Posts

Posted - 2014-06-18 : 09:43:53

I am trying to do a query where the column ChgB is either null or has a value, and the column ChgA always has a value. When ChgB has a value I want that value returned, but if ChgB is Null I want the value from ChgA.
In Access SQL this is simple : SELECT Time.ProjectCode, IIf([ChgB]=0 Or IsNull([ChgA]),[ChgB] FROM dbo_Time; This is useless in SQL 2008.

I tried this:
--Case
-- When Time.ChgB = 0
-- Then @Amt = Time.ChgA
-- When Time.ChgB is Null
-- Then @Amt = Time.ChgA
--Else @Amt = Time.ChgB
--End
Both with a declared variable @Amt and Like this:
--Case
-- When Time.ChgB = 0
-- Then Time.ChgB = Time.ChgA
-- When Time.ChgB is Null
-- Then Time.ChgB = Time.ChgA
--Else Time.ChgB = Time.ChgB
--End

The case statements will work if I put in text or a number but doesn't seem to be able to use the value from ChgA

Thanks,
Alan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-06-18 : 09:54:11

the assignment has to be outside the case statement...

@x=case when nullif(chgB,0) is null then chgA else chgB end

or without case:
@x=coalesce(nullif(chgB,0), chgA)

Too old to Rock'n'Roll too young to die.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-18 : 09:55:00

Your syntax is wrong. Case statements in SQL don't work like that. the syntax should be:


... @Amt = case 
     when Time.ChgB = 0 then Time.ChgA
     when Time.ChgB is null then Time.ChgA
     else Time.ChB

However, you can avoid CASE altogether like this:


@amt = isnull(nullif(Time.ChgB, 0), Time.ChgA)

Alan1018
Starting Member

13 Posts

Posted - 2014-06-18 : 10:43:50

Thanks,

ChgB = isnull(nullif(Time.ChgB, 0), Time.ChgA)

This did it, when I tried it as a variable the message was you can't assign a variable in a data retrieval query so I just changed the ChgB value.

Thanks,
Alan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources