SQL Update with File Join

[MichaelDe]

I have two files that I have been working with, IMSTKPF (Primary) and QQRYOUT. IMSTKPF file has a field called Price I am attempting to do a Join to theses to files to pull the value from the field TOTAL2 in the QQRYOUT file and update the PRICE field in THE IMSTKPF file.

I can’t seem to make this work. I update files all the time using the same method I have just never had to update every row of a file using Join criteria. Any guidance and help would be appreciated

update qday.IMSTKPF as v1
set v1.PRICE =
      (select v2.TOTAL2
       from QDAY.QQRYOUT as v2
       where v2.ARCSTK# = v1.STKPRT)

 

[mikrom]

Your update statement should work if for each line from the 1.table IMSTKPF with a specific value of STKPRT, there is at most one line with the same value of ARCSTK# in the 2.table QQRYOUT.
If there are in the 2.Table more lines with the same value of ARCSTK#, the update will fail.

For example for these values your update will not work:

table IMSTKPF:
STKPRT  PRICE
foo  	100
bar  	200
baz  	300

table QQRYOUT:
ARCSTK# TOTAL2
foo  	500
bar  	600
baz  	700
foo  	800

You can look for duplicates of ARCSTK#

select v2.ARCSTK#, count(v2.ARCSTK#) 
from
  IMSTKPF as v1,
  QQRYOUT as v2
where v2.ARCSTK# = v1.STKPRT 
group by v2.ARCSTK#
having count(v2.ARCSTK#) > 1

 

[MichaelDe]

I can't seem to figure this one out. I have gone back to my original file and cleaned up some of the data.
I have attempted to run this SQL statement again and it is still giving me errors.

Error Message Received*****************************

[ 11/23/2021, 11:21:00 AM ]  Run Selected...  update ARCTEST.XXIMSTKPF as v1 set v1.PRICE = (select v2.TOTAL2 from QDAY.QQRYOUT as v2 where v2.ARCSTK# = STKPRT)  SQL State: 23502 Vendor Code: -407 Message: [SQL0407] Null values not allowed in column or variable PRICE. Cause . . . . . :   One of the following has occurred: -- Column PRICE is a target column in an UPDATE, INSERT, or MERGE statement for table XXIMSTKPF in ARCTEST. Either a null value was specified to be inserted or updated into this column or a value for the column was not specified in an INSERT and the column does not allow null values. The null value was specified in the relative entry number 1 in the VALUES list, select list, or SET clause. -- Column PRICE is specified in an ALTER statement for table XXIMSTKPF in ARCTEST.  The attribute of column PRICE can not be changed to NOT NULL because a null value exists in relative entry number 1 of the column. -- Variable PRICE is a target variable in an SQL procedure, function, or trigger.  A null value was specified to be set into this variable using a SET or VALUES statement, but the variable does not allow null values.  The null value was specified in relative entry number 1 in the SET or VALUES INTO clause. The null value was specified as either NULL, a host variable with an associated indicator variable that contains a negative value, a column containing a null value, or an expression that evaluated to NULL. If it is a host variable or column then the name is *N. The null value for a column may be disallowed by a CHECK constraint that was added implicitly to enforce the NOT NULL attribute of the column specified on the CREATE or ALTER of the column. The null value for a column may be disallowed by a CHECK constraint that was added implicitly to enforce the partitioning key attributes of the column specified for the base table on the CREATE TABLE or ALTER TABLE statement. Recovery  . . . :   If this is an ALTER TABLE statement, change the existing null values in the column to a non-null value. Otherwise, change the value so the result is not null.  If a host variable is specified, change the value in the related indicator variable to be greater than or equal to zero.  Try the request again.

 

[mikrom]

The error is caused by null values:

Message: [SQL0407] Null values not allowed in column or variable PRICE

...
Recovery . . . :
If this is an ALTER TABLE statement, change the existing null values in the column to a non-null value.
Otherwise, change the value so the result is not null.
If a host variable is specified, change the value in the related indicator variable to be greater than or equal to zero.

 

[mikrom]

I seems that your first table IMSTKPF has the column PRICE defined as NOT NULL,
but your second table QQRYOUT could have null in the column TOTAL2.

I tried to reproduce this error in this simple example:

create or replace table IMSTKPF (   
  STKPRT character(3), 
  PRICE  decimal(3, 0) not null default 0
)
;
 
insert into IMSTKPF values
   ('foo',  100),
   ('bar',  200),
   ('baz',  300)  
;

create or replace table QQRYOUT (   
  ARCSTK# character(3), 
  TOTAL2  decimal(3, 0)
)
;

insert into QQRYOUT values
   ('foo',  null),
   ('bar',  600),
   ('baz',  700)  
;

Then after I tried this update

update IMSTKPF as v1
set v1.PRICE =
      (select v2.TOTAL2
       from QQRYOUT as v2
       where v2.ARCSTK# = v1.STKPRT)
;

I got the same error as you

SQL State: 23502
Vendor Code: -407
Message: [SQL0407] Null values not allowed in column or variable PRICE.
Cause . . . . . :   One of the following has occurred: -- Column PRICE is a target column in an UPDATE, INSERT, or MERGE statement for table IMSTKPF ...
...
...
Recovery  . . . :   If this is an ALTER TABLE statement, change the existing null values in the column to a non-null value. Otherwise, change the value so the result is not null.  If a host variable is specified, change the value in the related indicator variable to be greater than or equal to zero.  Try the request again.

 

[MichaelDe]

Mikron I want to thank you for your help so far. I can say I've never had this much trouble updating a file using SQL on the iSeries.
TOTAL2 from QQRYOUT file is Zoned Data 10,4.

I have stripped out all null data. Some of the remaining questionable data is less than one i.e (.4659) but I have never had issues updating these kinds of values. After updating with these kinds of values in the past. I am working on developing my skills here but this one still has me stumped. Could the less than 1 value be causing this? I am not sure that this SQL handles null well when doing an update.

I am able to attach the qqryout data if you think it would be helpful.

update ARCTEST.xxIMSTKPF as v1
set v1.PRICE =
      (select v2.TOTAL2
       from QDAY.QQRYOUT as v2
       where v2.ARCSTK# = v1.STKPRT )

Error Code *******************
[ 11/23/2021, 02:49:04 PM ]  Run Selected...  update ARCTEST.xxIMSTKPF as v1 set v1.PRICE = (select v2.TOTAL2 from QDAY.QQRYOUT as v2 where v2.ARCSTK# = v1.STKPRT )  SQL State: 23502 Vendor Code: -407 Message: [SQL0407] Null values not allowed in column or variable PRICE. Cause . . . . . :   One of the following has occurred: -- Column PRICE is a target column in an UPDATE, INSERT, or MERGE statement for table XXIMSTKPF in ARCTEST. Either a null value was specified to be inserted or updated into this column or a value for the column was not specified in an INSERT and the column does not allow null values. The null value was specified in the relative entry number 1 in the VALUES list, select list, or SET clause. -- Column PRICE is specified in an ALTER statement for table XXIMSTKPF in ARCTEST.  The attribute of column PRICE can not be changed to NOT NULL because a null value exists in relative entry number 1 of the column. -- Variable PRICE is a target variable in an SQL procedure, function, or trigger.  A null value was specified to be set into this variable using a SET or VALUES statement, but the variable does not allow null values.  The null value was specified in relative entry number 1 in the SET or VALUES INTO clause. The null value was specified as either NULL, a host variable with an associated indicator variable that contains a negative value, a column containing a null value, or an expression that evaluated to NULL. If it is a host variable or column then the name is *N. The null value for a column may be disallowed by a CHECK constraint that was added implicitly to enforce the NOT NULL attribute of the column specified on the CREATE or ALTER of the column. The null value for a column may be disallowed by a CHECK constraint that was added implicitly to enforce the partitioning key attributes of the column specified for the base table on the CREATE TABLE or ALTER TABLE statement. Recovery  . . . :   If this is an ALTER TABLE statement, change the existing null values in the column to a non-null value. Otherwise, change the value so the result is not null.  If a host variable is specified, change the value in the related indicator variable to be greater than or equal to zero.  Try the request again

 

[mikrom]

Hi MichaelDe,

Have you tried using the COALESCE() function to fix the NULL-Values problem?
This update works for me in the example reproduced above successfully:

update IMSTKPF as v1
set v1.PRICE =
      (select coalesce(v2.TOTAL2, 0)
       from QQRYOUT as v2
       where v2.ARCSTK# = v1.STKPRT)
;

 

[mikrom]

Hi MichaelDe,
Does it solved your problem or not ?

 

[MichaelDe]

No sir,
It didn't work I have tried that function a while back.
I am still getting the same error message about NULL values.
The 4th column contains the data in question

Sample of my data

STK     7564  STK7564                     4.2280           14.52362          221.04480           35.02264
STK     7565  STK7565                     4.7700           13.30838          231.38853           31.96246
STK     7567  STK7567                     5.1425           13.71345          236.14131           32.98252
STK     7568  STK7568                     4.9657            8.46551          269.72365           19.75162
STK     7569  STK7569                      .0645             .08030            3.44780             .34370
STK     7570  STK7570                      .6005            8.46551            7.81156           19.75162

 

[fredericofonseca]

try this

update IMSTKPF as v1
set v1.PRICE = coalesce(
      (select v2.TOTAL2
       from QQRYOUT as v2
       where v2.ARCSTK# = v1.STKPRT)
      , v1.PRICE, 0) -- adding price in case it is populated and you wish to keep it

or (potentially what you really need)

update IMSTKPF as v1
set v1.PRICE = 
      (select v2.TOTAL2
       from QQRYOUT as v2
       where v2.ARCSTK# = v1.STKPRT)
where exists (select 1 
             from QQRYOUT as v2
             where v2.ARCSTK# = v1.STKPRT)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[MichaelDe]

Thank you very much fredericofonseca


Your reply solved my issues and I learned something new.


Much appreciated