sybperl-l Archive

From: "Lee Wenzler" <leew at roanoketimes dot com>
Subject: RE: Sql or Sybperl advice needed
Date: Jun 20 2002 1:40PM

I tried this with the real tables and it worked as advertised. I tried
the order by with & without the isnulls and got the same results. The
users sometimes put in data that could cause equally valid rows to be
selected. I changed the order by, to have the more important fields
listed first and that made best case always come up first. 
Thanks,
Lee



-----Original Message-----
From: owner-SYBPERL-L@list.cren.net
[mailto:owner-SYBPERL-L@list.cren.net] On Behalf Of Matthew O. Persico
Sent: Wednesday, June 19, 2002 11:02 PM
To: SybPerl Discussion List
Subject: RE: Sql or Sybperl advice needed

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