I have a table which contains information regarding widgets. Each
widget has a Department Number and a Product Number.

I need to query the table for a specific set of widgets...about 100 in
all.

I'm familiar with the basic queries such as:

SELECT *
FROM projectTable
WHERE prodNum IN ('002312','023124','254124')

which works great if your only querying with a single specific field.

The only other method I know if is to do the following:

SELECT *
FROM projectTable
WHERE (deptNum = '01' AND prodNum = '002312') OR (deptNum = '02' AND
prodNum = '002314') OR .......

Using this method for a hundred widgets would make for a pretty sizable
query string.

Is there another method which is better and effecient?

Thanks in Advance!

grohrer@gmail.com wrote:
> I have a table which contains information regarding widgets. Each
> widget has a Department Number and a Product Number.
>
> I need to query the table for a specific set of widgets...about 100 in
> all.
>
> I'm familiar with the basic queries such as:
>
> SELECT *
> FROM projectTable
> WHERE prodNum IN ('002312','023124','254124')
>
> which works great if your only querying with a single specific field.
>
> The only other method I know if is to do the following:
>
> SELECT *
> FROM projectTable
> WHERE (deptNum = '01' AND prodNum = '002312') OR (deptNum = '02' AND
> prodNum = '002314') OR .......
>
> Using this method for a hundred widgets would make for a pretty sizable
> query string.
>
> Is there another method which is better and effecient?
>
> Thanks in Advance!

How do you know what digits you want to get? You must have some way of
identifying the set of widgets that you need to extract to find the
values to put in the where clause ...

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.

Thanks,


stephen O'D wrote:
> grohrer@gmail.com wrote:
> > I have a table which contains information regarding widgets. Each
> > widget has a Department Number and a Product Number.
> >
> > I need to query the table for a specific set of widgets...about 100 in
> > all.
> >
> > I'm familiar with the basic queries such as:
> >
> > SELECT *
> > FROM projectTable
> > WHERE prodNum IN ('002312','023124','254124')
> >
> > which works great if your only querying with a single specific field.
> >
> > The only other method I know if is to do the following:
> >
> > SELECT *
> > FROM projectTable
> > WHERE (deptNum = '01' AND prodNum = '002312') OR (deptNum = '02' AND
> > prodNum = '002314') OR .......
> >
> > Using this method for a hundred widgets would make for a pretty sizable
> > query string.
> >
> > Is there another method which is better and effecient?
> >
> > Thanks in Advance!
>
> How do you know what digits you want to get? You must have some way of
> identifying the set of widgets that you need to extract to find the
> values to put in the where clause ...

Create a table with the list of widgets that you want to query, and then
use this table in a join with the target table to filter the rows you want.

grohrer@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

grohrer@gmail.com wrote:
> I have a table which contains information regarding widgets. Each
> widget has a Department Number and a Product Number.
>
> I need to query the table for a specific set of widgets...about 100 in
> all.
>
> I'm familiar with the basic queries such as:
>
> SELECT *
> FROM projectTable
> WHERE prodNum IN ('002312','023124','254124')
>
> which works great if your only querying with a single specific field.
>
> The only other method I know if is to do the following:
>
> SELECT *
> FROM projectTable
> WHERE (deptNum = '01' AND prodNum = '002312') OR (deptNum = '02' AND
> prodNum = '002314') OR .......
>
> Using this method for a hundred widgets would make for a pretty sizable
> query string.
>
> Is there another method which is better and effecient?
>
> Thanks in Advance!
>

Yes:
1) Don't call identifiers numbers
2) create a department table
3) create a product number
4) rewrite the query as a joins over three tables

Any IN list with over x elements means a poorly analyzed
application: a reference table is missing.
Define x to your liking, but in my opinion, x should be
less than 10.
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

"Malcolm Dew-Jones" <yf110@vtn1.victoria.tc.ca> wrote in message
news:43a251cb$1@news.victoria.tc.ca...
> grohrer@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