Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    7

    Unanswered: Please help me to solve error ORA-00904

    Hi All,

    I have a input p_groupmonths if p_groupmonths=1 then some value will display else 0.

    below is the code
    decode(_groupmonths,'1',to_number(to_char(rptfrom,'MM')), 0) perSl,

    instead of input valriable i want to show both 1 and 0 and if 1 then some value else 0
    for the same my query is below:



    (select 1 aoi from dual union all select 0 from dual) ,

    case
    when (aoi='1')
    then to_number(to_char(rptfrom,'MM'))
    else
    '0'
    end as aoi,


    But I a getting error. ORA-00904: "AOI": invalid identifier
    Can anybody help me to solve this?

    Thanks and Regards
    Simi

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Please post the full statement using [code] tags
    You have only shown us a small part of your statement and that is not enough to know why that error occurs.

    Btw: your sample CASE expression is wrong because you return a number in the THEN part but a character in the ELSE part.

  3. #3
    Join Date
    Apr 2009
    Posts
    7
    Please find the complete statement below:

    //Code starts here

    select countrycode,
    case
    -- aram country list
    when _country_list like '%,'||trim(countrycode)||',%'
    then countrycode
    else 'Others'
    end as country_code,

    case
    when
    (countrycode not in ('TP','CM') or countrycode is null)
    then
    'YY'
    else
    'ZZ' end as countylist,


    DUMPID as Dump_ID,

    -- decode(_groupmonths,'1',to_number(to_char(rptfrom,'MM')), 0) perSl,
    -- decode(_groupmonths,'1',to_char(rptfrom,'Mon'),0) perDesc,

    (select 1 aoi from dual union all select 0 from dual) ,


    case
    when (aoi='1')
    then to_number(to_char(rptfrom,'MM'))
    else
    0
    end as aoi,


    p.*
    from table1 p
    where 1=1
    AND substr(p.DUMPID,0,1) = 'M'
    AND VERSION='F'

    //code ends here

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You still didn't use [code] tags so the formatting is all messed up (including those smileys in the middle of your code)

  5. #5
    Join Date
    Apr 2009
    Posts
    7
    Can you please suggest me how can I post the code using <code>.
    as this is my first query.
    and the the variables where the smilies are displaying are just a variable you can use any caharacter in place of that smilie.

    Thanks

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by simi28
    Can you please suggest me how can I post the code using <code>.
    http://www.dbforums.com/misc.php?do=bbcode

    (Can be found by clicking on the FAQ link at the bottom of each page)

Posting Permissions

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