sybperl-l Archive

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

That looks much cleaner than what I've been doing (running multiple
selects and dropping out when a heavy one occurs) ... there's also an
identity column available, would that help speed things up any? 

Also, using the hash as you suggest, do you see a way to weight the null
columns, so I could have some columns having more pull in the fetch loop
in the case of equal numbers of nulls?

-----Original Message-----
From: owner-SYBPERL-L@list.cren.net
[mailto:owner-SYBPERL-L@list.cren.net] On Behalf Of Michael Peppler
Sent: Tuesday, June 18, 2002 3:13 PM
To: SybPerl Discussion List
Subject: RE: Sql or Sybperl advice needed

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!