|
|
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 4:43PM
Here's maybe a better example of what I'm trying to get working:
1. Account_Type varchar(16)
2. Group_Name varchar(16)
3. Ordered tinyint
4. Rate_Code varchar(5)
5. Inv_ID_Start int
6. Inv_ID_End int
7. Product_Line varchar(16)
8. Wt_Min int
9. Wt_Max int
10. Prod_ID int
The idea is you want to use the first 9 columns to be able to point you
to the Prod_ID. The users want to be able to have all the way from the
most specific criteria (using all 9 columns) to most generic (having
only Rate_Code perhaps) to select the Prod_ID.
Say you have 5 rows with only this info:
Account_Type = "Web", Rate_Code = "FirstTime",
Product_Line = "Babblefish",
Wt_Min and Wt_Max values of 5 different ranges.
(Prod_ID is what you're seeking).
Then say another row has only
Account_Type = "Web",
Rate_Code = null, Product_line = "Babblefish",
Wt_Min & Wt_Max have nulls (don't care values).
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.
Lee
-----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 11:40 AM
To: SybPerl Discussion List
Subject: RE: Sql or Sybperl advice needed
It's tough to suggest a good solution without knowing what data we're
modeling and what problem we're solving, but it sounds like some boolean
math is in order:
select
x
from
test_table
where
( (a is null and b is null) or (@my_range between a and b) )
and ( (c is null and d is null) or (@my_range between c and d) )
might work. Also, creating two instances of the test table in the
select
statement (select ... from test_table one, test_table two) might be
useful,
as might running two separate selects in a union/union all operation.
Scott
> -----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 10:48 AM
> To: SybPerl Discussion List
> Subject: Re: Sql or Sybperl advice needed
>
>
> On Tue, 2002-06-18 at 07:27, Lee Wenzler wrote:
> > Sybperl hash advice needed:
> >
> >
> >
> > I'm using Sybase::Simple and need a method of selecting a row from a
> > table used to translate from one system to another where there are
> > matches of one more columns, but I want the row that has the most
> > matches (I'm hoping to use some type of hash table for this). All
of
> > the source columns can have null (don't care) values too. For
example,
> > with a table that has these columns:
>
> The solution that I'd use would probably depend on how many rows match
> the minimal condition (i.e. a / b not null, or d/e not null if I
> understand correctly).
>
> If the number of rows is relatively small I might load the minimal
> condition to a hash or array of some sort, and then find the entry
that
> matches my condition.
>
> You could also write a stored procedure that does this for you, but
that
> would have to iteratively query the table until it got a hit, I think.
>
> Maybe someone else has a better idea on how to solve this sort of
> problem...
>
> Michael
> --
> 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!
>
|
