CODEWITHSUNDEEP
sqlibm-midrangerpgle
Jeff
Jeff

Reputation: 349

Strange Embedded SQL situation

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.

Upvotes: 1

Views: 142

Answers (2)

user2338816
user2338816

Reputation: 2163

This code works:

 D mycnt           s             10i 0 inz( 0 )
 D myName          s             10a   inz( 'ACCP' )
  /free
   *inlr = *on ;

   EXEC SQL  Select count(*) into :mycnt
                 from ACCPTH where APFILE in(:myName) ;

   if SQLCODE = +100 and SQLSTATE = '02000' ;
      dsply 'None 1' ;
   endif ;
   if SQLERRD(3) > 0 ;
      dsply 'None 2' ;
   endif ;
   if mycnt > 0 ;
      dsply 'Found some' ;
   endif ;

   return ;
  /end-free

The ACCPTH test file was created by running DSPFD TYPE(*ACCPTH) against a library that had a file named 'ACCP' already in it. Debug showed that mycnt had the correct count at the point where the IF-statement tested it.

If the EXEC SQL and IF-tests portion is duplicated and a second myName2 variable is added for the additional code block, it continues to work correctly (for me). The myName2 variable would have a second file name (or a name that doesn't exist; it doesn't matter).

Additional details about your code are needed in order to guess why it's not working for you.

Upvotes: 0

WarrenT
WarrenT

Reputation: 4542

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.

Upvotes: 3

Related Questions