Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Posts
    31

    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.

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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'
       )
    ;

  3. #3
    Join Date
    Dec 2009
    Posts
    31
    Thanks for the prompt response tonkuma but It returned zero records when I ran it using the code you gave me

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 09-30-11 at 00:48.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 09-30-11 at 00:49.

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)
    ;

  8. #8
    Join Date
    Dec 2009
    Posts
    31
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •