OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » sql help (oracle 10.1.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql help [message #349999]

Tue, 23 September 2008 13:18

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

Report message to a moderator

Re: sql help [message #350012 is a reply to message #349999]

Tue, 23 September 2008 14:35

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use SQL*Plus and copy and paste your session, it will give where the error is.

Regards
Michel

Report message to a moderator

Re: sql help [message #350018 is a reply to message #349999]

Tue, 23 September 2008 14:46

Ronald Beck
Messages: 121
Registered: February 2003

Senior Member

Your second SELECT statement doesn't have a FROM clause.

Report message to a moderator

Re: sql help [message #350024 is a reply to message #349999]

Tue, 23 September 2008 15:34

joy_division
Messages: 4963
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.

Report message to a moderator

Re: sql help [message #350120 is a reply to message #349999]

Wed, 24 September 2008 02:54

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

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

Bye Alessandro

Report message to a moderator

Re: sql help [message #350212 is a reply to message #350120]

Wed, 24 September 2008 07:16

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

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

Bye Alessandro

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 20 11:26:02 CDT 2025