Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Syntax error (oracle 9i)
Oracle Syntax error [message #319670] Mon, 12 May 2008 09:45 Go to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
correct query :
SELECT INSERT_COUNT.ACCOUNT_KEY, INSERT_COUNT.INSERT_CNT, UPDATE_COUNT.UPDATE_CNT
FROM
(SELECT Account_Key,COUNT(*) INSERT_CNT
FROM HCDB_AIFA3100_HIST_V
WHERE REC_STAT_CD = 'I'
GROUP BY ACCOUNT_KEY,REC_STAT_CD) INSERT_COUNT

inner jOIN

(SELECT Account_Key,COUNT(*) UPDATE_CNT
FROM HCDB_AIFA3100_HIST_V
WHERE REC_STAT_CD = 'U'
GROUP BY ACCOUNT_KEY,REC_STAT_CD) UPDATE_COUNT

ON INSERT_COUNT.ACCOUNT_KEY = UPDATE_COUNT.ACCOUNT_KEY

=============================the issue is below ==============

I want to add a delete flag count So I am making a table say X of the above query. Later making a left outer join of X with a query for Delete count.

But getting a error as syntax: 3706 Expected some thing between ‘)’ and the word X.

SELECT X.ACCOUNT_KEY, X.INSERT_CNT, X.UPDATE_CNT,DELETE_COUNT.DELETE_CNT

FROM

((SELECT Account_Key,COUNT(*) INSERT_CNT
FROM HCDB_AIFA3100_HIST_V

WHERE REC_STAT_CD = 'I'
GROUP BY ACCOUNT_KEY,REC_STAT_CD) INSERT_COUNT

inner jOIN

(SELECT Account_Key,COUNT(*) UPDATE_CNT
FROM HCDB_AIFA3100_HIST_V
WHERE REC_STAT_CD = 'U'
GROUP BY ACCOUNT_KEY,REC_STAT_CD) UPDATE_COUNT
ON INSERT_COUNT.ACCOUNT_KEY = UPDATE_COUNT.ACCOUNT_KEY)
X
left outer join (SELECT Account_Key,COUNT(*) DELETE_CNT
FROM HCDB_AIFA3100_HIST_V
WHERE REC_STAT_CD = 'D'
GROUP BY ACCOUNT_KEY,REC_STAT_CD) DELETE_COUNT on

X.ACCOUNT_KEY=DELETE_COUNT.ACCOUNT_KEY



The error am getting
But getting a error as syntax: 3706 Expected some thing between ‘)’ and the word X

Regards,
Avril









Re: Oracle Syntax error [message #319674 is a reply to message #319670] Mon, 12 May 2008 10:00 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
am not able to figure out wat is the syntax issue
Re: Oracle Syntax error [message #319675 is a reply to message #319674] Mon, 12 May 2008 10:07 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
avrillavinge wrote on Mon, 12 May 2008 08:00
am not able to figure out wat is the syntax issue


neither can I.


http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Oracle Syntax error [message #319676 is a reply to message #319674] Mon, 12 May 2008 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do do I, I am unbale to read unformated post.
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 the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Oracle Syntax error [message #319685 is a reply to message #319670] Mon, 12 May 2008 10:58 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Don't you think this looks lot more readable and easier to understand. Formatting always helps.

SELECT X.ACCOUNT_KEY, X.INSERT_CNT, X.UPDATE_CNT,DELETE_COUNT.DELETE_CNT
FROM
(
 select insert_count.account_key from  -- I have added this line
 (
  SELECT Account_Key,COUNT(*) INSERT_CNT
    FROM HCDB_AIFA3100_HIST_V
  WHERE REC_STAT_CD = 'I'
  GROUP BY ACCOUNT_KEY,REC_STAT_CD
 ) INSERT_COUNT
 inner jOIN
 (
  SELECT Account_Key,COUNT(*) UPDATE_CNT
      FROM HCDB_AIFA3100_HIST_V
  WHERE REC_STAT_CD = 'U'
  GROUP BY ACCOUNT_KEY,REC_STAT_CD
 ) UPDATE_COUNT
 ON INSERT_COUNT.ACCOUNT_KEY = UPDATE_COUNT.ACCOUNT_KEY
) X
left outer join 
(
 SELECT Account_Key,COUNT(*) DELETE_CNT
     FROM HCDB_AIFA3100_HIST_V
 WHERE REC_STAT_CD = 'D'
 GROUP BY ACCOUNT_KEY,REC_STAT_CD
) DELETE_COUNT 
on X.ACCOUNT_KEY = DELETE_COUNT.ACCOUNT_KEY

Regards

Raj

P.S : I have not tested the code.
Re: Oracle Syntax error [message #319687 is a reply to message #319685] Mon, 12 May 2008 11:08 Go to previous messageGo to next message
avrillavinge
Messages: 98
Registered: July 2007
Member
thanks.. will check ..thank u so much
Re: Oracle Syntax error [message #319693 is a reply to message #319670] Mon, 12 May 2008 12:30 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
avrillavinge wrote on Mon, 12 May 2008 10:45

The error am getting
But getting a error as syntax: 3706 Expected some thing between ‘)’ and the word X



linuxy SCOTT>!oerr ora 3706
37060, 0000, "(XSMCSESS08) %v is not a valid custom member in dimension %j."
// *Cause: No custom member or invalid custom member
// *Action: Create the custom member or use the correct custom member
Previous Topic: query to find bonus qty and ytd quantity form a given table (merged)
Next Topic: Joining two Select Statements
Goto Forum:
  


Current Time: Wed Dec 07 10:42:54 CST 2016

Total time taken to generate the page: 0.13250 seconds