If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: help with a complex query

Hi ALL ,

I need help with a query that gets the same of result from 3 different tables but joins to a common table for a set of conditions

My issue is

First even though I say distinct it gives me duplicate result
Second How do I join this query in a single result. I trieda "union all" but it takes forever to return the results because these are pretty big tables
Thirdly the clause
AND S.ISSUEDBILL ='S'
OR S.ISSUEDBILL ='Q';
returns only the values for Q and using 'AND' is returning 0 rows

Is there a way I can join the three queries in an efficient and cost effective way into a single query and get the result set from one query

Note that all three tables store different data so I need to go to all three tables in order to get complete data. I have been working on this past two days trying to figure out now I am out . I ran the explain plan on the query with union all it is using all the right indexes but running for past 2 hours.

One thing I see that is not a good idea is mixing you join predicates between the ON clause and the WHERE clause. Put them all in the ON clause. Otherwise you can get some strange, hard to explain results.

PS if IDREN, DATEOFCHANGE, and CODE_STATUS are really hard-coded, you could just hard-code them in the SELECT. This may help with the response time depending on whether you can get Index only access with out having to return them from the table.

Another thing you can try is to use a nested table to filter the results before the Join:

How well these work depends on the number of rows, the expected result set for each table access, and Indexes.

Try working with one table (POLICY_STATUS, POLICY_RENEWAL, OR POLICY_CANCELLED) at a time with POLICY_ACTIVE until you get acceptable response time. Then either UNION them together or use a Common Table Expression to so each one separately and then join them.

Code:

WITH ACTIVE
AS ( select from ACTIVE)
, STATUS
AS ( STATUS joined with ACTIVE)
, RENEWAL
AS (RENEWAL joined with ACTIVE)
, CANCELLED
AS (CANCELLED joined with ACTIVE)
SELECT * FROM STATUS UNION
SELECT * FROM RENEWAL UNION
SELECT * FROM CANCELLED

Thank you so mucb for your response guys.
Tonkuma you were right the last one data didnot have 'S' as the Issuedbill so I went back to '2011-04-28' where I got 200 results (with fetch first 200 rows only). So I am trying the various methods that have been suggested on this post. I already tried this code by Stealth_DBA