sybperl-l Archive

From: "Matthew O dot Persico" <persicom at acedsl dot com>
Subject: RE: Sql or Sybperl advice needed
Date: Jun 20 2002 3:01AM

On 19 Jun 2002 07:28:35 -0700, Michael Peppler wrote:
>On Tue, 2002-06-18 at 19:11, Matthew O. Persico wrote:
>>
>>Or what about:
>>
>>select
>>Prod_ID, Account_Type, Rate_Code, Product_Line, Wt_Min, Wt_Max
>>from
>>info_table
>>where
>>@acct_type = isnull(Account_Type,@acct_type)
>>and  @rate_code = isnull(Rate_Code,@rate_code)
>>-- ditto for Product_Line
>>and     @weight bewteen isnull(Wt_Min,@weight)
>>and isnull(Wt_Max,@weight)
>>order by --all the columns, either asc or desc, whichever puts the
>>NULL row AFTER the non-nulls
>
>Neat idea - you can use isnull() in the order by clause to achieve
>the
>correct order.

Actually, you just sort by the column names; I don't think you need the isnulls in the sort...

Ok. I give. Time to boot up the linux box next to me and try it out.

Power switch on..
Booting...
Typing...

Ok. The order by does NOT use nulls and it is desc. Using the data from the example, substituting hardcoded strings for the @variables in the queries so that I don't need to repeat declare statements:

create table info_table ( Account_Type varchar(3) null,Rate_Code varchar(9) null, Product_Line varchar(10) null, Wt_Min numeric(3,1) null,Wt_Max numeric(3,1) null, Prod_ID int)
go

insert into info_table values('Web','FirstTime','Babblefish',2,2.9,432)
insert into info_table values('Web','FirstTime','Babblefish',3,3.9,222)
insert into info_table values('Web','FirstTime','Babblefish',4,4.9,666)
insert into info_table values('Web','FirstTime','Babblefish',5,5.9,123)
insert into info_table values('Web',null,'Babblefish',null,null,999)
go

select * from info_table;

 Account_Type Rate_Code Product_Line Wt_Min Wt_Max Prod_ID
 ------------ --------- ------------ ------ ------ -----------
 Web          FirstTime Babblefish      2.0    2.9         432
 Web          FirstTime Babblefish      3.0    3.9         222
 Web          FirstTime Babblefish      4.0    4.9         666
 Web          FirstTime Babblefish      5.0    5.9         123
 Web          NULL      Babblefish     NULL   NULL         999

select
Prod_ID, Account_Type, Rate_Code, Product_Line, Wt_Min, Wt_Max
from
info_table
where 'Web' = isnull(Account_Type,'Web')
and 'FirstTime' = isnull(Rate_Code,'FirstTime')
and 'Babblefish' = isnull(Product_Line,'Babblefish')
and 10 between isnull(Wt_Min,10)
           and isnull(Wt_Max,10)
order by Account_Type desc, Rate_Code desc, Product_Line desc;

 Prod_ID     Account_Type Rate_Code Product_Line Wt_Min Wt_Max
 ----------- ------------ --------- ------------ ------ ------
         999 Web          NULL      Babblefish     NULL   NULL

-- Here, we got the one row we wanted

select 
Prod_ID, Account_Type, Rate_Code, Product_Line, Wt_Min, Wt_Max 
from 
info_table 
where 'Web' = isnull(Account_Type,'Web') 
and 'FirstTime' = isnull(Rate_Code,'FirstTime') 
and 'Babblefish' = isnull(Product_Line,'Babblefish') 
and 4.2 between isnull(Wt_Min,4.2) 
             and isnull(Wt_Max,4.2) 
order by Account_Type desc, Rate_Code desc, Product_Line desc;
 Prod_ID     Account_Type Rate_Code Product_Line Wt_Min Wt_Max
 ----------- ------------ --------- ------------ ------ ------
         666 Web          FirstTime Babblefish      4.0    4.9
         999 Web          NULL      Babblefish     NULL   NULL

-- Here, the row we really want is the first. Get it and then canquery.

--
Matthew O. Persico