• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 746
  • Last Modified:

CAN WE USE MUTIPLE GROUP FUNCTIONS IN ORACLE 9I

Hi
Please note this query is resulting an output in Oracle 10G but the same is not working with Oracle 9i

SELECT ProviderName, ProviderID, Count(ProviderID) AS NumOfReferrals,  MAX(SubmitDate) AS MaxDate
FROM ReferralView
GROUP BY ProviderName, ProviderID;
I am given the below error trying to execute this query from the sql prompt and the session gets terminated.
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Please update

Thank you

Surya
0
suryapinna
Asked:
suryapinna
  • 2
  • 2
3 Solutions
 
sujith80Commented:
Try these two alternatives.

SELECT ProviderName, ProviderID, Count(*) AS NumOfReferrals,  MAX(SubmitDate) AS MaxDate
FROM ReferralView
GROUP BY ProviderName, ProviderID;
 
 
SELECT ProviderName, ProviderID, Count(*) AS NumOfReferrals,  MAX(SubmitDate) AS MaxDate
FROM 
(
SELECT ProviderName, ProviderID, SubmitDate, rownum r
FROM ReferralView
)
GROUP BY ProviderName, ProviderID;

Open in new window

0
 
SJT2003ACommented:
Apparently the error message is nothing to do with the given multiple aggregate functions in the SQL.
The error ORA-03113 occurs when an unexpected end-of-file occurred during the communication channel. There should be something else that is causing a communication failure between the server and your oracle client.

Are you using Unix or Linux environments where the environment variable TWO_TASK is set specifying that you are connecting to a remote server?

This error could also occur if the shadow two-task process associated with the connection terminated abnormally.

Let me check if there is any reference could be found on ORACLE and get back to you.

Good luck :)


0
 
SJT2003ACommented:
Here it is, per the ORACLE documentation and description given for ORA-03113

ORA-03113 end-of-file on communication channel
Cause: An unexpected end-of-file was processed on the communication channel. The problem could not be handled by the Net8 two-task software. This message could occur if the shadow two-task process associated with a Net8 connect has terminated abnormally, or if there is a physical failure of the interprocess communication vehicle, that is, the network or server machine went down.

In addition, this message could occur when any of the following statements/commands have been issued:

ALTER SYSTEM KILL SESSION ... IMMEDIATE
ALTER SYSTEM DISCONNECT SESSION ... IMMEDIATE
SHUTDOWN ABORT/IMMEDIATE/TRANSACTIONAL
Action: If this message occurs during a connection attempt, check the setup files for the appropriate Net8 driver and confirm Net8 software is correctly installed on the server. If the message occurs after a connection is well established, and the error is not due to a physical failure, check if a trace file was generated on the server at failure time. Existence of a trace file may suggest an Oracle internal error that requires the assistance of Oracle Support Services.


Hope this helps.
Good luck :)
0
 
sujith80Commented:
>> ORA-03113: end-of-file on communication channel

Yes you are right.
It is a very vague error, which could be resulted from N number of reasons.

Most of the times, it could be becuase of a bug and it may require patching.
The best resolution is - to contact Oracle support. They can dig your trace files and log files to locate the actual problem.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
suryapinna: it would be good if you contact your DBA first for this error and then they can decide whether they need to go for oracle support or they can resolve it by themselves.

As updated already, query is fine and it has to work fine even in 9i. So request DBA's to have a look as to why you are getting ora-03113
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now