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 11:33PM

Well - if you control all parts of the system you're probably OK.

The issue that I could see here is in 6 months or a year, when somebody
else is tasked with updating that program ...

But maybe I'm just paranoid :-)

Michael

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