Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
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 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 =

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 =

Wt = 10


You would want to get Prod_ID 999 from row 6 back.





-----Original Message-----
[] 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:







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

statement (select ... from test_table one, test_table two) might be

as might running two separate selects in a union/union all operation.




> -----Original Message-----

> From:

> []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

> > the source columns can have null (don't care) values too. For

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

> matches my condition.


> You could also write a stored procedure that does this for you, but

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

> / ZetaTools, Inc /

> ZetaTools: Call perl functions as Sybase stored procedures!