PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

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

On Thu, 20 Jun 2002 09:40:12 -0400, Lee Wenzler wrote:
>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.

Yes, I forgot to mention that you might need to re-order the order by clause. I would leave out the isnulls in the order by - they add nothing (as you've discovered) and cost time (infintestimal, probably).

>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
>


--
Matthew O. Persico