Convert part of RS before "order by" clause

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)

Convert part of RS before "order by" clause

Author

Topic

mitchman
Starting Member

2 Posts

Posted - 2002-05-16 : 11:20:23

I have to "order by" PO Num "asc" but I have returns of Null in some instances. The Nulls get displayed first before the not null values. I tried order by "desc" and that displayed the nulls last but obviously it displayed the not nulls "desc" as well. I was hoping to convert the Nulls to a number such as 9999999 and order by "asc" but I don't know if that is possible. If anyone can give me any advice on how to convert a part of a record set before the "order by" clause I would really appreciate it. Here is my SQL statement. "popon" is the field in question.

LEFT JOIN a_POmast ON a_shpfil.sffno = a_POmast.pofno
This is the left join that grabs the popon

Thanks in advance for your time.

Mitchman

SQLString = "SELECT sffno, sffdt, rt, sfvdt, sfedt, sfetc, sfvoy, sfvnm, sfexc, sfxdt, sfpcd,"_
& " imnme, popon, isdsc, canme, ffsfda, uscty, drlcst, drlmst "_
& "FROM (((((((A_SHPFIL "_
& " LEFT JOIN a_impmst ON a_shpfil.sfimp = a_impmst.impid)"_
& " LEFT JOIN a_POmast ON a_shpfil.sffno = a_POmast.pofno)"_
& " LEFT JOIN u_ISOFIL ON a_shpfil.sfexc = u_ISOFIL.iscde)"_
& " LEFT JOIN u_CarMst ON a_shpfil.sfcar = u_CarMst.caccd)"_
& " LEFT JOIN u_USPort ON a_shpfil.sfpcd = u_USPort.uscde)"_
& " LEFT JOIN DrelMst ON a_shpfil.sffno = DrelMst.drlfno)"_
& " LEFT JOIN UOGAFFL ON a_shpfil.sffno = LTRIM(UOGAFFL.ffsfil)) "_
& "WHERE ((a_shpfil.sffdt > '" & Date-45 & "' OR a_shpfil.sfvdt > '" & Date-45 & "') OR"_
& " (a_shpfil.sfvdt = '01/01/1901' OR a_shpfil.sfvdt = '01/01/1900')) "_
& " AND " & Session("SEC_CODE_REF") & ""_
& "ORDER BY popon desc, sffno"

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2002-05-16 : 11:32:03

Lookup COALESCE in BOL

....
ORDER BY COALESCE(popno, 99999999)

Edited by - YellowBug on 05/16/2002 11:32:42

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-16 : 11:33:15

okay, getting around nulls is always fun. In a prev post (fastest method of data format ) theres a bunch of null stuff there.. link isnull and the sort.

other option is case

select sffno, sffdt, rt, sfvdt, sfedt, sfetc, sfvoy,sfvnm, sfexc, sfxdt, sfpcd,"_& " imnme,
case when popon is null then '9999'
else popon
end
as 'popon', rest of fields
from....

the '9999' will make the anything null = 9999 otherwise it'll just leave the value there.

mitchman
Starting Member

2 Posts

Posted - 2002-05-16 : 12:14:06

Thanks for your replies. I got this from a different board and it works. It looks like the same basic idea as ME's solution. Thanks again!

order by case when popon is null then 0 else 1 end desc, popon"

Edited by - mitchman on 05/16/2002 12:14:31

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources