Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    55

    Unanswered: Convert Date to Varchar

    I am trying to select the row with the largest enterdat for each entityid. See the sample data below. I only want one row from each entityid. It doesn't seem to like the 'cast'. If anyone can think of a totally different query that would be great too!

    SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1 OF + IS INVALID



    SELECT *
    FROM TB914 A
    where (A.GIFTCLUB BETWEEN 'l2' AND 'l9'
    OR A.GIFTCLUB BETWEEN 'G6' AND 'G9')
    AND A.ENTITYID + Cast(VARCHAR(10),A.ENTERDAT)
    in (SELECT X.ENTITYID + Cast(VARCHAR(10),MAX(X.ENTERDAT))
    FROM TB914 X
    WHERE X.GIFTCLUB between 'l2' and 'l9'
    or (X.GIFTCLUB between 'g6' and 'g9')
    GROUP BY X.ENTITYID)
    ORDER BY A.ENTITYID


    Sample Data:

    entityid giftclub enterdat
    0000000023 L3 2003-08-29 00:00:00.000
    0000000023 L6 2006-04-08 00:00:00.000
    0000000023 L8 2008-04-08 00:00:00.000
    0000000045 G6 2006-01-18 00:00:00.000
    0000000045 G7 2007-02-10 00:00:00.000

    Thank you so much!!
    Last edited by KevinYC; 02-05-09 at 16:20.

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I think you should be able to do max on date and group it by id. Here is an example:

    db2 "select * from test"

    C1 C2 C3
    ----------- ----------- ----------
    11 1 12/12/2008
    11 1 12/13/2008
    12 2 12/22/2008
    12 2 12/23/2008


    db2 "select c1, max(c3) from test group by c1"

    C1 2
    ----------- ----------
    11 12/13/2008
    12 12/23/2008
    Last edited by db2girl; 02-05-09 at 16:27.

  3. #3
    Join Date
    Aug 2008
    Posts
    55
    Thank you, db2girl.
    But I would like to include all 3 columns in my result. Max(c3) wouldn't work if I included C2 in my select statement.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    can you group by c1 and c2?

    db2 "select c1, c2, max(c3) from test group by c1,c2"

    C1 C2 3
    ----------- ----------- ----------
    11 1 12/13/2008
    12 2 12/23/2008

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I am not sure what this is supposed to do:
    Code:
    A.ENTITYID + Cast(VARCHAR(10),A.ENTERDAT)
    I have never seen such a "Cast" syntax. If it means that A.ENTERDAT should be cast to VARCHAR(10), the syntax should be:
    Code:
    CAST(a.enterdat AS VARCHAR(10))
    Aside from that, you try to add a string to something else. I guess you may want to use the CONCAT or '||' operators because those are the standard SQL constructs for string concatenation.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Aug 2004
    Posts
    24
    try these functions.
    day(c3) month(c3) year(c3) dayofyear(c3) etc.
    I am a java and database developer.

Posting Permissions

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