Re: Please help me with a simple query!!!

"Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message news:43a251cb$1_at_news.victoria.tc.ca...
> grohrer_at_gmail.com wrote:
>
> (oh no, top posting!)
>
> : I DO know the widgets I want to return, for example
>
> : Dept Product
> : 01 002312
> : 02 023124
> : 01 002315
> : 05 002312
> : ...
>
> : As you can see, there are two product numbers with 002312 but each has
> : a different department. In all I have a hundred or more KNOWN widgets
> : that I want to return.
>
> : My question is how can I build an effecient query against a table when
> : I have about a hundred or so items like the above, where I need to
> : query against the department number AND product number.
>
> : Do I do the long where clause for about a hundred of these..
> : ..
> : WHERE (dept = '01' AND product = '002312) OR (dept = '02' AND product =
> : '023124') OR...
>
> : Or is there a better way? The above example will make for a REALLY
> : long query string.
>
> You could put them in a table and then use various techniques to make use
> of the table.
>
> -- _NOT_ valid sql, just for illustration
>
> create table valid_combos (Dept, Product);
> insert into valid_combos (Dept, Product) values ('01','002312');
> insert into valid_combos (Dept, Product) values ('02','023124');
> ... etc...
>
> You could then join with that table
>
> select *
> from
> main_table , valid_combos
> where
> main_table.Dept = valid_combos.Dept
> and main_table.Product = valid_combos.Product
> and other conditions to filter the result
>
> or use something like this (but lookup the syntax cause I might have it
> wrong
>
> select *
> from
> main_table
> where (Dept,Product) in (select Dept,Product from valid_combos)
>
>
> or
>
> select *
> from
> main_table mt
> where
> exists (select *
> from valid_combos vc
> where mt.Dept = vc.Dept
> and mt.Product = vc.Product
> )
>
> and probably others as well
>
>

and you'll probably want the table to ba a global temporary table -- create it once as part of your application design, not each time this type of query needs to be run

another alternative is to create a pipelined function that transforms the list of value-pairs into a table structure (see http://www.psoug.org/library.html for references on both techniques)

++ mcs Received on Fri Dec 16 2005 - 05:39:58 CST