|
|
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!
|
