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: "Lee Wenzler" <leew at roanoketimes dot com>
Subject: RE: Sql or Sybperl advice needed
Date: Jun 18 2002 8:13PM

Yes! That looks exactly like what I was looking for. Although, I'm not
seeing the fragility of it. If my program has complete control over the
table (it's not used by any other apps), shouldn't I be able to just run
a sanity check on it's contents at the beginning of each run?

Thanks,
Lee


-----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:52 PM
To: SybPerl Discussion List
Subject: RE: Sql or Sybperl advice needed

The identity column won't speed things up (based on my understanding of
the problem), but it could help to disambiguate the query or the
returned data (if necessary).

As for weighting the various columns - that could be done fairly easily,
maybe like this:

%col_weight = (Account_Type => 1, Rate_Code => 2, Product_Line => 1,
...);

while(%data = $dbh->ct_fetch(CS_TRUE)) {
   $count = 0;
   for (keys(%data)) {
      $count += $col_weight{$_} unless defined(data{$_});
   }
   $prod_id{$count} = $data{Prod_ID};
}

with the rest of the code unchanged.

Here we ask ct_fetch to return a hash instead of an array (so that we
have all the column names), and we've created a hash with a weight for
each column (not filled out completely above, obviously).

Now if $data{$_} is undefined (i.e. NULL) we take the corresponding
weight from %col_weight and add it to $count - so in this case a row
that has Account_Type NULL is favored over a row that has Rate_Code
NULL. This won't completely remove possible duplicates, of course
(unless you choose your weights very carefully).

Note however that it's still quite a hack - and potentially fragile
should the data change significantly...

Michael


On Tue, 2002-06-18 at 12:40, Lee Wenzler wrote:
> 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!
> 
> 
-- 
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!