Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2009
    Posts
    3

    Unanswered: invalid identifier! Plz help!

    SELECT * FROM
    (
    SELECT CTRY_NM Geography, FAMILY_NM Product,
    CASE WHEN TO_CHAR(FORECAST_DT,'MON-YYYY') = 'JAN-2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END jan_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'MON-YYYY') = 'FEB-2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END Feb_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'MON-YYYY') = 'MAR-2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END MAR_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'MON-YYYY') = 'APR-2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END APR_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'MON-YYYY') = 'MAY-2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END MAY_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'MON-YYYY') = 'JUN-2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END JUN_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'MON-YYYY') = 'JUL-2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END JUL_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'MON-YYYY') = 'AUG-2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END AUG_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'MON-YYYY') = 'SEP-2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END SEP_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'MON-YYYY') = 'OCT-2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END OCT_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'MON-YYYY') = 'NOV-2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END NOV_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'MON-YYYY') = 'DEC-2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END DEC_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'YYYY') = '2009' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END Year_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'YYYY') = '2010' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END Year_10,
    CASE WHEN TO_CHAR(FORECAST_DT,'YYYY') = '2011' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END Feb_09,
    CASE WHEN TO_CHAR(FORECAST_DT,'YYYY') = '2009' OR TO_CHAR(FORECAST_DT,'YYYY') = '2010' OR TO_CHAR(FORECAST_DT,'YYYY') = '2011' THEN
    NVL( SUM(DECODE(CATEGORY_TYPE, 'forecast_prior',FORMUNITS)),0) - NVL(SUM(DECODE(CATEGORY_TYPE, 'forecast_current',FORMUNITS)),0)
    END Year_11
    FROM GSPDBA.SOP_FORECAST_MONTHLY_MV
    WHERE FAMILY_NM IN ('ARCOXIA', 'Fosamax' ) -- values selected from prompts
    AND CTRY_NM IN ('SOUTH AFRICA', 'SUB-SAHARA' ) -- values selected from prompts
    AND INCLUDE_ITEM = 'Y'
    AND TRANS_TYPE IN ('trade', 'sample', 'adjustment') -- values selected from prompts
    AND FORECAST_DT BETWEEN '01-JAN-2009' AND '31-DEC-2011'
    GROUP BY CTRY_NM, FAMILY_NM, TO_CHAR(FORECAST_DT,'MON-YYYY')
    ) GROUP BY FORECAST_DT


    this query is flagging me "Forecast_DT" invalid identifier ORA-00904

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I am intrigued that you say the message refers to "Forecast_DT", with mixed case. Is that what the message actually says? If you use column names in double quotes then they become case-sensitive, and "Forecast_DT" is not the same as "FORECAST_DT" or even Forecast_DT (without the double quotes).

Posting Permissions

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