NULL - How can I ? - 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 - How can I ?

Author

Topic

tomjacob
Starting Member

8 Posts

Posted - 2002-02-06 : 16:20:06

I have two critical questions. Please help.

1) I have to sort a varchar field(sql 7). The first character of the field will be always an Interger between 1 and 9. I want in the order 1,2,3--- etc . But the catch is , If i just say ORDER BY myfield all Null values are coming first. I am looking for a query which will push all null values to the bootom of my list ?

2) All datetime in our databse are stored in GMT. So I have to substract -6 when i display the actual time. Here also the catch is, if the values is NULL ( that 1753 ...etc) and when I substract it, it is giving errors. What I want is a query, which will display NULL if it is null or 6 hour less if If it is not null.

Once again, Thanks in advance.

izaltsman
A custom title

1139 Posts

Posted - 2002-02-06 : 16:28:06

You can use CASE function:

1)
ORDER BY CASE WHEN mysortfld is null THEN 'zzzzzzz' ELSE mysortfield END

2)
CASE WHEN mydate IS NULL THEN NULL ELSE DATEADD(hh, 6, mydate) END

tomjacob
Starting Member

8 Posts

Posted - 2002-02-06 : 16:40:52

Thanks a lot. Can you give me a example. If time permits, please.

quote:

You can use CASE function:

1)
ORDER BY CASE WHEN mysortfld is null THEN 'zzzzzzz' ELSE mysortfield END

2)
CASE WHEN mydate IS NULL THEN NULL ELSE DATEADD(hh, 6, mydate) END

izaltsman
A custom title

1139 Posts

Posted - 2002-02-06 : 18:33:10

Here you go... I modified the ORDER BY clause a bit to make it less dependent on the field datatype (not that it matters). And in the second example I originally overlooked the '-' sign (so it added 6 instead of subtracting)... That's what happens when you don't review something before posting.

Anyhow, this time I actually checked things over.


use pubs
go

--Create some sample data

create table mytable (blah int, mysortfld varchar (7), mydate datetime)

insert into mytable values (rand() * 10, '6xyxkfd', CONVERT(datetime, '03:12:00',108))
insert into mytable values (rand()* 10, null, CONVERT(datetime, '07:12:00',108))
insert into mytable values (rand()* 10, '5xyxkfd', CONVERT(datetime, '09:12:00',108))
insert into mytable values (rand()* 10, '4xyxkfd', CONVERT(datetime, '10:22:00', 108))
insert into mytable values (rand()* 10, '3xyxkfd', null)
insert into mytable values (rand()* 10, '5xyxkfd', CONVERT(datetime, '02:12:00',108))
insert into mytable values (rand()* 10, null, CONVERT(datetime, '11:12:00',108))
insert into mytable values (rand()* 10, '1xyxkfd', CONVERT(datetime, '12:12:00',108))
insert into mytable values (rand()* 10, '2xyxkfd', CONVERT(datetime, '08:12:00',108))
insert into mytable values (rand()* 10, '1xyxkfd', null)

-- return everything
select * from mytable


-- nulls sorted at the end 
SELECT blah, mysortfld
FROM mytable
ORDER BY CASE WHEN mysortfld is null THEN 2 ELSE 1 END, mysortfld

-- 6hrs subtracted unless date is null
SELECT blah, 
       CASE WHEN mydate IS NULL THEN NULL ELSE DATEADD(hh, -6, mydate) END as dates
FROM mytable

drop table mytable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources