1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-00904: "CODE_RED": invalid identifier

Discussion in 'General' started by rajender.duddu@gmail.com, Feb 1, 2011.

  1. rajender.duddu@gmail.com

    rajender.duddu@gmail.com Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    SELECT DISTINCT E.MTDB_ID AS MTDB_ID, E.RESPONSE_DT AS FIRST_RESP_DATE, B.CAMP_SRC_NUM AS SOURCE_CODE, C.ZIPCODE AS ZIP_CODE,
    CASE
    WHEN D.ANSWER_ID IN ('A21029', 'A21031', 'A21175', 'A21177', 'A22039', 'A22017','A22244', 'A22091', 'A22279',
    'A22314', 'A22119', 'A22249', 'A22128', 'A22388', 'A22412', 'A22425', 'A22423')
    THEN 'TYPE A'
    WHEN D.ANSWER_ID IN ('A21030', 'A21032', 'A21176', 'A21178', 'A22035', 'A22054','A22262', 'A22144', 'A22304',
    'A22364', 'A22322', 'A22285', 'A22299', 'A22469', 'A22400', 'A22413', 'A22426')
    THEN 'TYPE B'
    ELSE 'UNKNOWN'
    END AS HEMOPHILIA_TYPE,
    DENSE_RANK() over (Partition by E.MTDB_ID ORDER BY E.RESPONSE_DT) As CODE_RED
    FROM WC_CONSENT_D A,
    W_SOURCE_D B,
    W_PERSON_D C,
    WC_ANSWER_D D,
    WC_SURVEY_ANSWER_F E
    WHERE B.ROW_WID = E.SOURCE_WID
    AND A.MTDB_ID = E.MTDB_ID
    AND E.PERSON_WID = C.ROW_WID
    AND E.ANSWER_WID = D.ROW_WID
    AND A.CNSNT_STAT_CD = 'NI'
    AND E.PROD_CODE IN ('Benefix', 'Hemophilia', 'Xyntha')
    AND CODE_RED = 1 ;

    getting error NEED HELP -- ORA-00904: "CODE_RED": invalid identifier

    Can Anyone please let me know the problem with this query..

    Thanks and Regards,
    Laxmi Rajender Duddu.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Try this,

    Code (SQL):

    SELECT DISTINCT e.mtdb_id
                    AS mtdb_id,
                    e.response_dt
                    AS first_resp_date,
                    b.camp_src_num
                    AS source_code,
                    c.zipcode
                    AS zip_code,
                    CASE
                      WHEN d.answer_id IN ( 'A21029', 'A21031', 'A21175', 'A21177',
                                            'A22039', 'A22017', 'A22244', 'A22091',
                                            'A22279', 'A22314', 'A22119', 'A22249',
                                            'A22128', 'A22388', 'A22412', 'A22425',
                                            'A22423' ) THEN
                      'TYPE A'
                      WHEN d.answer_id IN ( 'A21030', 'A21032', 'A21176', 'A21178',
                                            'A22035', 'A22054', 'A22262', 'A22144',
                                            'A22304', 'A22364', 'A22322', 'A22285',
                                            'A22299', 'A22469', 'A22400', 'A22413',
                                            'A22426' ) THEN
                      'TYPE B'
                      ELSE 'UNKNOWN'
                    END
                    AS hemophilia_type,
                    Dense_rank() OVER (PARTITION BY e.mtdb_id ORDER BY
                    e.response_dt) AS code_red
    FROM   wc_consent_d a,
           w_source_d b,
           w_person_d c,
           wc_answer_d d,
           wc_survey_answer_f e
    WHERE  b.row_wid = e.source_wid
           AND a.mtdb_id = e.mtdb_id
           AND e.person_wid = c.row_wid
           AND e.answer_wid = d.row_wid
           AND a.cnsnt_stat_cd = 'NI'
           AND e.prod_code IN ( 'Benefix', 'Hemophilia', 'Xyntha' )
           AND Dense_rank() OVER (PARTITION BY e.mtdb_id ORDER BY
                    e.response_dt) = 1;  
     
  3. rajender.duddu@gmail.com

    rajender.duddu@gmail.com Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    Getting this error when executing the above query ORA-30483: window functions are not allowed here.

    Thanks and Regards,
    Laxmi Rajender Duddu.
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    hmmm, not sure... u got to wait until some of our SQL heavyweights turn up... :)
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Try this:

    Code (SQL):
     
    SELECT x.mtdb_id, x.first_resp_date, x.source_code, x.zip_code, x.code_red
    FROM
    (SELECT DISTINCT E.MTDB_ID AS MTDB_ID, E.RESPONSE_DT AS FIRST_RESP_DATE, B.CAMP_SRC_NUM AS SOURCE_CODE, C.ZIPCODE AS ZIP_CODE,
    CASE
    WHEN D.ANSWER_ID IN ('A21029', 'A21031', 'A21175', 'A21177', 'A22039', 'A22017','A22244', 'A22091', 'A22279',
    'A22314', 'A22119', 'A22249', 'A22128', 'A22388', 'A22412', 'A22425', 'A22423')
    THEN 'TYPE A'
    WHEN D.ANSWER_ID IN ('A21030', 'A21032', 'A21176', 'A21178', 'A22035', 'A22054','A22262', 'A22144', 'A22304',
    'A22364', 'A22322', 'A22285', 'A22299', 'A22469', 'A22400', 'A22413', 'A22426')
    THEN 'TYPE B'
    ELSE 'UNKNOWN'
    END AS HEMOPHILIA_TYPE,
    DENSE_RANK() OVER (Partition BY E.MTDB_ID ORDER BY E.RESPONSE_DT) AS CODE_RED
    FROM WC_CONSENT_D A,
    W_SOURCE_D B,
    W_PERSON_D C,
    WC_ANSWER_D D,
    WC_SURVEY_ANSWER_F E
    WHERE B.ROW_WID = E.SOURCE_WID
    AND A.MTDB_ID = E.MTDB_ID
    AND E.PERSON_WID = C.ROW_WID
    AND E.ANSWER_WID = D.ROW_WID
    AND A.CNSNT_STAT_CD = 'NI'
    AND E.PROD_CODE IN ('Benefix', 'Hemophilia', 'Xyntha')) x
    WHERE x.CODE_RED = 1 ;