AW: Building an Embedded SQL Statement based on a prompt screenusing a variable SELECT

Hallo Tommy,

colon (:) describes an host variable, that cannot be user in an text string.

@th: a character variable must be embedded in quotes.
A single quote must be specified as two quotes.

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

Birgitta

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Holden Tommy
Gesendet: Montag, 16. Mai 2005 19:29
An: RPG programming on the AS400 / iSeries
Betreff: RE: Building an Embedded SQL Statement based on a prompt
screenusing 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.


--
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.