Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: Group by a variable created in a case statement?

    This code below does not work... any suggestions?
    TIA!

    SELECT B.FORM_RPT_ID AS FORMID
    ,B.FORM_RPT_LDSC_TX AS FORMDESC
    ,CASE (CURRENT DATE - DATE(A.FMRPT_CRETSTEFCVTS))
    WHEN 00 - 10 THEN '00 - 10'
    WHEN 10 - 20 THEN '10 - 20'
    WHEN 20 - 30 THEN '20 - 30'
    WHEN 30 - 40 THEN '30 - 40'
    WHEN 40 - 50 THEN '40 - 50'
    WHEN 50 - 60 THEN '50 - 60'
    ELSE '60+ '
    END DAYSPEND
    ,COUNT(*) AS COUNT

    FROM &DATABASE..H_PRSN_FORM_T A
    ,&DATABASE..H_FORM_RPT_T B

    WHERE A.FORM_RPT_CRET_STCD IN ('RPRT','RCRT')
    AND A.FORM_RPT_ID = B.FORM_RPT_ID

    GROUP BY B.FORM_RPT_ID,B.FORM_RPT_LDSC_TX,DAYSPEND

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Group by a variable created in a case statement?

    Do you have an error message at all ? Also, do you mind specifying your db2 version and your OS ?

    Please space a few minutes to read 'Must Read Before Posting' thread in this forum ...

    Cheers
    Sathyaram

    [QUOTE][SIZE=1]Originally posted by Yoshi
    This code below does not work... any suggestions?
    TIA!
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    I have never seen something like:
    WHEN 00 - 10 THEN '00 - 10'

    I think the correct sintax is something like:
    WHEN column > 00 then ...
    WHEN column > 10 then ...

    I think that you have a sintax error in CASE statement. Check the sintax for CASE statement in db2 documentation.

    Please give more info about your db2 version and operating system. Also the error message would help. Please also read the Must Read Before Posting on how to write the efficient questions.

    Hope this helps,
    Grofaty

  4. #4
    Join Date
    Sep 2003
    Posts
    2

    More info on problem

    I think the DB2 Version is 3.0 and the OS is IBM 5.0

    The syntax in the case statement is correct. I have used it before. It specifies a range. Its just that using the count(*) forces me to use a group by, and that causes a problem.

    The error message I am getting.
    QUERY MESSAGES:
    Column DAYSPEND is not in any table named in the query.

  5. #5
    Join Date
    Nov 2002
    Location
    vienna
    Posts
    9
    this is not a range, this is a substraction

    P:\bin\db2\bnd>db2 select case -9 when 1 - 10 then '1-10' else 'gtr10' end as x from sysibm.sysdummy1

    X
    -----
    1-10

    1 record(s) selected.


    P:\bin\db2\bnd>db2 select case 1 when 1 - 10 then '1-10' else 'gtr10' end as x from sysibm.sysdummy1

    X
    -----
    gtr10

    1 record(s) selected.

Posting Permissions

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