Home » SQL & PL/SQL » SQL & PL/SQL » Help me to solve error ORA-00904 (merged 3)
Help me to solve error ORA-00904 (merged 3) [message #399809] Fri, 24 April 2009 03:35 Go to next message
simi28
Messages: 2
Registered: April 2009
Junior Member
Hi All,

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

below is the code

decode(:p_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

[Updated on: Fri, 24 April 2009 04:48] by Moderator

Report message to a moderator

Re: Need help to solve error ORA-00904 [message #399811 is a reply to message #399809] Fri, 24 April 2009 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put a complete and working code or SQL.
Explain in details what you want to do.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Fri, 24 April 2009 03:40]

Report message to a moderator

Re: Need help to solve error ORA-00904 [message #399814 is a reply to message #399809] Fri, 24 April 2009 03:50 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Firstly, compare (or use on the same position) numbers with numbers and strings with strings.

> (select 1 aoi from dual union all select 0 from dual) returns numbers
> when (aoi='1') compares it with string

> then to_number(to_char(rptfrom,'MM')) is number
> else '0' is string

I do not know, what you want to do. Do you want to multiply rows in the resultset by two? It is not clear from your post, how you constructed the query, but it is working fine for me:
SQL> select case when (aoi=1) then to_number(to_char(sysdate,'MM'))
  2              else 0
  3         end as aoi
  4  from (select 1 aoi from dual union all select 0 from dual);

       AOI
----------
         4
         0

2 rows selected.

SQL> 
Re: Please help me to solve error ORA-00904 [message #399817 is a reply to message #399809] Fri, 24 April 2009 03:59 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Quote:
ORA-00904: string: invalid identifier
Cause: The column name entered is either missing or invalid.
Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.
Re: Need help to solve error ORA-00904 [message #399818 is a reply to message #399809] Fri, 24 April 2009 04:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can't refer to column aliases in the same query that you define them in.
You can't do:
SELECT 1 aoi
                         ,case when aoi = 1 then..

as the identifier AOI only exists at the level above this query.
You can do this:
SELECT case when aoi = 1 then...
FROM (SELECT 1 aoi...)
Re: Help me to solve error ORA-00904 (merged 3) [message #399847 is a reply to message #399809] Fri, 24 April 2009 04:48 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
STOP repeating the same question over and over.

Regards
Michel
Previous Topic: to_char(0.1) result is ,1 but must be 0,1
Next Topic: query
Goto Forum:
  


Current Time: Fri Dec 09 00:13:25 CST 2016

Total time taken to generate the page: 0.10156 seconds