Results 1 to 5 of 5

Thread: DB2 SQL help

  1. #1
    Join Date
    Mar 2012
    Posts
    2

    Unanswered: DB2 SQL help

    I require a "snazzy" piece of SQl to help display data in a nicer format.

    I have data held on 2 DB2 tables that are related (a parent table and a child table) in the form of:

    A-Table
    Code (k)
    Start Date (k)
    End Date (k)
    Description

    B-Table
    Code (k)
    Type (k)
    Start Date (k)
    End Date (k)
    Consultant

    If I run a straight forward piece of SQL joining the two tables I get the output in the following format:

    Code Description Start Date End Date Type Consultant
    A Firm A 01.01.2012 31.12.9999 AT RET 000001
    A Firm A 01.01.2012 31.12.9999 BPA 000002
    A Firm A 01.01.2012 31.12.9999 CORP 000001
    B Firm B 01.01.2012 31.12.9999 AT RET 000003
    B Firm B 01.01.2012 31.12.9999 BPA 000002
    B Firm B 01.01.2012 31.12.9999 CORP 000004

    As there are always the same number of child rows per parent row (in the exampel above 4) and always the same "Types" (again in the above example 3) is there any way in an SQL query I could get the data displayed as below?

    Code Description Start Date End Date AT RET BPA CORP
    A Firm A 01.01.2012 31.12.9999 000001 000002 000001
    B Firm B 01.01.2012 31.12.9999 000003 000002 000004

    *Apologies for the display - I can't seem to get this editor to display this as I want so have attached them in a txt document

    I've tried to do this using UNIONS and CASE commands but always come up with multiple rows per "Code"...

    I imagine I'll need to use some sort of "SUB-SELECT" functionality but from what I can find I can't seem to find a way to make it work

    I could potentially write some SELCOPY or COBOL/DB2 programme to do this but that would mean going through my companies change process to do this which would be time consuming when I have SQL read access.

    Any idea on how I could do this woudl be very much appreciated

    Any questions, just let me know

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    select ...
    from a
    join b b1 on a.code = b1.code and b1.type = 'type1'
    join b b2 on a.code = b2.code and b2.type = 'type2'
    join b b3 ...

  3. #3
    Join Date
    Mar 2012
    Posts
    2
    Quote Originally Posted by n_i View Post
    select ...
    from a
    join b b1 on a.code = b1.code and b1.type = 'type1'
    join b b2 on a.code = b2.code and b2.type = 'type2'
    join b b3 ...
    doh!!!

    I think I was trying to complicate it too much...

    That's what happens when you don't don't doing anything in SQL for about 4 years...

    Many thanks - you were a great help

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If I run a straight forward piece of SQL joining the two tables I get the output in the following format:

    Code Description Start Date End Date Type Consultant
    A Firm A 01.01.2012 31.12.9999 AT RET 000001
    A Firm A 01.01.2012 31.12.9999 BPA 000002
    A Firm A 01.01.2012 31.12.9999 CORP 000001
    B Firm B 01.01.2012 31.12.9999 AT RET 000003
    B Firm B 01.01.2012 31.12.9999 BPA 000002
    B Firm B 01.01.2012 31.12.9999 CORP 000004
    Looking in your joined output,
    all Start Date for Code = 'A' are same and all End Date for Code = 'A' are same and
    all Start Date for Code = 'B' are same and all End Date for Code = 'B' are same.

    If this was applied for all real data,
    (1) Code (k) might be enough for Primary key in A-Table. Start Date and End Date might be not neccesary in Primary Key.
    (2) Start Date and End Date in B-Table might be not necessary.
    Last edited by tonkuma; 03-14-12 at 09:14. Reason: Add QUOTE

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Other ideas.

    Example 1:
    Code:
    SELECT a.Code
         , MAX(a.Description) AS Description
         , MAX(a.Start_Date)  AS Start_Date
         , MAX(a.End_Date)    AS End_Date
         , MAX( CASE Type WHEN 'AT RET' THEN Consultant END ) AS AT_RET
         , MAX( CASE Type WHEN 'BPA'    THEN Consultant END ) AS BPA
         , MAX( CASE Type WHEN 'CORP'   THEN Consultant END ) AS CORP
     FROM  A_Table AS a
     INNER JOIN
           B_Table AS b
      ON   b.Code       = a.Code
       AND b.Start_Date = a.Start_Date
       AND,b.End_Date   = a.End_Date
     GROUP BY
           a.Code
    ;
    Example 2:
    Code:
    SELECT a.Code
         , a.Description
         , a.Start_Date
         , a.End_Date
         , b.AT_RET
         , b.BPA
         , b.CORP
     FROM  A_Table AS a
     INNER JOIN
          (SELECT Code
                , MAX( CASE Type WHEN 'AT RET' THEN Consultant END ) AS AT_RET
                , MAX( CASE Type WHEN 'BPA'    THEN Consultant END ) AS BPA
                , MAX( CASE Type WHEN 'CORP'   THEN Consultant END ) AS CORP
            FROM  B_Table
            GROUP BY
                  Code
          ) AS b
      ON   b.Code = a.Code
    ;

Tags for this Thread

Posting Permissions

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