Solved

iseries sql set statement failing

Posted on 2015-02-18
2
276 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
ID: 40617777
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
ID: 40617843
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

861 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