sybperl-l Archive

From: "Scott Zetlan" <scottzetlan at aol dot com>
Subject: RE: Sql or Sybperl advice needed
Date: Jun 18 2002 6:48PM

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.