Could be that there are null values in EMPID which then the MAX might be
NULL.
Use nvl(max(empid),0) instead.


-----Original Message-----
From: Sachidananda_T
[mailto:oracledba-ezmlmshield-x63669305.[Email address protected]
Sent: Tuesday, September 27, 2005 10:42 PM
To: LazyDBA Discussion
Subject: Insert problem

Hi,



A statement like this sometimes gives me Cannot insert null into EMPID
column even though EMP_REG has a record with ename as SACHI.



This is very strange with oracle.



INSERT INTO EMP(EMPID,ENAME) VALUES( (SELECT MAX(EMPID) FROM EMP_REG
WHERE ENAME='SACHI'),'DFGH');



Of course I can replace this as



DECLARE

MAX_EMP NUMER:=0;

BEGIN

SELECT MAX(EMPID) INTO MAX_EMP FROM EMP_REG);

INSERT INTO EMP(EMPID,ENAME) VALUES( MAX_EMP,'DFGH');

END;

And this works fine.



But I would like to know why the above INSERT has failed.



Regards

Sachi









DISCLAIMER:
This email (including any attachments) is intended for the sole use of
the intended recipient/s and may contain material that is CONFIDENTIAL
AND PRIVATE COMPANY INFORMATION. Any review or reliance by others or
copying or distribution or forwarding of any or all of the contents in
this message is STRICTLY PROHIBITED. If you are not the intended
recipient, please contact the sender by email and delete all copies;
your cooperation in this regard is appreciated.


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com To unsubscribe:
see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com By using this list you agree
to these terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page