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 19 2002 3:20PM

Gotcha ... paranoid is good. If you were starting from scratch and
multi-columned translation tables were needed how would you approach it?


The spec called for all the source db translation fields to be optional,
with the program determining the best translation based on the most hits
in the source db fields compared to the translation fields.

All the raw data already lived on Sybase, so it seemed like the best
place to put them, but now I'm wondering if it would have been better to
somehow get it all preloaded into perl and then use hashes??


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

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!