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 19 2002 2:11AM

On 18 Jun 2002 12:12:59 -0700, Michael Peppler wrote:
>On Tue, 2002-06-18 at 12:00, Lee Wenzler wrote:
>>When I've tried that logic, what returned was the row that
>>qualified and
>>also any of the null valued rows.
>
>That's what I figured - Prod_ID isn't unique.
>
>You could include the additional columns in the SELECT list, and then
>perform some logic where the minimum number of additional columns is
>NULL.
>
>For example:
>
>select
>Prod_ID, Account_Type, Rate_Code, Product_Line, Wt_Min, Wt_Max
>from
>info_table
>where
>(Account_Type is null or Account_Type = @acct_type)
>and (Rate_Code is null or Rate_Code = @rate_code)
>-- ditto for Product_Line
>and ( (Wt_Min is null and Wt_Max is null) or @weight between
>Wt_Min
>and
>Wt_Max)
 
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

Then just use the Sybperl equivilent of DBI's selectrow_array() if there is one. If not, fetch one row and cancel the query. You should then have your data.

Note that this is all untested; I am pulling it from memory of stuff at work when I am here at home.

>and in the fetch loop:
>
>while(@data = $dbh->ct_fetch) {
>$count = 0;
>for (@data) {
>++$count unless $_;
>}
>$prod_id{$count} = $data[0];
>}
># now return the prod_id where $count is smallest:
># The statement below takes all the keys that we've just added to
>the
># %prod_id hash, sorts them, and then gets the first element of that
># sorted array and assigns it to $min.
>$min = (sort(keys(%prod_id)))[0];
>
>return $prod_id{$min};
>
>While I think this will do what I think you want, it takes quite a
>lot
>of short-cuts, one of which is that if two rows with the same number
>of
>NULLs match then the last one fetched is the one returned. This may
>or
>may not be a problem for you.
>
>Michael
>
>>-----Original Message-----
>>From: owner-SYBPERL-L@list.cren.net
>>[mailto:owner-SYBPERL-L@list.cren.net] On Behalf Of Scott Zetlan
>>Sent: Tuesday, June 18, 2002 2:49 PM
>>To: SybPerl Discussion List
>>Subject: RE: Sql or Sybperl advice needed
>>
>>In that case, a SQL statement that looked like this should work:
>>
>>select
>>Prod_ID
>>from
>>info_table
>>where
>>(Account_Type is null or Account_Type = @acct_type)
>>and (Rate_Code is null or Rate_Code = @rate_code)
>>-- ditto for Product_Line
>>and ( (Wt_Min is null and Wt_Max is null) or @weight between
>>Wt_Min
>>and
>>Wt_Max)
>>
>>Although you might want to add some extra conditions for when
>>Wt_Min is
>>null
>>but Wt_Max is not, or vice versa.
>>
>>HTH,
>>
>>Scott
>>
>>-----Original Message-----
>>From: owner-SYBPERL-L@list.cren.net
>>[mailto:owner-SYBPERL-L@list.cren.net]On
>>Behalf Of Lee Wenzler
>>Sent: Tuesday, June 18, 2002 12:44 PM
>>To: SybPerl Discussion List
>>Subject: RE: Sql or Sybperl advice needed
>>
>>
>>Row   Account_Type   Rate_Code   Product_Line   Wt_Min   Wt_Max
>>Prod_ID
>>1       Web         FirstTime   Babblefish     1        1.9
>>234
>>2       Web         FirstTime   Babblefish     2        2.9
>>432
>>3       Web         FirstTime   Babblefish     3        3.9
>>222
>>4       Web         FirstTime   Babblefish     4        4.9
>>666
>>5       Web         FirstTime   Babblefish     5        5.9
>>123
>>6       Web         null        Babblefish     null     null
>>999
>>
>>
>>Now say you're looking for the Prod_ID with this info
>>Account_type = "Web", Rate_Code = "FirstTime", Product_Line =
>>"Babblefish",
>>Wt = 2.5
>>
>>The Prod_ID 432 from row 2 should come back.
>>
>>But if you had this:
>>Account_type = "Web", Rate_Code = "FirstTime", Product_Line =
>>"Babblefish",
>>Wt = 10
>>
>>You would want to get Prod_ID 999 from row 6 back.
>>
>>
 
 
--
Matthew O. Persico