Re: Building queries from table data

In <DLHcJc3w165w_at_sytex.com>, sullivan_at_sytex.com (mike sullivan) writes:
>...
>Objective: Create a query from each record in table A that then is used
>to search for matches in B.
>
>Example: Table A = field 1='A' field 2='B' field 3='C' field 4='D'
> MAX = 100 MIN = 20
>
>From the record above in table A I'd like to create the query for B as:
>
>Select * from TABLE B where field1='A', field2='B', field3='C',
> field4='D' and VALUE BETWEEN (MIN,MAX)
>
>I've thought of a brute force way by opening a cursor, looking at each
>field in A to see if it's null (if NULL, ignore it), and then building a
>dynamic query in FORMs, then execute the query. I'd like this to run
>unattended so I'm really looking to do this w/ PL/SQL in SQL*plus
>following the same process. I've just not seen "dynamic where" building
>in SQL*Plus.

It seems to be very simple, use a normal join:

select B.*
from A, B

where   A.field1 = B.field1
and     A.field2 = B.field2
and     A.field3 = B.field3
and     A.field4 = B.field4
and     B.VALUE between A.MIN and A.MAX

As a column containing NULL is considered not to be equal to anything (the value is UNKNOWN), this query should give you exactly what you want.

Remember, MIN, MAX and VALUE are keywords in ORACLE, so your columns should be named different.

Hope this helps.

Georg

 ___   ___
| + | |__ ' Georg Rehfeld,  D-20535 Hamburg, Jordanstr. 8,  [49] (40) 2518356
|_|_\ |___, georg_at_deerwood.hanse.de