Home » SQL & PL/SQL » SQL & PL/SQL » Sql error for above query when migarted from oracle 9i to oracle 10g
Sql error for above query when migarted from oracle 9i to oracle 10g [message #390958] Tue, 10 March 2009 08:27 Go to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi All,

Good day.

Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


Problem:

SELECT DISTINCT MRSA01_APP_NUM_R,MRSA01_APP_ACRONYM_C,MRSA01_LOB_C,
MRSA12_DEPT_N,MAX(MRSA03_SUPERVISOR_ID),MRSA01_APP_TYPE_C 
FROM IMRSA01_APPLICATION, IMRSA06_APP__STATUS STATUS1,IMRSA03_APP__RESOURCE,IMRSA12_DEPARTMENT 
WHERE MRSA01_APP_NUM_R = MRSA06_APP_NUM_R 
AND MRSA01_APP_NUM_R = MRSA03_APP_NUM_R 
AND MRSA01_PTG_DEPT_K = MRSA12_DEPT_K(+) 
AND MRSA06_STATUS_C IN ('A') 
AND MRSA06_STATUS_DATE_Y = (SELECT MAX(MRSA06_STATUS_DATE_Y)
 FROM IMRSA06_APP__STATUS STATUS2 
WHERE STATUS2.MRSA06_APP_NUM_R = STATUS1.MRSA06_APP_NUM_R) 
AND MRSA01_LOB_C = 'MKT-SLS' 
AND MRSA01_CENTER_C = 'NA' 
GROUP BY MRSA01_APP_NUM_R, MRSA01_APP_ACRONYM_C, MRSA01_LOB_C, MRSA12_DEPT_N, MRSA01_APP_TYPE_C 
ORDER BY 
(SELECT COUNT(*) 
FROM IMRSA30_INCIDENT, IMRSA01_APPLICATION 
WHERE MRSA30_APP_NUM_R = MRSA01_APP_NUM_R AND MRSA30_REP_TIME_Y > ADD_MONTHS(TRUNC(SYSDATE, 'MON'), (1 - 12))  
AND MRSA30_APP_SPVSR_SIGNOFF IS NOT NULL AND MRSA30_DEFECT_TYPE_R < 3) ASC


we are facing the Sql error for above query when migarted from oracle 9i to oracle 10g

when count =0 or count >5 inside orderby query , we are getting the below error in 10g

Database Error
Error while trying to retrieve text for error ORA-03113 (DBD ERROR: error possibly near <*> indicator at char 140 in 'SELECT DISTINCT MRSA01_APP_NUM_R, MRSA01_APP_ACRONYM_C, MRSA01_LOB_C, MRSA12_DEPT_N, MAX(MRSA03_SUPERVISOR_ID), MRSA01_APP_TYPE_C FROM <*>IMRSA01_APPLICATION APP1, IMRSA06_APP__STATUS STATUS1, IMRSA03_APP__RESOURCE, IMRSA12_DEPARTMENT WHERE MRSA01_APP_NUM_R = MRSA06_APP_NUM_R AND MRSA01_APP_NUM_R = MRSA03_APP_NUM_R AND MRSA01_PTG_DEPT_K = MRSA12_DEPT_K(+) AND MRSA06_STATUS_C IN ('A') AND MRSA06_STATUS_DATE_Y = (SELECT MAX(MRSA06_STATUS_DATE_Y) FROM IMRSA06_APP__STATUS STATUS2 WHERE STATUS2.MRSA06_APP_NUM_R = STATUS1.MRSA06_APP_NUM_R) AND MRSA01_CENTER_C = 'NA' GROUP BY MRSA01_APP_NUM_R, MRSA01_APP_ACRONYM_C, MRSA01_LOB_C, MRSA12_DEPT_N, MRSA01_APP_TYPE_C ORDER BY (SELECT COUNT(*) FROM IMRSA30_INCIDENT WHERE MRSA30_APP_NUM_R = APP1.MRSA01_APP_NUM_R AND MRSA30_REP_TIME_Y > ADD_MONTHS(TRUNC(SYSDATE, 'MON'), (1 - 12)) AND MRSA30_APP_SPVSR_SIGNOFF IS NOT NULL) ASC')



Thanks & Regards
Thangam

[Updated on: Tue, 10 March 2009 09:08] by Moderator

Report message to a moderator

Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #390960 is a reply to message #390958] Tue, 10 March 2009 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00600/ORA-07445/ORA-03113 = Oracle bug => search on Metalink and/or call Oracle support

Regards
Michel
Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #390985 is a reply to message #390960] Tue, 10 March 2009 10:31 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Thanks a lot Michel.

Recently my application DBA's migrated from 9i to 10g. That was the reason the above query is not working.

Also whether I need to contact them or just I need to inform DBA team. Like it is related to Oracle bug you have to contact Oracle support team.

Could you please advice me.

Thanks & Regards
Thangam.
Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #390991 is a reply to message #390985] Tue, 10 March 2009 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Inform your DBA team, give them all relevant information (for instance and above all your client version with 4 decimals), and ask them to investigate.

Regards
Michel
Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #391161 is a reply to message #390991] Wed, 11 March 2009 05:08 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Michel,

I inofrmed my DBA's they are saying i need to re-write the query for this fix.

DBA's suggestion:
Quote:

For the SQL query to return the row in a particular order from a set of tables, the order by clause must have a column ( or a calculation ) joined with the same instance of the table(s).

This query does not follow the basic rules of SQL queries.



Query details:

SELECT DISTINCT 
       MRSA01_APP_NUM_R, 
       MRSA01_APP_ACRONYM_C, 
       MRSA01_LOB_C, 
       MRSA12_DEPT_N, 
       MAX(MRSA03_SUPERVISOR_ID), 
       MRSA01_APP_TYPE_C 
  FROM [U][COLOR=blue]IMRSA01_APPLICATION[/COLOR][/U], 
       IMRSA06_APP__STATUS STATUS1, 
       IMRSA03_APP__RESOURCE, 
       IMRSA12_DEPARTMENT 
 WHERE MRSA01_APP_NUM_R = MRSA06_APP_NUM_R 
   AND MRSA01_APP_NUM_R = MRSA03_APP_NUM_R 
   AND MRSA01_PTG_DEPT_K = MRSA12_DEPT_K(+) 
   AND MRSA06_STATUS_C IN ('A') 
   AND MRSA06_STATUS_DATE_Y = (SELECT MAX(MRSA06_STATUS_DATE_Y) 
                                 FROM IMRSA06_APP__STATUS STATUS2 
                                 WHERE STATUS2.MRSA06_APP_NUM_R = STATUS1.MRSA06_APP_NUM_R) 
   AND MRSA01_LOB_C = 'IT'
   AND MRSA01_CENTER_C = 'NA'
 GROUP BY MRSA01_APP_NUM_R, MRSA01_APP_ACRONYM_C, MRSA01_LOB_C, MRSA12_DEPT_N, MRSA01_APP_TYPE_C 
 ORDER BY (SELECT COUNT(*) 
             FROM IMRSA30_INCIDENT,                    
                [COLOR=blue]  IMRSA01_APPLICATION[/COLOR]  <====== this is not same instance of the table in the main query(in blue above)
            WHERE MRSA30_APP_NUM_R = MRSA01_APP_NUM_R 
              AND MRSA30_REP_TIME_Y > ADD_MONTHS(TRUNC(SYSDATE, 'MON'), (1 - 12)) 
              AND MRSA30_APP_SPVSR_SIGNOFF IS NOT NULL) ASC ; <====== OR, not joined with the table(s) in the main query

Can you please advice me, how can i re-write the above query.

Let me know if you need any more details.

Thanks & Regards
Thangam.

[Updated on: Wed, 11 March 2009 05:24] by Moderator

Report message to a moderator

Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #391169 is a reply to message #391161] Wed, 11 March 2009 05:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I really doubt whether your order by would not return a constant value. (also in your previous, 9i, version)
Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #391173 is a reply to message #391169] Wed, 11 March 2009 06:02 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Frank,

The same query worked in when it was in Oracle 9i version, last week in my application upgarded from Oracle 9i to Oracle 10g.

Now the same query having issue. As per the DBA's suggestion i need change the query paticlury in Orader by class.

Let me know if you need any more details.

Thanks & Regards
Thangam.
Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #391175 is a reply to message #391173] Wed, 11 March 2009 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain what the order you want.
Currently your order by clause is about the same as "order by null".

Regards
Michel

[Updated on: Wed, 11 March 2009 06:26]

Report message to a moderator

Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #391182 is a reply to message #391175] Wed, 11 March 2009 06:58 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Michel,

Based the count i need to order, also i have attached the my output screen.

Example:

12
15
18
22
1000


SELECT COUNT(*) 

             FROM IMRSA30_INCIDENT,                    

                  IMRSA01_APPLICATION  <====== this is not same instance of the table in the main query(in blue above)

            WHERE MRSA30_APP_NUM_R = MRSA01_APP_NUM_R 

              AND MRSA30_REP_TIME_Y > ADD_MONTHS(TRUNC(SYSDATE, 'MON'), (1 - 12)) 

              AND MRSA30_APP_SPVSR_SIGNOFF IS NOT NULL)



Let me know whether i understanding your questions or not?


Thanks & Regards
Thangam
  • Attachment: ora.JPG
    (Size: 161.31KB, Downloaded 175 times)

[Updated on: Wed, 11 March 2009 07:04]

Report message to a moderator

Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #391187 is a reply to message #391182] Wed, 11 March 2009 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Based the count i need to order

On which count?
"ORDER BY" is executed AFTER the rest of the query, so your count is constant for all rows and so there is no order.

Remove the order by from your query. Look at it and say us, from the result set of this query without "order by", how you want to order.

Regards
Michel
Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #391207 is a reply to message #391187] Wed, 11 March 2009 07:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Michel Cadot wrote on Wed, 11 March 2009 13:13
Quote:
Based the count i need to order

On which count?
"ORDER BY" is executed AFTER the rest of the query, so your count is constant for all rows and so there is no order.

Remove the order by from your query. Look at it and say us, from the result set of this query without "order by", how you want to order.

Regards
Michel


Better yet: replace it by ORDER BY decode(1+1, 2, 'ABC').
Having an order by might fool the optimizer into taking a different path.
Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #391212 is a reply to message #391187] Wed, 11 March 2009 08:07 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Michel,

It is like counts of monthly incidents which is attached pic shows excetly.

column name : MRSA01_APP_NUM_R

Order by need to do based on the MRSA01_APP_NUM_R counts where every plant.

Thanks & Regards
Thangam.
Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #391214 is a reply to message #391212] Wed, 11 March 2009 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select ename, deptno
  2  from emp
  3  order by count(*) over (partition by deptno)
  4  /
ENAME          DEPTNO
---------- ----------
MILLER             10
KING               10
CLARK              10
ADAMS              20
FORD               20
JONES              20
SMITH              20
SCOTT              20
WARD               30
TURNER             30
ALLEN              30
JAMES              30
BLAKE              30
MARTIN             30

14 rows selected.

Regards
Michel
Re: Sql error for above query when migarted from oracle 9i to oracle 10g [message #391216 is a reply to message #391212] Wed, 11 March 2009 08:15 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Maybe if you use aliases in your query, it would make clear (at least to us) which table each column should be gotten from. (Thus showing how you think the order by query relates to the main query)
Previous Topic: Wait Time Command
Next Topic: Comma sepred string
Goto Forum:
  


Current Time: Wed Dec 07 10:22:31 CST 2016

Total time taken to generate the page: 0.20638 seconds