|
|
sybperl-l Archive
Up Prev Next
From: "Matthew O dot Persico" <persicom at acedsl dot com>
Subject: RE: Sql or Sybperl advice needed
Date: Jun 19 2002 2:11AM
On 18 Jun 2002 12:12:59 -0700, Michael Peppler wrote:
>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)
Or what about:
select
Prod_ID, Account_Type, Rate_Code, Product_Line, Wt_Min, Wt_Max
from
info_table
where
@acct_type = isnull(Account_Type,@acct_type)
and @rate_code = isnull(Rate_Code,@rate_code)
-- ditto for Product_Line
and @weight bewteen isnull(Wt_Min,@weight)
and isnull(Wt_Max,@weight)
order by --all the columns, either asc or desc, whichever puts the NULL row AFTER the non-nulls
Then just use the Sybperl equivilent of DBI's selectrow_array() if there is one. If not, fetch one row and cancel the query. You should then have your data.
Note that this is all untested; I am pulling it from memory of stuff at work when I am here at home.
>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.
>>
>>
--
Matthew O. Persico
|
