Re: problem with inserting a generated seq no into not null field

On Tue, 08 Jun 2004 12:37:48 +0100, mike 
wrote:
>INSERT INTO tb_files (subj, area, file_no) select '4', '8',(SELECT CASE
>WHEN (4||8) NOT IN (SELECT (subj||area) FROM tb_files) THEN '1' ELSE
>max(file_no)+1 END FROM tb_files GROUP BY (subj||area),subj,area HAVING
>subj = '4' AND area = '8');

If there is no row with subj = '4' AND area = '8', then SELECT ...
HAVING returns no rows and therefore max(file_no) is NULL.  And NULL+1
is NULL.  Try

	INSERT INTO tb_files (subj, area, file_no)
	SELECT '4', '8', coalesce(max(file_no), 0)+1
	  FROM tb_files
	 WHERE subj = '4' AND area = '8';

Servus
 Manfred