August 7, 2008 at 11:26 am
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?
August 7, 2008 at 1:40 pm
Dynamic SQL.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 7, 2008 at 2:30 pm
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)
...
August 11, 2008 at 2:14 pm
Care to elaborate?
August 11, 2008 at 2:17 pm
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.
August 11, 2008 at 2:28 pm
Like posted earlier you should consider building the query dynamically...
* Noel
August 11, 2008 at 2:43 pm
steve (8/11/2008)
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.
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)
...
August 12, 2008 at 4:10 pm
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
August 12, 2008 at 7:29 pm
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.
August 13, 2008 at 2:11 pm
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.
August 13, 2008 at 2:18 pm
i meant no offense. i'm just saying that sometimes there are no shortcuts or tricks. sometimes you gotta code what you gotta code even if it is laborious.
August 13, 2008 at 2:42 pm
The ways that I know of to do this:
1. Disabled WHERE clauses: This is what you currently have and you and antonio were discussing. Problems: parameter-sniffing and other query plan issues.
2. Dynamic SQL: You dynamically construct your query (including Where clauses) from the parameters. Problems: must protect against SQL Injection, some (small) compile overhead. [edit:]can be hard to read and more difficult to maintain.
3. Decision Tree: Nested IF..ELSE's provide a separate branch for every combination of parameters present, where separate queries are executed. Problems: query plans not usually optimized, number of branches grow exponentially, maintenance overhead.
4. Decision Tree & sub-Procs: Same as (3) except each branch calls a seperate proc with the actual query to solve query plan issues. Problems: number of branches grow exponentially, maintenance overhead.
5. Shallow IF Tree: Same as (4) (or (3)) except that only the most important (remaining) parameter is tested. The proc (or query) executed has the first found parameter hard-coded, the rest have logical disabling like (1). Problems: still complicated, still some maintenance overhead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 13, 2008 at 2:45 pm
append to #2: dynamic SQL. can be hard to read and more difficult to maintain.
August 13, 2008 at 3:01 pm
Agreed.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 14, 2008 at 8:50 am
Here's a thought... though I don't know if it's advantageous in your environment.
What if you created local temp tables (or table variables) in a sproc and loaded single values or select ranges into the temp tables? You could then join your temp tables to your query. Admittedly, If the goal is simply to return a list of the resulting hierarchy this would be stupid to do... but if you are returning a more complex query and this is only a small part of the where clause or join than this might be a performance booster.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply