Help me to solve error ORA-00904 (merged 3) [message #399809] |
Fri, 24 April 2009 03:35  |
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
flyboy
Messages: 1903 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: Need help to solve error ORA-00904 [message #399818 is a reply to message #399809] |
Fri, 24 April 2009 04:00   |
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...)
|
|
|
|