OraFAQ Forum: SQL & PL/SQL » query required

Home » SQL & PL/SQL » SQL & PL/SQL » query required (oracle 10g)

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

query required [message #397893]

Tue, 14 April 2009 07:59

rajasekhar857
Messages: 500
Registered: December 2008

Senior Member

hi,
i am having query like this

UPDATE EMRIDS SET EZEMRXID = (SELECT MAX(PARENT_LAB_REQUEST_ID) FROM EMRParentLabRequestMaster) WHERE PROPERTY_NAME = 'PARENT_LAB_REQUEST_ID'
/

i am not having any records in EMRParentLabRequestMaster table.
so i am facing exception like this

Error starting at line 1,017 in command:
UPDATE EMRIDS SET EZEMRXID = (SELECT MAX(PARENT_LAB_REQUEST_ID) FROM EMRParentLabRequestMaster) WHERE PROPERTY_NAME = 'PARENT_LAB_REQUEST_ID'
Error report:
SQL Error: ORA-01407: cannot update ("TESTINGDATA"."EMRIDS"."EZEMRXID") to NULL
01407. 00000 -  "cannot update (%s) to NULL"
*Cause:    
*Action:

so i want some conditions for the above query without producing any exception.can we do it without any error?

Report message to a moderator

Re: query required [message #397895 is a reply to message #397893]

Tue, 14 April 2009 08:03

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

You need a correlated subquery to add the condition that the value must exist in the second table. That was a hint.

Report message to a moderator

Re: query required [message #397897 is a reply to message #397893]

Tue, 14 April 2009 08:05

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

Senior Member
Account Moderator

Quote:

can we do it without any error?

Do not try to update a NOT NULL field with NULL.

Use SQL Formatter and keep your lines in 80 characters.

Regards
Michel

Report message to a moderator

Re: query required [message #397899 is a reply to message #397897]

Tue, 14 April 2009 08:07

rajasekhar857
Messages: 500
Registered: December 2008

Senior Member

how to write a correlated sub query for this?
can't we directly do it?

Report message to a moderator

Re: query required [message #397901 is a reply to message #397899]

Tue, 14 April 2009 08:10

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Do you know what a correlated subquery is? If not, look it up in Google. I pretty much gave you the full answer in my hint. You need some problem solving ability to do it. If you do not have that ability, then programming is the wrong career path for you.

Report message to a moderator

Re: query required [message #397904 is a reply to message #397893]

Tue, 14 April 2009 08:20

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

Actually, if OP really just wants the max(PARENT_LAB_REQUEST_ID) of all the rows in EMRParentLabRequestMaster
(there's no where on the select) then a simple nvl will suffice.

Or you could just make sure there's actually some data in that table before you do anything else.

Report message to a moderator

Re: query required [message #397907 is a reply to message #397904]

Tue, 14 April 2009 08:27

rajasekhar857
Messages: 500
Registered: December 2008

Senior Member

UPDATE EMRIDS SET EZEMRXID =
COALESCE ((SELECT MAX(PARENT_LAB_REQUEST_ID) FROM EMRParentLabRequestMaster),0)
WHERE PROPERTY_NAME = 'PARENT_LAB_REQUEST_ID'
/

HELLO joy_division,
go and learn oracle once again ok

Report message to a moderator

Re: query required [message #397923 is a reply to message #397907]

Tue, 14 April 2009 09:54

Frank
Messages: 7901
Registered: March 2000

Senior Member

First of all, a bit of modesty might be in order, since YOU are the one who asked a question. joy_division took some of his FREE time to help you.
Secondly, how on earth could anyone but you know that you want a value of zero for the cases where no max could be found?

Report message to a moderator

Previous Topic:	SQL query
Next Topic:	Group by help (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 11 22:00:19 CST 2024