help with a complex query

dbsam · #1 (**permalink**) 09-29-11, 21:31

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.

Below are the qeuries

SELECT distinct S.ID,
S.REN,
S.IDEND,
S.ISSUEDBILL,
S.DATEOFCHANGE,
A.STATUS,
A.CODE_STATUS
FROM table POLICY_STATUS S
INNER JOIN
table POLICY_ACTIVE A
ON S.ID=A.ID
WHERE S.IDEND=A.IDEND
AND S.IDREN=A.IDREN
AND S.IDREN = '00'
AND S.DATEOFCHANGE = '2011-08-28'
AND A.CODE_STATUS = 'NEW'
AND A.ACTIVE_STATUS = 1
AND S.ISSUEDBILL ='S'
OR S.ISSUEDBILL ='Q';

SELECT distinct R.ID,
R.REN,
R.IDEND,
R.ISSUEDBILL,
R.DATEOFCHANGE,
A.STATUS,
A.CODE_STATUS
FROM table POLICY_RENEWAL R
INNER JOIN
table POLICY_ACTIVE A
ON R.ID=A.ID
WHERE R.IDEND=A.IDEND
AND R.IDREN=A.IDREN
AND R.IDREN = '00'
AND R.DATEOFCHANGE = '2011-08-28'
AND A.CODE_STATUS = 'NEW'
AND A.ACTIVE_STATUS = 1
AND R.ISSUEDBILL ='S'
OR R.ISSUEDBILL ='Q';

SELECT distinct C.ID,
C.REN,
C.IDEND,
C.ISSUEDBILL,
C.DATEOFCHANGE,
A.STATUS,
A.CODE_STATUS
FROM table POLICY_CANCELLED C
INNER JOIN
table POLICY_ACTIVE A
ON C.ID=A.ID
WHERE C.IDEND=A.IDEND
AND C.IDREN=A.IDREN
AND C.IDREN = '00'
AND C.DATEOFCHANGE = '2011-08-28'
AND A.CODE_STATUS = 'NEW'
AND A.ACTIVE_STATUS = 1
AND C.ISSUEDBILL ='S'
OR C.ISSUEDBILL ='Q';

Please help

Thanks

dbsam

tonkuma · #2 (**permalink**) 09-29-11, 22:29

Quote:

Thirdly the clause
AND S.ISSUEDBILL ='S'
OR S.ISSUEDBILL ='Q';
returns only the values for Q and using 'AND' is returning 0 rows

AND is priority over OR.

So, your WHERE conditions are quivalent to

Code:

 WHERE
  (    S.IDEND         = A.IDEND
   AND S.IDREN         = A.IDREN
   AND S.IDREN         = '00'
   AND S.DATEOFCHANGE  = '2011-08-28'
   AND A.CODE_STATUS   = 'NEW'
   AND A.ACTIVE_STATUS = 1
   AND S.ISSUEDBILL    = 'S'
  )
  OR   S.ISSUEDBILL    = 'Q'
;

so on...

You might want to specify

Code:

 WHERE
       S.IDEND         = A.IDEND
   AND S.IDREN         = A.IDREN
   AND S.IDREN         = '00'
   AND S.DATEOFCHANGE  = '2011-08-28'
   AND A.CODE_STATUS   = 'NEW'
   AND A.ACTIVE_STATUS = 1
   AND
   (   S.ISSUEDBILL    = 'S'
    OR S.ISSUEDBILL    = 'Q'
   )
;

dbsam · #3 (**permalink**) 09-29-11, 22:59

Thanks for the prompt response tonkuma but It returned zero records when I ran it using the code you gave me

tonkuma · #4 (**permalink**) 09-29-11, 23:40

So, there must be no such combination of data.

Why did you think there are some results matching the conditions.

If possible, please show sample data and expected results from the sample data.

tonkuma · #5 (**permalink**) 09-29-11, 23:45

This is only my guess.

I wonder if you want to get the data satisfying a condition
S.DATEOFCHANGE >= '2011-08-28' /* changed within last 1 month */
and other conditions you already coded.

Stealth_DBA · #6 (**permalink**) 09-29-11, 23:51

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.

Code:

FROM POLICY_STATUS S
       INNER JOIN
     POLICY_ACTIVE A
       ON     S.ID    = A.ID
          AND S.IDEND = A.IDEND
          AND S.IDREN = A.IDREN
WHERE S.IDREN = '00'
  AND S.DATEOFCHANGE  = '2011-08-28'
  AND A.CODE_STATUS   = 'NEW'
  AND A.ACTIVE_STATUS = 1
  AND S.ISSUEDBILL IN('S','Q')
;

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:

Code:

FROM (SELECT ID
           , IDEND
           , IDREN
           , REN
           , ISSUEDBILL
           , DATEOFCHANGE
      FROM POLICY_STATUS
      WHERE ID.REN       = '00'
        AND DATEOFCHANGE = '2011-08-28'
        AND ISSUEDBILL IN('S','Q')
     ) AS S
         INNER JOIN
     (SELECT ID
           , IDEND
           , IDREN
           , CODE_STATUS
           , ACTIVE_STATUS
           , STATUS
      FROM POLICY_ACTIVE
      WHERE IDREN         = '00'
        AND CODE_STATUS   = 'NEW'
        AND ACTIVE_STATUS = 1
     ) AS A
         ON    S.ID    = A.ID
           AND S.IDEND = A.IDEND
           AND S.IDREN = A.IDREN

You could also try a Common Table Expression and only process the POLICY_ACTIVE table once:

Code:

WITH POL_ACTV
  AS (
      SELECT ID
           , IDEND
           , IDREN
           , CODE_STATUS
           , ACTIVE_STATUS
           , STATUS
      FROM POLICY_ACTIVE
      WHERE IDREN         = '00'
        AND CODE_STATUS   = 'NEW'
        AND ACTIVE_STATUS = 1
     )
SELECT S.ID
     , S.REN
     , S.IDEND
     , S.ISSUEDBILL
     , S.DATEOFCHANGE
     , A.STATUS
     , A.CODE_STATUS
FROM (SELECT ID
           , IDEND
           , IDREN
           , REN
           , ISSUEDBILL
           , DATEOFCHANGE
      FROM POLICY_STATUS
      WHERE ID.REN       = '00'
        AND DATEOFCHANGE = '2011-08-28'
        AND ISSUEDBILL IN('S','Q')
     ) AS S
         INNER JOIN
     POL_ACTV AS A
         ON    S.ID    = A.ID
           AND S.IDEND = A.IDEND
           AND S.IDREN = A.IDREN

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

tonkuma · #7 (**permalink**) 09-30-11, 01:21

First ideas to come three queries which came in my mind were UNION ALL or FULL OUTER JOIN.

(1) Using UNION was already supplied by Stealth_DBA.
I want to add two desirable things.
Note 1-1: Add a column to show from which query the row was column, like

Code:

SELECT 'S' AS tbl_nm
     , S.ID
     , S.REN
...

Note 1-2: Add "ORDER BY id" clause.

(2) The idea using FULL OUTER JOIN was like...

Code:

SELECT
       COALESCE(S.ID , R.ID , C.ID) AS id
     , SUBSTR(
             CASE WHEN S.ID IS NOT NULL THEN ', status'    ELSE '' END
          || CASE WHEN S.ID IS NOT NULL THEN ', renewal'   ELSE '' END
          || CASE WHEN S.ID IS NOT NULL THEN ', cancelled' ELSE '' END
        , 3
       )            AS exist
     , S.REN        AS s_ren
     , R.REN        AS r_ren
     , C.REN        AS ced_ren
     , COALESCE(S.IDEND , R.IDEND , C.IDEND) AS idend
     , S.ISSUEDBILL AS s_issuedbill
     , R.ISSUEDBILL AS r_issuedbill
     , C.ISSUEDBILL AS c_issuedbill
     , COALESCE(
          S.DATEOFCHANGE
        , R.DATEOFCHANGE
        , C.DATEOFCHANGE
       )            AS DATEOFCHANGE
     , COALESCE(
          S.STATUS
        , R.STATUS
        , C.STATUS
       )            AS status
     , COALESCE(
          S.CODE_STATUS
        , R.CODE_STATUS
        , C.CODE_STATUS
       )            AS code_status
 FROM  
       (/* your first query */

       ) s
 FULL  OUTER JOIN
       (/* your second query */

       ) r
   ON  r.id = s.id
 FULL  OUTER JOIN
       (/* your third query */

       ) c
   ON  c.id = COALESCE(r.id , s.id)
;

dbsam · #8 (**permalink**) 09-30-11, 09:58

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

WITH POL_ACTV
AS (
SELECT ID
, IDEND
, IDREN
, CODE_STATUS
, ACTIVE_STATUS
, STATUS
FROM POLICY_ACTIVE
WHERE IDREN = '00'
AND CODE_STATUS = 'NEW'
AND ACTIVE_STATUS = 1
)
SELECT S.ID
, S.REN
, S.IDEND
, S.ISSUEDBILL
, S.DATEOFCHANGE
, A.STATUS
, A.CODE_STATUS
FROM (SELECT ID
, IDEND
, IDREN
, REN
, ISSUEDBILL
, DATEOFCHANGE
FROM POLICY_STATUS
WHERE ID.REN = '00'
AND DATEOFCHANGE = '2011-08-28'
AND ISSUEDBILL IN('S','Q')
) AS S
INNER JOIN
POL_ACTV AS A
ON S.ID = A.ID
AND S.IDEND = A.IDEND
AND S.IDREN = A.IDREN

and it retruned zero results. But probably I am going wrong somewhere still working on it

Thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech