Home » SQL & PL/SQL » SQL & PL/SQL » Showing error ora-00923 from keyword not found where expected (oracle 9i)
Showing error ora-00923 from keyword not found where expected [message #415560] Tue, 28 July 2009 04:23 Go to next message
GURAVHARSHA
Messages: 9
Registered: July 2009
Location: Mumbai,India
Junior Member
Hi

I m getting above error in below query.
SELECT
  SUM(DECODE(A.DRCRTYPE, 'D', A.BRKAMT, 0)) DRAMT, 
  SUM(DECODE(A.DRCRTYPE, 'C', A.BRKAMT, 0)) CRAMT, 
  B.BANKCODE,
  A.GLACCODE,
  A.GLSUBACCODE
FROM FTXNACSDTL A,
     FTXNACSHDR B
WHERE A.ENTRYNO = B.ENTRYNO 
  AND A.CTRLFLAG = 'N' 
  AND B.PBCONTROL IS NULL 
GROUP BY A.ENTRYNO, A.GLACCODE, A.GLSUBACCODE

please help me.

Thanks
Haresh

[EDITED by LF: reformatted code and applied [code] tags]

[Updated on: Tue, 28 July 2009 04:29] by Moderator

Report message to a moderator

Re: Showing error ora-00923 from keyword not found where expected [message #415561 is a reply to message #415560] Tue, 28 July 2009 04:26 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You are missing something somewhere.

Oracle already told you in which row and column in the error message you didn't post completely.

[Updated on: Tue, 28 July 2009 04:28]

Report message to a moderator

Re: Showing error ora-00923 from keyword not found where expected [message #415563 is a reply to message #415561] Tue, 28 July 2009 04:31 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Heheheh, "somewhere somewhere" Smile

I did reformat the code (using a utility) but didn't modify it. Reviewed it as well, but - really, what's wrong with it? Seems to be OK (at least, for my poor 4 eyes).

@GURAVHARSHA, could you, please, copy-paste your SQL*Plus session so that we could see what's going on?

[Updated on: Tue, 28 July 2009 04:32]

Report message to a moderator

Re: Showing error ora-00923 from keyword not found where expected [message #415564 is a reply to message #415563] Tue, 28 July 2009 04:33 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yep. I also read the code a few times, and there doesn't seem to be anything wrong with it. So the problem is most likely in some part that isn't posted.

[Updated on: Tue, 28 July 2009 04:34]

Report message to a moderator

Re: Showing error ora-00923 from keyword not found where expected [message #415569 is a reply to message #415561] Tue, 28 July 2009 04:46 Go to previous messageGo to next message
GURAVHARSHA
Messages: 9
Registered: July 2009
Location: Mumbai,India
Junior Member
Thanks

Here with i m posting all query in highlighted column.


SELECT DRAMT , 
	   CRAMT , 
	   C.GLACNAME , 
       D.PARTYNAME ,
	   A.BANKCODE 
FROM 
	   FMSTACS C, CMSTPARTY D, 
(SELECT
	   	SUM(DECODE(DRCRTYPE,'D', BRKAMT,0)) DRAMT, 
		SUM(DECODE(DRCRTYPE,'C', BRKAMT,0)) CRAMT, 
		B.BANKCODE,
        A.GLACCODE,
        A.GLSUBACCODE
[B]FROM	FTXNACSDTL A,[/B]		FTXNACSHDR B
	WHERE	
--			B.COCODE = :COCODE 
--	AND 	B.DIVCODE =:DCODE  
--	AND 	B.TXNDT BETWEEN :DATEFROM 
--			AND 	:DATETO 
--	AND 	B.BANKCODE = :BANKFROM  
		 	B.PBCONTROL IS NULL 
--	AND 	:REPRINT ='N'
	AND 	A.CTRLFLAG = 'N' 
	AND 	A.ENTRYNO = B.ENTRYNO 
--AND  ((:approve = '1' AND ((B.APPROVE1 IS NULL) OR (B.APPROVE1 IS NOT NULL AND B.APPROVE2 IS NULL))) OR 
--       (:approve = '2' AND B.APPROVE2 IS NOT NULL) OR 
--       (:approve = '0') )

GROUP BY	B.BANKCODE,
            A.GLACCODE,
            A.GLSUBACCODE
) A
WHERE 
	  		A.GLACCODE = C.GLACCODE 
AND 		A.GLSUBACCODE = D.PARTYCODE(+)






[Updated on: Tue, 28 July 2009 04:50]

Report message to a moderator

Re: Showing error ora-00923 from keyword not found where expected [message #415570 is a reply to message #415569] Tue, 28 July 2009 04:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I can't see anything wrong with that on first glance either.

Post the entire SQL*Plus session where you run the query and get the error. Like this:

SQL> select 1 from dual where order by 1;
select 1 from dual where order by 1
                         *
ERROR at line 1:
ORA-00936: missing expression



Re: Showing error ora-00923 from keyword not found where expected [message #415571 is a reply to message #415569] Tue, 28 July 2009 04:52 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Though, there's an empty line in front of the GROUP BY clause.

Once again: copy-paste your SQL*Plus session (not your TOAD or PL/SQL Developer or whatever tool you use). I'd like to see SQL> prompt and Oracle's response to your statement.
Re: Showing error ora-00923 from keyword not found where expected [message #415572 is a reply to message #415570] Tue, 28 July 2009 04:53 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah, I just spotted an empty line in there. Sometimes SQL*Plus doesn't like empty lines in queries.
Re: Showing error ora-00923 from keyword not found where expected [message #415576 is a reply to message #415572] Tue, 28 July 2009 05:15 Go to previous messageGo to next message
GURAVHARSHA
Messages: 9
Registered: July 2009
Location: Mumbai,India
Junior Member
Thanks ThomasG and Littlefoot.

When i run the query in SQL Plus it is showing me desired output, but when i run same query in TOAD, It is giving me an error mentioned above .
why this happening

Regards
Haresh
Re: Showing error ora-00923 from keyword not found where expected [message #415582 is a reply to message #415576] Tue, 28 July 2009 05:21 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't believe it until I see it. Copy-paste SQL*Plus session and attach screenshot (PNG or JPG) of TOAD session.
Re: Showing error ora-00923 from keyword not found where expected [message #415591 is a reply to message #415582] Tue, 28 July 2009 05:46 Go to previous messageGo to next message
GURAVHARSHA
Messages: 9
Registered: July 2009
Location: Mumbai,India
Junior Member
Hi

Please find the attached printshot.

Regards
Haresh./fa/6617/0/
Re: Showing error ora-00923 from keyword not found where expected [message #415592 is a reply to message #415591] Tue, 28 July 2009 05:47 Go to previous messageGo to next message
GURAVHARSHA
Messages: 9
Registered: July 2009
Location: Mumbai,India
Junior Member
Hi

This is second one in toad session.

[Updated on: Tue, 28 July 2009 05:53]

Report message to a moderator

Re: Showing error ora-00923 from keyword not found where expected [message #415597 is a reply to message #415592] Tue, 28 July 2009 05:54 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There is a completely different error in the TOAD session, most likely because you just highlighted the word "from" and only ran the word "from".

Also, Copy and past the SQL*Plus session as TEXT in the future.
Re: Showing error ora-00923 from keyword not found where expected [message #415598 is a reply to message #415597] Tue, 28 July 2009 05:56 Go to previous messageGo to next message
GURAVHARSHA
Messages: 9
Registered: July 2009
Location: Mumbai,India
Junior Member
Hi ThomasG

please check new modified printshot.

I have just change it.

Thanks and Regards
Haresh
Re: Showing error ora-00923 from keyword not found where expected [message #415601 is a reply to message #415598] Tue, 28 July 2009 06:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, if it works in SQL*Plus and not in TOAD it seems to be a TOAD bug. Maybe TOAD has problems with Table aliases?
Re: Showing error ora-00923 from keyword not found where expected [message #415603 is a reply to message #415598] Tue, 28 July 2009 06:04 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm. Strange. What happens if you (while in TOAD) delete the whole FROM clause and rewrite it once again? Or, even better, rewrite the whole query? Now don't copy-paste it, take some time and type on the keyboard. Perhaps there was some ... I don't know, escape character, invisible to you but which makes problems during query execution.
Re: Showing error ora-00923 from keyword not found where expected [message #415607 is a reply to message #415603] Tue, 28 July 2009 06:13 Go to previous messageGo to next message
GURAVHARSHA
Messages: 9
Registered: July 2009
Location: Mumbai,India
Junior Member
Thanks LittleFoot and ThomasG for your valuable co-operation.

Regards
Haresh
Re: Showing error ora-00923 from keyword not found where expected [message #415618 is a reply to message #415607] Tue, 28 July 2009 06:59 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, what/who was the culprit?
Re: Showing error ora-00923 from keyword not found where expected [message #415626 is a reply to message #415560] Tue, 28 July 2009 07:53 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
GURAVHARSHA wrote on Tue, 28 July 2009 05:23

SELECT
  SUM(DECODE(A.DRCRTYPE, 'D', A.BRKAMT, 0)) DRAMT, 
  SUM(DECODE(A.DRCRTYPE, 'C', A.BRKAMT, 0)) CRAMT, 
  B.BANKCODE,
  A.GLACCODE,
  A.GLSUBACCODE
FROM FTXNACSDTL A,
     FTXNACSHDR B
WHERE A.ENTRYNO = B.ENTRYNO 
  AND A.CTRLFLAG = 'N' 
  AND B.PBCONTROL IS NULL 
GROUP BY A.ENTRYNO, A.GLACCODE, A.GLSUBACCODE




Going back to your first post, your group by does not match the selected columns.
Re: Showing error ora-00923 from keyword not found where expected [message #415694 is a reply to message #415626] Tue, 28 July 2009 15:01 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What an eye! ./fa/1950/0/

Though, it would raise "ORA-00979: not a GROUP BY expression", not the one @OP is complaining about.
Re: Showing error ora-00923 from keyword not found where expected [message #415695 is a reply to message #415694] Tue, 28 July 2009 15:12 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Yep, but I also noticed that the OP query completely changed and just assumed that he was not giving us the full picture even after posting the second query.
Previous Topic: bulk collect
Next Topic: grant execute to procedure
Goto Forum:
  


Current Time: Tue Dec 06 02:54:19 CST 2016

Total time taken to generate the page: 0.06042 seconds