RE: Building an Embedded SQL Statement based on a prompt screen using a variable SELECT

c                   if        pname <> *blanks 
c                   eval      pname ='%'+%trim(pname)+'%' 
c                   eval      mainstatement = 
c                             'select * from wmmstr where ' + 
c                             'wmname like '   + 
c                             'trim('+%trim(:pname)+') ' + 
c                             'order by wmname' 
c                   endif 

Thanks,
Tommy Holden


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of PHarte@xxxxxxxxxx
Sent: Monday, May 16, 2005 10:09 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Building an Embedded SQL Statement based on a prompt screen
using a variable SELECT


Thanks for the previous replys guys your suggestions worked great. 

I have run into another problem....I have multiple fields on a prompt 
screen.  I have to build a SELECT statement in SQL/RPG to load my subfile 
based on the prompt screen selections, this could be name or address.

Here's how I build part my select statement which doesn't seem to work.

 * 
c                   if        pname <> *blanks 
c                   eval      pname ='%'+%trim(pname)+'%' 
c                   eval      mainstatement = 
c                             'select * from wmmstr where ' + 
c                             'wmname like '   + 
c                             'trim(:pname) ' + 
c                             'order by wmname' 
c                   endif 
 * 
 * Value of "mainstatement after eval statement.... 
 *  select * from wmmstr where wmname like trim(:pname) order by' 
 *   wmname                                                     ' 
 *                      
c/exec sql 
C+ DECLARE MAINCURSOR CURSOR FOR MAINSELECT 
c/end-exec 
c/exec sql 
C+ PREPARE MAINSELECT FROM :mainstatement 
c/end-exec 

Here's my FETCH statement which I use when loading the subfile

c/exec sql 
c+ fetch next from mainCursor into :main_ds 
c/end-exec 

Th
-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.