RE: simple 4gl programme

Alright - it's bigger than a script, it needs a small program. Effectively you're setting bkrec.bkr_linkref == bkref.bkr_batchref, in special circumstances. Is this what you really want? You get NULLs because bkrec contains records that do not map to bkpaymnt (with your query constraints). In other words, bkrec contains more records than bkpayment. It's simplest to do this in a program (i4gl, c, or perl with DBI) FUNCTION some_function() DEFINE pbkrec RECORD LIKE bkrec.*, pbkpayment RECORD bkpm_linkref LIKE bkpayment.bkpm_linkref END RECORD, text CHAR( 512 ) LET text = "SELECT * FROM bkrec" PREPARE pc_bkrec FROM text DECLARE qc_bkrec CURSOR FOR pc_bkrec FOR UPDATE LET text = "SELECT bkpayment.bkpm_linkref FROM bkpayment ", "WHERE bkpayment.some_field = ? ", ... PREPARE pc_bkpayment FROM scratch DECLARE qc_bkpayment CURSOR FOR pc_bkpayment FOREACH qc_bkrec INTO pbkrec.* LET pbkrec.bkr_currvalue = pbkrec.bkr_currvalue - 1 OPEN qc_bkpayment using .... FETCH qc_bkpayment INTO pbkpayment.* IF sqlca.sqlcode = 0 AND sqlca.sqlerrd[ 3 ] THEN UPDATE bkrec USING pbkpayment.bkpm_linkref WHERE CURRENT OF qc_bkrec END IF CLOSE qc_bkpayment END FOREACH END FUNCTION -----Original Message----- From: Langton.Tigere@icl.com [mailto:Langton.Tigere@icl.com] Sent: 30 November 2000 13:55 To: kagel@bloomberg.net Cc: informix-list@iiug.org Subject: simple 4gl programme I have done the following to copy from table bkpayment the value in its column bkpm_linkref to a table bkrec in the column bkr_linkref but I am getting an error " 391: Cannot insert a null into column (bkrec.bkr_linkref). ". I then looked if there are any null values in the source table (bkpaymnt) and found that there were no rows found , Please help urgently. The SQL statement I used is as follows Update bkrec set bkr_linkref = ( select bkpaymnt.bkpm_linkref from bkpaymn where bkr_batchref = bkpm_linkref and bkr_currvalue = bkpm_currvalue * -1 and bkr_proof = bkpm_proof and bkpm_linkref is not NULL ) where bkr_status = "U" and bkr_linkref = " " nB) Please note that the source table has more columns that the destination table -----Original Message----- From: ART KAGEL, BLOOMBERG/ NEW YORK [mailto:KAGEL@bloomberg.net] Sent: Wednesday, November 29, 2000 3:02 PM To: Simbarashe.Pangeti@icl.com Subject: Re: simple 4gl programme UPDATE table_1 SET course = ( select t2.course from table_2 t2 where t2.name = table_1.name and t2.regno = table_1.regno );Include the UNIQUE keyword or an aggregate (ie MAX, MIN, etc.) if there may be more than one matching row in table_2.