Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2011
    Posts
    3

    Unanswered: select statement

    Hi,
    I have a table t1 having data in below foramt

    code desc rate date
    ***** ***** ***** *****
    abc abcd 1.2 2004-12-23
    ax adbcf .12 2004-12-23
    abc abcd 1.19 2004-12-24
    abc abcd 1.21 2004-12-25
    ax adbcf .11 2004-12-25

    I need the output from this table like below
    Output
    *******
    code desc 2004-12-23 2004-12-24 2004-12-25
    **** ***** ********** ********** **********
    abc abcd 1.2 1.19 1.21
    ax adbcf .12 .11

    that means I need rates of each code for all date in a single row and column header to display the rates should be date.
    Please help me in this regard.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    See PIVOT in "SQL on Fire! Part 1"

    http://sirdug.org/downloads/SQLonFire_1_SirDUG.pdf

  3. #3
    Join Date
    Feb 2011
    Posts
    3

    need help to build select statement

    Hi Tonkuma,
    I used one of your example like below
    WITH
    Add_rn1 AS (
    SELECT date
    , ROWNUMBER() OVER(ORDER BY date) rn1 from
    (SELECT DISTINCT R.RT_DT FROM CRNCY_RT R, CRNCY C WHERE R.CRNCY_CD=C.CRNCY_CD AND R.RTDT between '2004-1-1' AND '2004-1-2' AND R.ID=0 AND R.STRTDT<='2004-1-2' AND R.ENDDT>'2004-1-2' AND R.STSCD='O') Q(date))
    ,
    Add_rn2 AS (
    SELECT R.CRNCY_CD, C.DESC, R.CRNCY_CD1, R.RT, rn1
    , ROWNUMBER() OVER(PARTITION BY R.RT_DT ORDER BY R.CRNCY_CD) rn2
    FROM CRNCY_RT R, CRNCY C
    , Add_rn1 n
    WHERE R.CRNCY_CD=C.CRNCY_CD AND R.RTDT = n.date
    AND C.PID=0 AND C.STRTDT<=n.date AND C.ENDDT>n.date AND C.STSCD='O' )
    ,
    Recurse (ln, rn1, CRNCY_CD, DESC, CRNCY_CD1, RT) AS (
    SELECT 1 ln
    , rn1,CRNCY_CD,DESC,CRNCY_CD1,RT
    FROM Add_rn2
    WHERE rn2 = 1
    UNION ALL
    SELECT pre.ln + 1
    , pre.rn1
    , new.CRNCY_CD
    ,new.DESC
    ,new.CRNCY_CD1
    , new.RT
    FROM Recurse pre
    , Add_rn2 new
    WHERE pre.ln < 10000
    AND pre.rn1 = new.rn1
    AND new.rn2 = pre.ln + 1
    )

    SELECT col2 AS " ", col3 AS " "
    FROM (
    SELECT -1 ln
    , MAX(CASE rn1 WHEN 1 THEN CHAR(date,USA) END)
    , MAX(CASE rn1 WHEN 2 THEN CHAR(date,USA) END)
    FROM Add_rn1
    UNION ALL
    SELECT ln,CRNCY_CD,DESC,CRNCY_CD1
    , MAX(CASE rn1 WHEN 1 THEN char(RT) END)
    , MAX(CASE rn1 WHEN 2 THEN char(RT) END)
    FROM Recurse
    GROUP BY ln

    ) Q (ln, col2, col3)
    ORDER BY ln;

    From Recurse I am selecting 3 more columns CRNCY_CD,DESC,CRNCY_CD1. We are using UNION ALL at the end to get the final result. But I am not able to find out how to add these 3 columns in the 1st table (Add_rn1) of UNION part. Please help me regarding this.

    Thanks a lot.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ..., I am not able to find out how to add these 3 columns in the 1st table (Add_rn1) of UNION part. ...
    You may want to add strings of column names, like...
    'CRNCY_CD' AS CRNCY_CD, 'DESC' AS DESC, 'CRNCY_CD1' AS CRNCY_CD1

    Anyway, I thought that you don't need to use such complex query.
    How about like this?
    Code:
    WITH
     add_rank AS (
    SELECT R.CRNCY_CD
         , C.DESC
         , R.CRNCY_CD1
         , R.RT
         , R.RT_DT
         , DENSE_RANK() OVER(ORDER BY RT_DT)    AS x_axis
         , DENSE_RANK() OVER(ORDER BY CRNCY_CD) AS y_axis
      FROM CRNCY_RT R
         , CRNCY    C 
     WHERE R.CRNCY_CD = C.CRNCY_CD
       AND ...
       AND ...
    )
    SELECT CRNCY_CD
         , MAX(DESC)      AS DESC
         , MAX(CRNCY_CD1) AS CRNCY_CD1
         , MAX(CASE x_axis WHEN 1 THEN data END)
         , MAX(CASE x_axis WHEN 2 THEN data END)
      FROM (SELECT DISTINCT
                 , -1               AS y_axis
                 , 'CRNCY_CD'       AS CRNCY_CD
                 , 'DESC'           AS DESC
                 , 'CRNCY_CD1'      AS CRNCY_CD1
                 , x_axis
                 , CHAR(RT_DT, USA) AS data
              FROM add_rank
            UNION ALL
            SELECT y_axis
                 , CRNCY_CD
                 , DESC
                 , CRNCY_CD1
                 , x_axis
                 , CHAR(RT)         AS data
              FROM add_rank
           )
     GROUP BY
           y_axis
         , CRNCY_CD
     ORDER BY
           y_axis
    ;

  5. #5
    Join Date
    Feb 2011
    Posts
    3
    Thanks a lot
    I tried to execute the query, but I am getting below error

    42601(-104)[IBM][CLI Driver][DB2] SQL0104N An unexpected token "<EMPTY>" was found following "<EMPTY>". Expected tokens may include: "CORRELATION NAME". SQLSTATE=42601

    Please help in this regard.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are you using older DB2 version?

    Nested table expression without CORRELATION NAME was supported from DB2 9.5 for LUW.

    If you are using DB2 9.1 for LUW or earlier,
    try
    Code:
    ...
                 , CHAR(RT)         AS data
              FROM add_rank
           ) AS q
     GROUP BY
           y_axis
         , CRNCY_CD
    ...

Posting Permissions

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