many possible input combinations

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » many possible input combinations

16 posts, Page 1 of 2

1 2 »»

many possible input combinations

Rate Topic

Display Mode

Topic Options

Author

Message

Steve-524674

Posted Thursday, August 07, 2008 11:26 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, August 22, 2011 1:46 PM
Points: 16, Visits: 114

I am working on an application with a hierarchy. The hierarchy includes company, region, division, district, store. The existing input screen includes from and to ranges that employees may enter values on. They always enter a company, but I will not know what other options they may select and would like to make the application flexible.

Problem: The number of possible present and absent values make writing a sproc to support this a pain.

I can code "IF" statements until tomorrow, like...

IF @i_fr_corpid <> '' AND @i_to_corpid = ''
AND @i_fr_regid = '' AND @i_to_regid = ''
AND @i_fr_divid = '' AND @i_to_divid = ''
AND @i_fr_distid = '' AND @i_to_distid = ''
AND @i_fr_storeid = '' AND @i_to_storeid= ''

I would probably need more than 20 such statements to cover all possibilities.

I can retrieve the records for an entire company and let the application select down from there.

I can padd the fields and concatenate them together like one big value for "from" and "to."

SOOOOO, my next statement after I know what values have been supplied has to include the appropriate ranges or specific values depending on the input.

Has anyone got a different approach to this that simplifies evaluation for many purmutations of input values?

Post #548531

RBarryYoung

Posted Thursday, August 07, 2008 1:40 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Saturday, May 04, 2013 11:13 AM
Points: 9,855, Visits: 9,374

Dynamic SQL.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Post #548633

antonio.collins

Posted Thursday, August 07, 2008 2:30 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:31 PM
Points: 437, Visits: 883

you could replace blank values with infinity values for the datatype and range on those or you could also just replace blank values with the compared column like so:

select ...
from [some table] T ...
where ...
and T.i_regid between isnull(nullif(@i_fr_regid, ''),T.i_regid) 
    and isnull(nullif(@i_to_regid, ''),T.i_regid)
and T.i_divid like isnull(nullif(@i_fr_divid, ''),T.i_divid) 
    and isnull(nullif( @i_to_divid, ''),T.i_divid)
...

Post #548670

Steve-524674

Posted Monday, August 11, 2008 2:14 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, August 22, 2011 1:46 PM
Points: 16, Visits: 114

Care to elaborate?

Post #550592

Steve-524674

Posted Monday, August 11, 2008 2:17 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, August 22, 2011 1:46 PM
Points: 16, Visits: 114

I can understand using the high-values in the "TO" part of the range to eliminate extra code. However, I am interested in a specific value when I provide one [as in the "FROM"] and a range of values when I supply FROM and TO.

if regid_from = 100

or

if regid_from >= 100 and regid_to <= 999999999999999

So this helps cut down the overall, but I was still thinking there must be a better way.

Post #550593

noeld

Posted Monday, August 11, 2008 2:28 PM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260, Visits: 1,977

Like posted earlier you should consider building the query dynamically...

* Noel

Post #550600

antonio.collins

Posted Monday, August 11, 2008 2:43 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:31 PM
Points: 437, Visits: 883

steve (8/11/2008)

pre-process your parameters and if a "to" value is omitted, substitute the "from" value. that will result in a between comparison with both the upper and lower values being the same (effectively an equal comparison).

set @i_fr_regid = nullif(@i_fr_regid,'')
set @i_to_regid = ifnull(nullif(@i_to_regid,''), @i_fr_regid)
... and so on for other parameters ...
select ...
from [some table] T ...
where ...
and T.i_regid between isnull(@i_fr_regid, T.i_regid) 
    and isnull( @i_to_regid, T.i_regid)
and T.i_divid between isnull(@i_fr_divid,T.i_divid) 
    and isnull( @i_to_divid, T.i_divid)
...

Post #550618

Steve-524674

Posted Tuesday, August 12, 2008 4:10 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, August 22, 2011 1:46 PM
Points: 16, Visits: 114

Thanks for your thoughts on this. It seems like a fairly common problem without a really good solution. Although some of the suggestions herein work to reduce the number of different statements that must be tried, I just can't help but believe there isn't a better way.

Thanks to you all for writing!

--Steve

Post #551479

antonio.collins

Posted Tuesday, August 12, 2008 7:29 PM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:31 PM
Points: 437, Visits: 883

i'm surprised that you're surprised by this. if you want to find people based on height, weight, age, gender, race, hair color, and eye color then you're going to have at least 7 conditions in your where clause. there's no way around that fact since you're using 7 different attributes. you can either use a technique similar to the one i've described or use dynamic sql.

Post #551526

Steve-524674

Posted Wednesday, August 13, 2008 2:11 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, August 22, 2011 1:46 PM
Points: 16, Visits: 114

You may have been in diapers when I started in database work on DB2 V2. I am not surprised that lots of combinations means lots of code -- I just want to explore a better way. I want to explore the possibilities. Einstein said, "Innovation is not the product of logical thought, even though the final product is tied to a logical structure."

I like pursuing innovation, even when it does not seem logical to someone else. My experience suggests that if you kick a problem like this around long enough, you can come up with some interesting alternatives. I have explored several other options that no one mentioned in this thread, like using hashes and datawarehouse-like fact tables. These are worth investigating and testing, although no one mentioned them.

Just because I have encountered the problem before, does not mean that I shouldn't look for more interesting ways to solve the problem.

Post #552261

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions