• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

iseries sql set statement failing

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
Leogal
Asked:
Leogal
1 Solution
 
LeogalAuthor Commented:
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
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now