Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: invalid identifier on Production (Oracle 10g R2 , Windows and Aix 5.3)
ORA-00904: invalid identifier on Production [message #362451] Tue, 02 December 2008 06:11 Go to next message
sgborse
Messages: 6
Registered: December 2008
Location: mumbai
Junior Member
Dear all,

The following query executes without error on My development Windows Oracle 10g server.

I am getting ORA-00904 : invalid identifier for following query on AIX 5.3 Oracle 10g
select (select status_name
                 from (select Status_Name from v_aso_status
                                          where agency_id = ag.agency_id -- ORA-00904 : invalid identifier ag.agency_id
                                           AND STAGE_CODE = 'I'
                                           order by status_id desc)
         where rownum = 1) AS "INSPECTION STATUS"
       from v_Agencies ag
       where  ag.AGENCY_CLASS = 'Passenger'
              And (ag.Application_Type_Name = 'NEW APPLICATION' 
              And  ag.aims_date is not null 
              or   ag.Application_Type_Name = 'CHANGE REQUEST' 
              OR   ag.Application_Type_Name = 'MISCELLANEOUS')


[Mod-Edit: Frank added [code]-tags around the code to improve readability]

[Updated on: Tue, 02 December 2008 06:32] by Moderator

Report message to a moderator

Re: ORA-00904: invalid identifier on Production [message #362456 is a reply to message #362451] Tue, 02 December 2008 06:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
My bet is that the two schemas differ
Re: ORA-00904: invalid identifier on Production [message #362461 is a reply to message #362451] Tue, 02 December 2008 06:30 Go to previous messageGo to next message
sgborse
Messages: 6
Registered: December 2008
Location: mumbai
Junior Member
database version is 10.2.0.1.0
Re: ORA-00904: invalid identifier on Production [message #362467 is a reply to message #362461] Tue, 02 December 2008 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ag.agency_id is defined 2 levels up where it is called, so it is not known.
You can only refer to field that are in the query immedatly outer.

Regards
Michel

[Updated on: Tue, 02 December 2008 06:36]

Report message to a moderator

Re: ORA-00904: invalid identifier on Production [message #362468 is a reply to message #362456] Tue, 02 December 2008 06:42 Go to previous messageGo to next message
sgborse
Messages: 6
Registered: December 2008
Location: mumbai
Junior Member
The schemas are same.
But doesn't matter because when I execute the following command on Production database server It gets executed without error.
select (select status_name
          from (select v. Status_Name
                  from v_aso_status v, v_Agencies ag
                 where v.agency_id = ag.agency_id invalid identifier ag.agency_id
                       AND v.STAGE_CODE = 'I'
                 order by v.status_id desc)
         where rownum = 1) AS "INSPECTION STATUS"
  from v_Agencies ag
 where ag.AGENCY_CLASS = 'Passenger' And
       (ag.Application_Type_Name = 'NEW APPLICATION' And
       ag.aims_date is not null or
       ag.Application_Type_Name = 'CHANGE REQUEST' OR
       ag.Application_Type_Name = 'MISCELLANEOUS')
Re: ORA-00904: invalid identifier on Production [message #362470 is a reply to message #362467] Tue, 02 December 2008 06:52 Go to previous messageGo to next message
sgborse
Messages: 6
Registered: December 2008
Location: mumbai
Junior Member
The query is executing smoothly on my development server without
error.
As you said about levels for referencing the columns.
Then why it is executing without error on the development database
server.
Is there any oracle back-end/ parameter setting for it??
Re: ORA-00904: invalid identifier on Production [message #362474 is a reply to message #362470] Tue, 02 December 2008 07:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The Oracle version is not the same one.
10.2.0.1 has a bug that allows this but it is a bug and it is not allowed.

Regards
Michel

[Updated on: Tue, 02 December 2008 07:09]

Report message to a moderator

Re: ORA-00904: invalid identifier on Production [message #362481 is a reply to message #362451] Tue, 02 December 2008 07:22 Go to previous messageGo to next message
sgborse
Messages: 6
Registered: December 2008
Location: mumbai
Junior Member
The oracle version are same on Development and Production server.
i.e. 10.2.0.1.0 -64 bit for production
& 10.2.0.1.0 - 32 bit for Development
Re: ORA-00904: invalid identifier on Production [message #362492 is a reply to message #362481] Tue, 02 December 2008 07:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
32bit, 64bit not the same for me.

Anyway, the error is the one I mentionned.
Full stop.
Don't need to argue more.

Regards
Michel

[Updated on: Tue, 02 December 2008 07:46]

Report message to a moderator

Re: ORA-00904: invalid identifier on Production [message #362499 is a reply to message #362451] Tue, 02 December 2008 08:19 Go to previous messageGo to next message
sgborse
Messages: 6
Registered: December 2008
Location: mumbai
Junior Member
Thanks for the help & replying my doubts.

THE 64-bit & 32 -bit is the reason ?

[Updated on: Tue, 02 December 2008 08:22] by Moderator

Report message to a moderator

Re: ORA-00904: invalid identifier on Production [message #362500 is a reply to message #362499] Tue, 02 December 2008 08:23 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who knows? Only Oracle knows how they implement their stuff.
I detected the bug on 32bit but never tested it on 64bit.
(Remind: the bug is that there is no error returned.)

Regards
Michel

[Updated on: Tue, 02 December 2008 08:23]

Report message to a moderator

Previous Topic: how to query table that only have a value....
Next Topic: Partition truncate not releasing space?
Goto Forum:
  


Current Time: Fri Dec 09 05:57:10 CST 2016

Total time taken to generate the page: 0.08425 seconds