Null value - 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 2000 Forums

SQL Server Development (2000)

Null value

Author

Topic

cardgunner

326 Posts

Posted - 2007-09-13 : 10:27:19

I can't get rid of a null value.


Select main.unit, 
       Case when datediff(d, main.date,current_timestamp) > 1095 then ''
		when datediff(d, main.date,current_timestamp) between 730 and 1096 then cast(round((price.bpris* 2),0) as varchar )+ 'PT Warranty'
		when datediff(d, main.date,current_timestamp) <730 then cast(round((price.bpris* 4),0) as varchar )+ 'Full Warranty'
		else 'Error' end as 'Ext Warranty',
        price.bpris as 'Price',
        datediff(d, main.date,current_timestamp) as 'dtdiff'

from main left join
     price on main.unit=price.unit

Unit Ext Warranty     Price   dtdiff
1    200PT Warranty   100     800
2    800Full Warranty 200     600
3                     300     2000
4    null               0     400

I tried 2 hours of different case statements and adding isnull where they shouldn't be and such. I'm tired and I want to get on to somewthing else. Any help?

The above code is reworded and taken from a much larger select statement. I tried to simplify it and make it as accurate as possible.

I was almost going to live with a result of "0PT Warranty" but my bosses wouldn't.

What I want is a '' instead of a null value.

Card Gunner

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-13 : 10:29:56

select ... isnull(your case, '') as 'Ext Warranty'
from...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 10:43:10

Border date diff of 1096, what should happen then?
Now it fulfill the first when bu default, but you have written the code to also accept the second when.

Select		main.unit, 
		ltrim(Case
			when datediff(d, main.date, current_timestamp) < 730 then str(isnull(price.bpris * 4, ''), 20, 0) + 'Full Warranty'
			when datediff(d, main.date, current_timestamp) between 730 and 1096 then str(isnull(price.bpris * 2, ''), 20, 0) + 'PT Warranty'
			when datediff(d, main.date, current_timestamp) > 1096 then ''
			else 'Error'
		end) as 'Ext Warranty',
		price.bpris as 'Price',
		datediff(d, main.date, current_timestamp) as 'dtdiff'
from		main
left join	price on price.unit = main.unit

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2007-09-13 : 10:56:42

Spirit,

I tried that. It tells me I

Server: Msg 174, Level 15, State 1, Line 14
The isnull function requires 2 arguments.

I started it: isnull(case...
ended it: ...end),'') as 'Ext Warranty'

Card Gunner

cardgunner

326 Posts

Posted - 2007-09-13 : 11:01:38

Peso,

You have helped me alot in the past. But I don't understand your reply. Border date diff of 1096? Not sure what you mean.

Also:
Now it fulfill the first when bu default, but you have written the code to also accept the second when.
I'm trying to figure out if there is a misspelling here or If I'm really this inexperienced. I have no idea what your trying to say. I'm sorry.

Card Gunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:06:26

The value that datediff(d, main.date,current_timestamp) returns are valid in two of your CASE WHEN statements!
If datediff(d, main.date,current_timestamp) returns 1096 exactly, both

> 1095

and

between 730 and 1096

are true. And because the > comparison is made first, the 1096 case is accepted there first, and not in the BETWEEN case comparison.

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2007-09-13 : 11:14:06

Oh. Good point. I will change it to >=1096. and <=730. Right?

I tried your suggestion above but it gave me the same results as a previous attempt

0PT Warranty or 0Full Warranty

I just want it blank. No value at all.

Card Gunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:19:43

I can't tell you what border limits to use.
Either change the BETWEEN to one number less for the second number,
or increase the number by 1 used in the > case.

Select		main.unit, 
		ltrim(Case
			when datediff(d, main.date, current_timestamp) < 730 then isnull(str(price.bpris * 4, 20, 0), '') + 'Full Warranty'
			when datediff(d, main.date, current_timestamp) between 730 and 1096 then isnull(str(price.bpris * 2, 20, 0), '') + 'PT Warranty'
			when datediff(d, main.date, current_timestamp) > 1096 then ''
			else 'Error'
		end) as 'Ext Warranty',
		price.bpris as 'Price',
		datediff(d, main.date, current_timestamp) as 'dtdiff'
from		main
left join	price on price.unit = main.unit

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2007-09-13 : 11:21:16

Oh!!! sorry Peso. I mistyped in my first opost it should have been >1096 however your correction is still valid is it not. I still need to make it >= and <= in case datediff lands on 1096 or 730.

Thanks I'm real new at this with NO formal training. As if you couldn't tell.

Card Gunner

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-13 : 11:21:50

change
isnull(case...
ended it: ...end),'') as 'Ext Warranty'
to
isnull(case...
ended it: ...end,'') as 'Ext Warranty'

there's no ) after end

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

cardgunner

326 Posts

Posted - 2007-09-13 : 11:31:28

Peso,
The result of your last answer is

"PT Warranty" or "Full Warranty".

I don't even want that. I want a null value without the word 'Null'.

Card Gunner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:37:14

Can't be done. However, you can get an EMPTY SPACE instead, just as '' only.

Select		main.unit, 
		ltrim(Case
			when datediff(d, main.date, current_timestamp) < 730 then isnull(str(price.bpris * 4, 20, 0) + 'Full Warranty', '') 
			when datediff(d, main.date, current_timestamp) between 730 and 1096 then isnull(str(price.bpris * 2, 20, 0) + 'PT Warranty', '')
			when datediff(d, main.date, current_timestamp) > 1096 then ''
			else 'Error'
		end) as 'Ext Warranty',
		price.bpris as 'Price',
		datediff(d, main.date, current_timestamp) as 'dtdiff'
from		main
left join	price on price.unit = main.unit

E 12°55'05.25"
N 56°04'39.16"

cardgunner

326 Posts

Posted - 2007-09-13 : 11:38:32

Spirit,

That worked beautifully. Thank you very much.

Card Gunner

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources