adding a 'null' case - 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

General SQL Server Forums

New to SQL Server Programming

adding a 'null' case

Author

Topic

hardwood
Starting Member

19 Posts

Posted - 2010-06-09 : 11:31:44

So i have this line in my query:

select
case when charindex(';',d.Tax) > 0 then convert(decimal(10,2),substring(d.Tax,1,charindex(';',d.Tax)-1))
else convert(decimal(10,2),d.Tax) end as Tax

which works perfectly,

but I want to add a case that says if it = 0 then to be null. I tried to just case the entire thing:

case when(case when charindex(';',d.Tax) > 0 then convert(decimal(10,2),substring(d.Tax,1,charindex(';',d.Tax)-1))
else convert(decimal(10,2),d.Tax) end as Tax) >0, Tax, else null end)

but it didn't work. Any tips?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-09 : 12:06:19

change:
else convert(decimal(10,2),d.Tax) end as Tax) >0, Tax, else null end)

to:
else convert(decimal(10,2),substring(d.Tax,1,charindex(';',d.Tax)-1) >0, convert(decimal(10,2),substring(d.Tax,1,charindex(';',d.Tax)-1), else null end)

I might be off on the Parenthsis, but you get teh idea.

hardwood
Starting Member

19 Posts

Posted - 2010-06-09 : 12:26:37

I'm using Query Express so I have to eyeball the parenthesis, I think there are two missing here:

case when(case when charindex(';',d.Tax) > 0 then convert(decimal(10,2),substring(d.Tax,1,charindex(';',d.Tax)-1))
else convert(decimal(10,2),substring(d.Tax,1,charindex(';',d.Tax)-1) >0,
convert(decimal(10,2),substring(d.Tax,1,charindex(';',d.Tax)-1), else null end)

hardwood
Starting Member

19 Posts

Posted - 2010-06-09 : 12:42:01

quote:
Originally posted by Lamprey

change:
else convert(decimal(10,2),d.Tax) end as Tax) >0, Tax, else null end)

to:
else convert(decimal(10,2),substring(d.Tax,1,charindex(';',d.Tax)-1) >0, convert(decimal(10,2),substring(d.Tax,1,charindex(';',d.Tax)-1), else null end)

I might be off on the Parenthsis, but you get teh idea.

so I decided maybe I could just insert a null in the original syntax as such:

case when charindex(';',d.Tax) > 0 then convert(decimal(10,2),substring(d.Tax,1,charindex(';',d.Tax)-1))
else null end as Tax

but it still returns 0's

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-06-09 : 12:52:11

[code]--Sample data
DECLARE @Foo TABLE (Tax VARCHAR(20))

INSERT @Foo
SELECT '1234'
UNION ALL SELECT '12.12;f'
UNION ALL SELECT '0;r'

SELECT
CASE
WHEN CHARINDEX(';', d.Tax) > 0
THEN
CASE
WHEN CONVERT(DECIMAL(10,2), SUBSTRING(d.Tax, 1, CHARINDEX(';', d.Tax) -1)) > 0
THEN CONVERT(DECIMAL(10,2), SUBSTRING(d.Tax, 1, CHARINDEX(';', d.Tax) -1))
ELSE NULL
END
ELSE NULL
END AS Tax
FROM
@Foo AS d[/code]Edit forgot my sample data.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-06-09 : 14:02:22

You can wrap the whole thing in a NULLIF to convert the 0 to a NULL:

NULLIF(case when charindex(';', d.Tax) > 0
then convert(decimal(10,2), substring(d.Tax, 1, charindex(';', d.Tax) - 1))
else convert(decimal(10,2), d.Tax)
end, 0) As Tax

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources