Results 1 to 10 of 10

Thread: New to DB2 SQL

  1. #1
    Join Date
    Jul 2012
    Posts
    4

    Unanswered: New to DB2 SQL

    Hi,

    I am currently trying to create a query with the following:

    table 1 (att1, att2, att3, att4, att.others)
    table 2 (att1, att2, att3, att4, att.5, att.others)
    ==> att1-4 in table 1 and 2 are the same columns

    query 1 is a subset of table 2 with certain conditions.


    Now, I want the result to be table 1 x table 2 where it matches att1-4 in query1

    I hope someone out there can give me an idea on how to tackle this.
    I am thinking of implementing subqueries but not sure how exactly the nesting will be.

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Code:
    select blah
    from table1 as t1
    inner join table2 as t2 on (t1.attr4 = t2.attr4)
    where t1.attr4 in (select attr4 [rest of query 1])
    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by DB_noobie_DC View Post
    query 1 is a subset of table 2 with certain conditions.

    Now, I want the result to be table 1 x table 2 where it matches att1-4 in query1

    I hope someone out there can give me an idea on how to tackle this.
    Apply "certain conditions" to "table 1 x table 2".

    P.S. You are on the short list for the worst problem statement for the month of July.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jul 2012
    Posts
    4

    More details...

    ARWinner(ANDY)

    Thank you for your reply.

    Im trying your suggestion and have a few questions

    T1(att1, att2, att3, att4)
    T2(att1, att2, att3, att4, att5, att6)
    T3(att5, attx)

    query:
    SELECT T2.*,T3.3*
    FROm T2, T3
    WHERE T2.att5=T3.att5
    AND T3.att5 = value1


    I need the result to be:
    all T1 join T2 on (att1, att2, att3, att4) where
    T1joinT2.att1 = Query1.att1
    T1joinT2.att2 = Query1.att2
    T1joinT2.att3 = Query1.att3
    T1joinT2.att4 = Query1.att4

    The limitation is that I can only do select. I know I can do this if i create a view table for the query, but unfortunately I do not have the authorization for DDL type of command to create a view table.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This is all basic SQL stuff. I suggest you read the manuals and some SQl guides such at this:

    DB2 SQL Cookbook


    Andy

  6. #6
    Join Date
    Jul 2012
    Posts
    4

    Sorry about the limited info

    Quote Originally Posted by n_i View Post
    Apply "certain conditions" to "table 1 x table 2".

    P.S. You are on the short list for the worst problem statement for the month of July.

    Here are more details on the problem:

    T1(att1, att2, att3, att4)
    T2(att1, att2, att3, att4, att5, att6)
    T3(att5, attx)

    query:
    SELECT T2.*,T3.3*
    FROm T2, T3
    WHERE T2.att5=T3.att5
    AND T3.att5 = value1


    I need the result to be:
    all T1 join T2 on (att1, att2, att3, att4) where
    T1joinT2.att1 = Query1.att1
    T1joinT2.att2 = Query1.att2
    T1joinT2.att3 = Query1.att3
    T1joinT2.att4 = Query1.att4

    The limitation is that I can only do select. I know I can do this if i create a view table for the query, but unfortunately I do not have the authorization for DDL type of command to create a view table.

    Basically T1 is a header table with one or more line item record from T2.

    Now I need to list all header + line item records of all items in T1joinT2(att1,att2,att3,att4) where it matches query1(att1,att2,att3,att4).

    Again, as i mentioned to Andy on my previous reply, I know i can do this if i create a view table, however, I am not authorized to use the CREATE VIEW command hence, limited to SELECT onlyl.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You know you can join more than two tables at a time, right?
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jul 2012
    Posts
    4
    SELECT CLAIM.*,
    LINE2.*,
    brdg2.DIAG_CD_POS_NUM,
    disp2.DISP_CD,
    disp2.DISP_DESC,
    csts2.CLM_STS_CD,
    csts2.CLM_STS_DESC,
    PLAN1.pln_cd,
    PLAN1.pln_nm
    FROM PDW.CLM_F AS CLAIM,
    PDW.CLM_LN_F AS LINE2,
    PDW.PLAN_D AS plan1,
    PDW.DIAG_CD_GRP_BRDG AS BRDG2,
    PDW.DIAG_CD_D AS DIAG2,
    PDW.DISP_D AS DISP2,
    PDW.CLM_STS_D AS CSTS2,
    (SELECT DISTINCT LINE1.CLM_AFFTNG_TS,
    LINE1.CNTR_SK,
    LINE1.CLM_ID,
    LINE1.PAT_SK,
    LINE1.PLN_SK,
    LINE1.DISP_SK,
    LINE1.CLM_DISP_UID
    FROM PDW.CLM_LN_F AS LINE1,
    PDW.DIAG_CD_GRP_BRDG AS BRDG1,
    PDW.DIAG_TP_D AS TYPE1,
    PDW.PRESENT_ON_ADMS_D AS POA1,
    PDW.DIAG_CD_D AS DIAG1,
    PDW.DISP_D AS DISP1,
    PDW.CLM_STS_D AS CSTS1
    WHERE LINE1.DIAG_CD_GRP_SK = BRDG1.DIAG_CD_GRP_SK
    AND BRDG1.DIAG_TP_SK = TYPE1.DIAG_TP_SK
    AND BRDG1.PRESENT_ON_ADMS_SK = POA1.PRESENT_ON_ADMS_SK
    AND BRDG1.DIAG_CD_SK = DIAG1.DIAG_CD_SK
    AND LINE1.DISP_SK = DISP1.DISP_SK
    AND LINE1.CLM_STS_SK = CSTS1.CLM_STS_SK
    AND UPPER (DIAG1.DIAG_DESC) = UPPER ('INVALID')
    AND -- lines below indicate the logic for denied claims
    ( (DISP1.DISP_CD <> '9' -- Disposition code 9 = Denied Claims
    AND CSTS1.clm_sts_cd = '70') -- Claim Status code 70 = Approved
    OR CSTS1.clm_sts_cd = '55')
    AND timestamp (LINE1.CLM_AFFTNG_TS) >=
    timestamp ('2012-01-01 00:00:00')) AS LINE
    WHERE CLAIM.CLM_AFFTNG_TS = LINE2.CLM_AFFTNG_TS
    AND CLAIM.CNTR_SK = LINE2.CNTR_SK
    AND CLAIM.CLM_ID = LINE2.CLM_ID
    AND CLAIM.PAT_SK = LINE2.PAT_SK
    AND CLAIM.PLN_SK = LINE2.PLN_SK
    AND CLAIM.DISP_SK = LINE2.DISP_SK
    AND CLAIM.CLM_DISP_UID = LINE2.CLM_DISP_UID
    AND LINE2.DISP_SK = DISP2.DISP_SK
    AND LINE2.CLM_STS_SK = CSTS2.CLM_STS_SK
    AND LINE2.DIAG_CD_GRP_SK = BRDG2.DIAG_CD_GRP_SK
    AND BRDG2.DIAG_CD_SK = DIAG2.DIAG_CD_SK
    AND LINE2.PLN_SK = PLAN1.PLN_SK;

    Is there a way to optimize this SQL code given that query optimizer is not installed?

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 automatically optimizes all SQL. If you want to see what DB2 did to optimize it, you can run the EXPAIN SQL statement. See the SQL Reference Vol 2, for more information.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You didn't specify relationship(condition) of a subquery LINE and any other tables.
    So, if the subquery LINE returned a row, all rows of final SELECT list(mached with FROM and WHERE clause) would be returned.

    I guesses that you might want to get rows of LINE2 where values of columns of LINE2 matched with corresponding columns of LINE1 in the subquery LINE.

    With that assumpion, I reached the following query after some steps of modifications for your query.
    If this modified query was not returned your expected result,
    it would be better to publish test data for all tables and expected result from the test data.
    Code:
    SELECT CLAIM.*
         , LINE .*
         , brdg .DIAG_CD_POS_NUM
         , disp .DISP_CD
         , disp .DISP_DESC
         , csts .CLM_STS_CD
         , csts .CLM_STS_DESC
         , PLAN .pln_cd
         , PLAN .pln_nm
    
     FROM
           PDW.CLM_F            AS CLAIM
     INNER JOIN
           PDW.CLM_LN_F         AS LINE
      ON   LINE.CLM_AFFTNG_TS  = CLAIM.CLM_AFFTNG_TS
       AND LINE.CNTR_SK        = CLAIM.CNTR_SK
       AND LINE.CLM_ID         = CLAIM.CLM_ID
       AND LINE.PAT_SK         = CLAIM.PAT_SK
       AND LINE.PLN_SK         = CLAIM.PLN_SK
       AND LINE.DISP_SK        = CLAIM.DISP_SK
       AND LINE.CLM_DISP_UID   = CLAIM.CLM_DISP_UID
     INNER JOIN
           PDW.DIAG_CD_GRP_BRDG AS BRDG
      ON   BRDG.DIAG_CD_GRP_SK = LINE.DIAG_CD_GRP_SK
     INNER JOIN
           PDW.DISP_D           AS DISP
      ON   DISP.DISP_SK        = LINE.DISP_SK
     INNER JOIN
           PDW.CLM_STS_D        AS CSTS
      ON   CSTS.CLM_STS_SK     = LINE.CLM_STS_SK
       AND
      (    CSTS.clm_sts_cd =  '70'
       AND DISP.DISP_CD    <> '9'
       OR
           CSTS.clm_sts_cd =  '55'
      )
     INNER JOIN
           PDW.PLAN_D           AS plan
      ON   PLAN.PLN_SK         = LINE.PLN_SK
    
     WHERE
    /*
           timestamp(LINE.CLM_AFFTNG_TS) >= timestamp('2012-01-01 00:00:00')
    */
           DATE(LINE.CLM_AFFTNG_TS) >= DATE('2012-01-01')
       AND EXISTS
           (SELECT 0
             FROM  PDW.DIAG_CD_D        AS DIAG
             WHERE DIAG.DIAG_CD_SK       = BRDG.DIAG_CD_SK
               AND UPPER(DIAG.DIAG_DESC) = 'INVALID'
           )
       AND EXISTS
           (SELECT 0
             FROM  PDW.DIAG_TP_D         AS TYPE
                 , PDW.PRESENT_ON_ADMS_D AS POA
             WHERE TYPE.DIAG_TP_SK         = BRDG.DIAG_TP_SK
               AND POA .PRESENT_ON_ADMS_SK = BRDG.PRESENT_ON_ADMS_SK
          )
    ;
    Last edited by tonkuma; 07-25-12 at 17:20.

Posting Permissions

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