| unions and intersection [message #372461] |
Wed, 14 February 2001 06:28  |
reeta
Messages: 4
Registered: February 2001
|
Junior Member |
|
|
hi!
i have four parameters based on which i need to search(And /Or) the database.
<parameter1>
<parameter2>
<parameter3>
<parameter4>
<parameter5>
Search And /Or
i have to display uniform information for which ever
parameter i select.
i am planning to use union and intersect. for the search mode and for eaxh parameter i have separte query.and with appl program i want to check for
the parameters selected and search used.
My question
a.will these effect in future when data is increasing
b. is there any restriction for rows fetched(255).
waiting for reply
reeta
|
|
|
|
| Re: unions and intersection [message #372478 is a reply to message #372461] |
Wed, 14 February 2001 12:28  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
I don't fully understand the question, but from what I gather you will have 4 query conditions which could be given in any combination. This gives you 16 possible different queries? For querying, the 16 different queries would be most efficient, but the coding and testing would just take forever.
The following extract shows how different queries can be executed depending on what query parms are passed in:
IF v_stoprec_flag = 1 -- Display Stopped records!
THEN
OPEN v_mytab_cv FOR
SELECT ...
FROM ...
WHERE 'X' || col1 LIKE 'X' || v_query_parm_1 || '%' -- Trick to overcome
AND 'X' || col2 LIKE 'X' || v_query_parm_2 || '%' -- the nullability
AND 'X' || col3 LIKE 'X' || v_query_parm_3 || '%' -- of these columns!
ORDER BY ...;
ELSE -- Not Stopped Records!
OPEN v_mytab_cv FOR
SELECT ...
FROM ...
WHERE end_dt >= TRUNC (SYSDATE) -- Not Stopped records!
AND 'X' || col1 LIKE 'X' || v_query_parm_1 || '%' -- Trick to overcome
AND 'X' || col2 LIKE 'X' || v_query_parm_2 || '%' -- the nullability
AND 'X' || col3 LIKE 'X' || v_query_parm_3 || '%' -- of these columns!
ORDER BY ...;
END IF;
In this example "LIKE" is used so shat all query parms can be put into each query. This is not identical to having "=".
Regarding the Intersection and Union, they will work just fine and you shouldn't have performance problems. Beware that UNION and UNION ALL are different (UNION eliminates duplicates in the result set). INTERSECT is basically the same as "in (subquery)"
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
