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: Michael Peppler <mpeppler at peppler dot org>
Subject: RE: Sql or Sybperl advice needed
Date: Jun 18 2002 7:12PM

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)

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.
> 
> 
-- 
Michael Peppler / mpeppler@peppler.org / http://www.mbay.net/~mpeppler
mpeppler@zetatools.com / ZetaTools, Inc / http://www.zetatools.com
ZetaTools: Call perl functions as Sybase stored procedures!