I have an SQLRPGLE program that updates a pallet file to change the status field for all pallets in a given location EXCEPT for the (up to) 4 that the user has entered when prompted.
If the user enters 2-4 pallets it works like a charm, but every time the user only enters 1 pallet, it return an sqlcde of 100 (no records found for update.) I have debugged the code and actually copy and pasted (changing the variables) in the SQL shell and still it works fine outside the program but not inside.
In the code below, P1 is true when 1 pallet entered p2 when 2 and so on...
if (Error=*off);
strline=%char(XLINE);
select;
when p4=true;
exec sql
update plt set ptstat='0'
where ptloc=:strLINE and
ptplt not in (:xpal1, :xpal2, :xpal3, :Xpal4);
if sqlcod<>0;
msgnbr='SQL0001';
exsr MSG;
leave;
endif;
when p3=true;
exec sql
update plt set ptstat='0'
where ptloc=:strLINE and
ptplt not in (:xpal1, :xpal2, :xpal3);
if sqlcod<>0;
msgnbr='SQL0001';
exsr MSG;
leave;
endif;
when p2=true;
exec sql
update plt set ptstat='0'
where ptloc=:strLINE and
ptplt not in (:xpal1, :xpal2);
if sqlcod<>0;
msgnbr='SQL0001';
exsr MSG;
leave;
endif;
when p1=true;
exec sql
update plt set ptstat='0'
where ptloc=:strLINE and
ptplt not in (:xpal1);
if sqlcod<>0;
msgnbr='SQL0001';
exsr MSG;
leave;
endif;
other;
ENDSL;
Any ideas?
I forgot to mention....originally the code for 1 pallet was:
when p1=true;
exec sql
update plt set ptstat='0'
where ptloc=:strLINE and
ptplt<>:xpal1;
if sqlcod<>0;
msgnbr='SQL0001';
exsr MSG;
leave;
endif;
but that didn't work, so I changed it to the first example trying to keep it as similar to the working code as possible.
How about simplifying your code so that you dont need four different UPDATE statements?
If the user doesnt supply all four values, copy the first value into the other empty fields. Now you can say
ptplt not in (:xpal1, :xpal2, :xpal3, :Xpal4);
You should get correct results regardless of whether any of those for variables hold identical values.