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

>