Home » SQL & PL/SQL » SQL & PL/SQL » sql help (oracle 10.1.2)
sql help [message #349999] Tue, 23 September 2008 13:18 Go to next message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Hi All
I'm using the following query to get fields from 3 different views and a table and i'm involving a CASE statement as well and i'm getting the following error message. :

ORA-00923: FROM keyword not found where expected

Please, any help is greatly appreciated.

select PRIMES_AND_SUBS_VW,rfp_item_search_vw,AMENDMENT,ASSIGNMENT_PO_VW
CASE WHEN PRIMES_AND_SUBS_VW.FIRM_TYPE_CODE='S' THEN PRIMES_AND_SUBS_VW.PRIME_FIRM_NAME=NULL
     WHEN PRIMES_AND_SUBS_VW.FIRM_TYPE_CODE='P' THEN PRIMES_AND_SUBS_VW.SUB_FIRM_NAME=NULL
	 ELSE SUB_FIRM_NAME
	 END,
	 PRIMES_AND_SUBS_VW.amendment_number,buy_indiana,Firm_type_code,ntp_actual,prime_firm_name,sub_firm_name,
	 rfp_item_search_vw.CONTRACT_TYPE,
	 amendment.ACTION_AMOUNT,
	 ASSIGNMENT_PO_VW.work_order_amount,po_date
from (SELECT PRIMES_AND_SUBS_VW,rfp_item_search_vw,AMENDMENT,ASSIGNMENT_PO_VW
where ASSIGNMENT_PO_VW.contract_id=AMENDMENT.contract_id
AND RFP_ITEM_SEARCH_VW.ITEM_REQ_SEQ_NUM=PRIMES_AND_SUBS_VW.ITEM_REQ_SEQ_NUM
AND PRIMES_AND_SUBS_VW.CONTRACT_NUMBER=ASSIGNMENT_PO_VW.CONTRACT_NUMBER
Re: sql help [message #350012 is a reply to message #349999] Tue, 23 September 2008 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session, it will give where the error is.

Regards
Michel
Re: sql help [message #350018 is a reply to message #349999] Tue, 23 September 2008 14:46 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
Your second SELECT statement doesn't have a FROM clause.
Re: sql help [message #350024 is a reply to message #349999] Tue, 23 September 2008 15:34 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
aarti81 wrote on Tue, 23 September 2008 14:18
i'm getting the following error message. :

ORA-00923: FROM keyword not found where expected

Please, any help is greatly appreciated.



Oracle doesn't just make up these errors. As Ronald pointed out, Oracle already told you what the problem was.
Re: sql help [message #350120 is a reply to message #349999] Wed, 24 September 2008 02:54 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
That is a syntax error. You forgot a comma at the end of the first line.

By the way case doesn't work in that way you may change it in this way:
CASE 
	WHEN PRIMES_AND_SUBS_VW.FIRM_TYPE_CODE in ('S','P') THEN 
		NULL 
	ELSE 
		SUB_FIRM_NAME
END as SUB_FIRM_NAME


or

nullif(nullif(PRIMES_AND_SUBS_VW.FIRM_TYPE_CODE,'S'),'P') as FIRM_TYPE_CODE


Bye Alessandro
Re: sql help [message #350212 is a reply to message #350120] Wed, 24 September 2008 07:16 Go to previous message
aarti81
Messages: 235
Registered: December 2007
Location: USA
Senior Member
Thank you all very much
Alessandro Rossi wrote on Wed, 24 September 2008 02:54
That is a syntax error. You forgot a comma at the end of the first line.

By the way case doesn't work in that way you may change it in this way:
CASE 
	WHEN PRIMES_AND_SUBS_VW.FIRM_TYPE_CODE in ('S','P') THEN 
		NULL 
	ELSE 
		SUB_FIRM_NAME
END as SUB_FIRM_NAME


or

nullif(nullif(PRIMES_AND_SUBS_VW.FIRM_TYPE_CODE,'S'),'P') as FIRM_TYPE_CODE


Bye Alessandro

Previous Topic: using to_date command to display specific month
Next Topic: Problem with packages between Users
Goto Forum:
  


Current Time: Wed Dec 07 02:47:54 CST 2016

Total time taken to generate the page: 9.34705 seconds