Solved

iseries sql set statement failing

Posted on 2015-02-18
2
241 Views
Last Modified: 2015-02-18
I am in need of help.  I am having a struggle with the UPDATE set statement .  I have a table that contains a quantity field that is alpha character.  I have spaces in the field in some rows and I have alpha characters in other rows, last but not least I have numbers however these are stored as character.

What I need to do is convert the quantity field to numeric if populated with a number, populate with a number 1 if the field contains spaces and populate the column with 1 if there alpha characters.

This is my code so far:

drop table  qtemp.Bom_Lines ;
create table qtemp.Bom_Lines as
(
Select
psco, 
pspmrn,
pscmrn,
psseq,
max(case when PSEFMO Between 1 and 12 then ( (1000000 * PSEFCT + 19000000)  + ( PSEFYR * 10000 ) + ( PSEFMO * 100 ) + PSEFDA )  else 19000101 END)  as effective,     
max( CASE when PSEXMO between 1 and 12 then ( (1000000 * PSEXCT + 19000000)  + ( PSEXYR * 10000 ) + ( PSEXMO * 100 ) + PSEXDA ) else 20451231  END )   as Discontinue,  
psqty,
cast(0 as decimal( 32,16))  as Qty_Per
 from kbm400mfg.fkpstruc 
where  ( (1000000 * PSEXCT + 19000000)  + ( PSEXYR * 10000 ) + ( PSEXMO * 100 ) + PSEXDA )  > 20150101 or ( (1000000 * PSEXCT + 19000000)  + ( PSEXYR * 10000 ) + ( PSEXMO * 100 ) + PSEXDA )  = 19791212
group by psco, pspmrn, pscmrn, psseq, psqty
order by psco, pspmrn, pscmrn, psseq, psqty

)with data;

select  * from qtemp.BOM_lines;

update qtemp.BOM_lines 
set qty_per = 
case when psqty  =  ' '   then 1 end;

update qtemp.BOM_lines 
set qty_per = 
case when psqty  =  'J' then 1 end;

update qtemp.BOM_lines 
set qty_per = 
case when psqty  =  'I' then 1 end;

Open in new window


Here is the error thrown when attempting the first update

> update qtemp.BOM_lines  set qty_per =  case when psqty  =  ' '   then 1 end

SQL State: 23502
Vendor Code: -407
Message: [SQL0407] Null values not allowed in column or variable QTY_PER. Cause . . . . . :   One of the following has occurred: -- Column QTY_PER is a target column in an UPDATE or INSERT statement for table BOM_LINES in QTEMP. 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 statement 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 QTY_PER is specified in an ALTER statement for table BOM_LINES in QTEMP.  The attribute of column QTY_PER can not be changed to NOT NULL because a null value exists in relative entry number 1 of the column. -- Variable QTY_PER 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.

Processing ended because the highlighted statement did not complete successfully

Open in new window


The same error occurs with any of the three update statements.
0
Comment
Question by:Leogal
2 Comments
 

Author Comment

by:Leogal
Comment Utility
this is my latest attempt to update

update qtemp.BOM_lines a
set qty_per = 
case when (select distinct psqty from qtemp.BOM_Lines b where a.psco= b.psco and a.pspmrn= b.pspmrn and a.pscmrn = b.pscmrn )  =  ' '   then   1 end;

Open in new window


I am still get an error:
QL State: 23502
Vendor Code: -407
Message: [SQL0407] Null values not allowed in column or variable QTY_PER. Cause . . . . . :   One of the following has occurred: -- Column QTY_PER is a target column in an UPDATE or INSERT statement for table BOM_LINES in QTEMP. 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 statement 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 QTY_PER is specified in an ALTER statement for table BOM_LINES in QTEMP.  The attribute of column QTY_PER can not be changed to NOT NULL because a null value exists in relative entry number 1 of the column. -- Variable QTY_PER 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.

Open in new window

0
 
LVL 18

Accepted Solution

by:
daveslash earned 500 total points
Comment Utility
I'm not fully clear on what you're trying to accomplish because your explanation doesn't at all match your SQL, but this should get you closer:

update qtemp.BOM_lines 
set qty_per = case 
                            when psqty  =  ' '   then 1 
                            when psqty  =  'J' then 1
                            when psqty  =  'I' then 1
                            else 0
                         end
;

Open in new window


HTH,
DaveSlash
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now