ISQL- I need help with query - error 391
THE PROBLEM INITIALLY APPEARS TO BE WITH NULLS IN THE DATA BUT THAT IS FALSE.
THE UNLOADED TABLE (see below) SHOWS NO NULLS IN DATA.
THE PROBLEM OCCURS WHEN I TRY TO PROCESS DIFFERENT ORDERS THAT HAVE COMMON PARTS,
IF ORDER 'A' USES PARTS 1, 2, & 3 - THE SCRIPT WILL PROCESS THE 3 RECORDS OK.
IF I ADD ORDER 'B' TO FOLLOW ORDER 'A' AND USE PARTS 1, 2, & 3 - ALL IS OK
NOW IF ORDER 'B' ONLY HAS PART 3; THE SCRIPT ERRORS WITH THE 391 ERROR MSG.
LETS SAY INSTEAD OF ORDER 'B' I HAVE ORDER 'T' FOLLOW ORDER 'A' IN THE TABLE,
ORDER 'A' HAS PARTS 1, 2, & 3 AGAIN BUT ORDER 'T' HAS PART 8 - ALL IS OK.
The following script should update 3 rows but instead returns a 391 error. Can
anyone explain this to me?
=================================
.
!
dbload -d ${M2_DB} -c ${M2_BASE_DIR}/fmdbin/requiretrn.cmd -l ${M2_BASE_DIR}/log/requiretrn.log
$INFORMIXDIR/bin/dbaccess ${M2_DB} <<!
begin work;
lock table require in exclusive mode;
set explain on; update require
set (req_qty, issued_qty, scrapped_qty) =
((select req_qty, issued_qty, scrapped_qty
from requiretrn
where require.ord_num = requiretrn.ord_num
and
require.part_num = requiretrn.part_num))
where ord_num in
(select ord_num from requiretrn)
and part_num in
(select part_num from requiretrn);
commit work;
!
The following rows were loaded by the dbload in the above script:
======================
203190*NA000910|115002|1|1|0|
203190*NA000910|115003|1|1|0|
203190*NA000911|115003|1|1|0|
this is results of the script using above data
==============================================
3 row(s) deleted.
DBLOAD Load Utility INFORMIX-SQL Version 5.00.UC6
Copyright (C) Informix Software, Inc., 1984-1991
Table requiretrn had 3 row(s) loaded into it.
Started transaction.
Table locked.
Explain set.
391: Cannot insert a null into column (require.req_qty).
110: ISAM error: end or beginning of the file.
Error in line 11Near character position 47
Data committed.
This is info for the require table:
==========================================
Column name Type Nulls
ord_num char(15) no
part_num char(25) no
oper_id char(6) no
chng_pend char(1) yes
chng_status char(2) yes
auth_doc_num char(8) yes
req_qty integer no
rel_qty integer no
issued_qty integer no
scrapped_qty integer no
comp_typ char(1) yes
issue_cntl_cd char(1) yes
qty_typ char(1) yes
wc_id char(9) yes
Index name Owner Type Cluster Columns
require_k_idx01 fmd unique No ord_num
oper_id
part_num
chng_pend
chng_status
require_part_idx fmd unique No part_num
ord_num
oper_id
chng_pend
require_upd_idx fmd unique No ord_num
part_num
This is info for the requiretrn table:
==============================================
Column name Type Nulls
ord_num char(15) no
part_num char(25) no
req_qty integer no
issued_qty integer no
scrapped_qty integer no
Index name Owner Type Cluster Columns
requiretrn_idx fmd unique No ord_num
part_num