AutoCode not Working Correctly

[JosephHarriman]

Hello,

I created an autocode pgm using a SQL statement and the generation process duplicated the Where keyword. Please see code below. Thanks.

       stmt= 'Select distinct wprod, idesc, iums, wwhs, wopb, wadj, wrct, '+   
      'wiss From iwi join iim on wprod = iprod join codes on key4 = '+  
      'wwhs join mrbomdp a on wprod = a.bmitm# join mrbomhp b on '+     
      'a.bmpr# = b.bmpr# and a.bmver# = b.bmver# Where wid = ''WI'' '+  
      'and iid = ''IM'' and b.bmact = 0 and key1 = ''PLANT'' and key2 '+
      '= ''WHSES'' and key3 = ''OK''';                                  
search=vvIn_char('search':toUpper);                                     
if search <> *blanks;                                                   
  stmt+= ' where upper(WPROD) like '+SQ+search+'%'+SQ+' or'+            
               ' upper(IDESC) like '+SQ+search+'%'+SQ+' or'+            
               ' upper(IUMS) like '+SQ+search+'%'+SQ+' or'+             
               ' upper(WWHS) like '+SQ+search+'%'+SQ;                   
endif;

[richard.milone]

Does the app work correctly if you change the second "where" in the search logic to "and" in the RPG code and recompile?

[JosephHarriman]

Yes sort of, I do get back data. I just noticed, using the Test RPG Call, that it also needs a single parenthesis pair around the multiple or statements to work correctly.

[JosephHarriman]

I forgot to add that when I click on app to run, the little wheel just spins and that's it. No errors are generated and it does not time out.

[richard.milone]

I assume you're getting an MSGW on the RPG program. In that case you should have an entry in the Errors app that details the problem. Please get your SQL statement modified so that it's working the way you want and then post it up on this thread so we can see how we may want to modify AutoCode to handle this condition.

[JosephHarriman]

No sir. I do not see any errors. I let the program run for at least 15 minutes. I finally ended it myself. Here's my modified code. It does return data using the Test RPG Call.

stmt= 'Select distinct wprod, idesc, iums, wwhs, wopb, wadj, wrct, '+    
      'wiss From iwi join iim on wprod = iprod join codes on key4 = '+   
      'wwhs join mrbomdp a on wprod = a.bmitm# join mrbomhp b on '+      
      'a.bmpr# = b.bmpr# and a.bmver# = b.bmver# Where wid = ''WI'' '+   
      'and iid = ''IM'' and b.bmact = 0 and key1 = ''PLANT'' and key2 '+ 
      '= ''WHSES'' and key3 = ''OK''';                                   
search=vvIn_char('search':toUpper);                                      
if search <> *blanks;                                                    
  stmt+= ' and (upper(WPROD) like '+SQ+search+'%'+SQ+' or'+              
             ' upper(IDESC) like '+SQ+search+'%'+SQ+' or'+               
             ' upper(IUMS) like '+SQ+search+'%'+SQ+' or'+                
             ' upper(WWHS) like '+SQ+search+'%'+SQ+')';                  
endif;

[richard.milone]

If you run this same SQL command on the green screen how long does it take to complete?

[JosephHarriman]

About one second.