Home » SQL & PL/SQL » SQL & PL/SQL » missing parenthesis error (oracle 9.0.1.8)
missing parenthesis error [message #569981] Sun, 04 November 2012 03:01 Go to next message
engsng
Messages: 5
Registered: November 2012
Location: Istanbul
Junior Member
Hi guys, I just registered this forum. In my new job, I am implementing SQL codes. Here I stucked some issue in following code. I want to list attributes of table
A and look for some of the attribute "MEDIA_GATEWAY_NUMBER" in lookup table B if exists. If it exists in table B (which is stated column MG_NAME in table)I want to merge with corresponding value, if not I want to put'unknown' - Media gateway number.

When I executed this code, I got an error called 'MISSING RIGHT PARENTHESIS' that I could not solve.

When I remove
nvl(b.MG_NAME,'unknown') ||'-'|| A.MEDIA_GATEWAY_NUMBER "MG_NAME",
A.MEDIA_GATEWAY_NUMBER||'-'||IP MGNUMBER_IP ,

from both select and group by statement, it works..

I appreciate guys of your help.
Thanks

-------------------------------------------


SELECT
TR.DATETIME DATETIME,
TR.SS_NAME SS_NAME,
TR.MG_NAME MG_NAME,
TR.MGNUMBER_IP MGNUMBER_IP,
TR.AVG_UTIL_TRANSMIT AVG_UTIL_TRANSMIT,
TR.AVG_UTIL_RECEIVE AVG_UTIL_RECEIVE


FROM
(
SELECT
A.DATETIME,
A.SS_NAME,
nvl(b.MG_NAME,'unknown') ||'-'|| A.MEDIA_GATEWAY_NUMBER "MG_NAME",
A.MEDIA_GATEWAY_NUMBER||'-'||IP MGNUMBER_IP ,
SUM(ZTE_SOFTSWITCH.A.AVG_UTIL_TRANSMIT) AS AVG_UTIL_TRANSMIT,
SUM(ZTE_SOFTSWITCH.A.AVG_UTIL_RECEIVE) AS AVG_UTIL_RECEIVE

FROM
ZTE_SOFTSWITCH.V_STD_H248_STATS_HR A,
ZTE_SOFTSWITCH.H248_STATS_HR C, global.V_NGN_LOOKUP b
WHERE
%DATE AND
A.DATETIME = C.DATETIME AND
A.SS_NAME = C.SS_NAME AND
A.OBJECT_ID = C.OBJECT_ID AND
A.OBJECT_GROUP_ID = C.OBJECT_GROUP_ID AND
A.MEDIA_GATEWAY_NUMBER = C.MEDIA_GATEWAY_NUMBER AND
b.mg_id(+)=a.media_gateway_number
GROUP BY
A.DATETIME,
A.SS_NAME,
nvl(b.MG_NAME,'bilinmiyor') ||'-'|| A.MEDIA_GATEWAY_NUMBER "MG_NAME",
A.MEDIA_GATEWAY_NUMBER||'-'||IP MGNUMBER_IP
) TR
WHERE %FILTER
Re: missing parenthesis error [message #569982 is a reply to message #569981] Sun, 04 November 2012 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 57611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Use SQL*Plus and copy and paste your session.

Regards
Michel

Re: missing parenthesis error [message #569984 is a reply to message #569982] Sun, 04 November 2012 05:36 Go to previous messageGo to next message
Littlefoot
Messages: 18826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Problem is the GROUP BY clause - you don't use column aliases in there. For example:
select name ||' '|| address as name_address,
       count(*) cnt
from some_table
group by name ||' '|| address as name_address  --> WRONG! Don't use "as name_address" here!
group by name ||' '|| address                  --> OK

The same happens in your code as you used
group by ...
  nvl(b.MG_NAME,'bilinmiyor') ||'-'|| A.MEDIA_GATEWAY_NUMBER   "MG_NAME",    --> no "mg_name"
  A.MEDIA_GATEWAY_NUMBER||'-'||IP                              MGNUMBER_IP   --> no mgnumber_ip
Re: missing parenthesis error [message #569986 is a reply to message #569984] Sun, 04 November 2012 05:52 Go to previous messageGo to next message
engsng
Messages: 5
Registered: November 2012
Location: Istanbul
Junior Member
I solved the problem..

Many thanks Littlefoot
Re: missing parenthesis error [message #569990 is a reply to message #569986] Sun, 04 November 2012 08:40 Go to previous message
BlackSwan
Messages: 21944
Registered: January 2009
Senior Member
>(oracle 9.0.1.Cool
Oracle version above is ancient, obsoleted, unsupported, buggy and should have been abandoned yesterday.
Previous Topic: ORA-00902 invalid data type - pl/sql table cast error
Next Topic: ERROR PLS-00306
Goto Forum:
  


Current Time: Fri Apr 18 19:58:15 CDT 2014

Total time taken to generate the page: 0.08834 seconds