Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Exclamation Unanswered: Query Syntax Help

    I have a table (CONT_CHOICE_USAGE) with the following columns:
    ----------------
    PK ACCOUNT_NBR
    PK PREMISE
    PK CHOICE_YEAR
    USAGE_MONTH
    USAGE

    I need to create a view to output the data in a format similar to:
    ----------------
    ACCOUNT_NBR
    PREMISE
    CHOICE_YEAR
    JAN_USAGE
    FEB_USAGE
    MAR_USAGE
    ...
    DEC_USAGE

    I have been messing with CASE to create my columns if the data exists,
    Code:
    SELECT 
    cc.ACCOUNT_NBR,
    cc.PREMISE,
    cc.CHOICE_YEAR,
    CASE  WHEN (ccu.USAGE_MONTH = 'JAN') THEN ccu.usage
      ELSE NULL
      END PRELIM_JAN,
    CASE  WHEN (ccu.USAGE_MONTH = 'FEB') THEN ccu.usage
      ELSE NULL
      END PRELIM_FEB,
    CASE  WHEN (ccu.USAGE_MONTH = 'MAR') THEN ccu.usage
      ELSE NULL
      END PRELIM_MAR,
    CASE  WHEN (ccu.USAGE_MONTH = 'APR') THEN ccu.usage
      ELSE NULL
      END PRELIM_APR,
    CASE  WHEN (ccu.USAGE_MONTH = 'MAY') THEN ccu.usage
      ELSE NULL
      END PRELIM_MAY,
    CASE  WHEN (ccu.USAGE_MONTH = 'JUN') THEN ccu.usage
      ELSE NULL
      END PRELIM_JUNE,
    CASE  WHEN (ccu.USAGE_MONTH = 'JUL') THEN ccu.usage
      ELSE NULL
      END PRELIM_JULY,
    CASE  WHEN (ccu.USAGE_MONTH = 'AUG') THEN ccu.usage
      ELSE NULL
      END PRELIM_AUG,
    CASE  WHEN (ccu.USAGE_MONTH = 'SEP') THEN ccu.usage
      ELSE NULL
      END PRELIM_SEPT,
    CASE  WHEN (ccu.USAGE_MONTH = 'OCT') THEN ccu.usage
      ELSE NULL
      END PRELIM_OCT,
    CASE  WHEN (ccu.USAGE_MONTH = 'NOV') THEN ccu.usage
      ELSE NULL
      END PRELIM_NOV,
    CASE  WHEN (ccu.USAGE_MONTH = 'DEC') THEN ccu.usage
      ELSE NULL
      END PRELIM_DEC
    FROM cont_choice cc
    inner join
    cont_choice_usage ccu
    on 
    cc.account_nbr = ccu.account_nbr
    but I need all of the data for the year on one row.

    What am I doing wrong?

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Man, In my eyes that is a pretty aggressive use of a case statement. I would approach the problem by using a DECODE statement. I am not saying this is exactly what you need or that what I have is syntacticly correct but it is the jist of what you need

    Code:
    SELECT 
    cc.ACCOUNT_NBR,
    cc.PREMISE,
    cc.CHOICE_YEAR,
    sum(DECODE(ccu.USAGE_MONTH, 'JAN', ccu.usage, 0)) "JAN_USAGE",
    sum(DECODE(ccu.USAGE_MONTH, 'FEB', ccu.usage, 0)) "FEB_USAGE",
    etc.
    HTH
    Todd
    NOTE: Please disregard the label "Senior Member".

  3. #3
    Join Date
    Feb 2005
    Posts
    2

    danke!

    worked perfectly!

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Yea for me!!
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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