> I have the following generic query which I am trying to use to insert
> into a 3 field table
>
> 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
That's not really a good way to detect the existance of a row with those
values. If either subj or area is allowed to be NULL, wierdness occurs
(*). Even if neither is allowed to be null, if the values can ever have
more than one digit wierdness occurs.
(*) - IIRC, even if no row exists with (4,8) as (subj,area), if there's a
row where subj || area is NULL, you will never get true from the NOT IN
clause and the second alternative will always be chosen. NULLs and IN
don't play very well together.
