Yes, you
can prepare the statement first.
This:
C/EXEC SQL DECLARE
MYCUR CURSOR FOR
C+ SELECT field1a , field2a
, field3a, field4a, field5a
C+
field5a, field1b, field2b, field3b, field4b
C+ FROM
FileA , FileB
C+ WHERE
C+
field1a = field1b AND
C+
field2a = field2b AND
C+
field3a = field3b
C/END-EXEC
Becomes this:
C
select
C
when
whatever
C
eval
@select = ‘SELECT field1a . . . ‘ etc.
C
when
a different condition
C
eval
@select = ‘SELECT field1a. . . ‘ etc
C
when
another different condition
C
eval
@select = ‘SELECT field1a . . . ‘ etc
C
endsl
C/exec sql
C+ prepare @select from :@select
C/end-exec
C/exec sql
C+ declare @cursor cursor for @select
C/end-exec
Hth,
Phil
-----Original
Message-----
From: owner-rpg400-l@midrange.com
[mailto:owner-rpg400-l@midrange.com]On Behalf
Of Refaie.Heba@khb.hu
Sent: Wednesday, June 06, 2001
9:17 AM
To: RPG400-L@midrange.com
Subject: embeded SQL question
Hi Group
I don't use
embeded SQL statement in my RPG program frequently, but I had to use them
this time due to the lack of logical file. what is the best way to do the
following
I have to filter the selected
records from two files fileA & fileB according to some conditions (coming
to me as parameter from the caller)
so I have the basic select statement
joing the two files
C/EXEC SQL DECLARE MYCUR CURSOR FOR
C+ SELECT field1a , field2a
, field3a, field4a, field5a
C+
field5a, field1b, field2b, field3b, field4b
C+ FROM
FileA , FileB
C+ WHERE
C+
field1a = field1b AND
C+
field2a = field2b AND
C+
field3a = field3b
C/END-EXEC
and I have to filter the records
according to input parameter in the following sequence
Parm1 if not eq *blanks
C/EXEC SQL DECLARE MYCUR CURSOR FOR
C+ SELECT field1a , field2a
, field3a, field4a, field5a
C+
field6a, field1b, field2b, field3b, field4b
C+ FROM
FileA , FileB
C+ WHERE
C+
field1a = field1b AND
C+
field2a = field2b AND
C+
field3a = field3b and field5a = : parm1
C/END-EXEC
Parm2 if it is not equal blanks
C/EXEC SQL DECLARE MYCUR CURSOR FOR
C+ SELECT field1a , field2a
, field3a, field4a, field5a
C+
field6a, field1b, field2b, field3b, field4b
C+ FROM
FileA , FileB
C+ WHERE
C+
field1a = field1b AND
C+
field2a = field2b AND
C+
field3a = field3b and field5a = : parm1 and
field6a = : parm2
C/END-EXEC
and so on for the rest of parameters
Is there a smart method that saves
me from writing the sql statement many times with different where conditions
Thanks in advance and sorry for the
long mail
Heba
midrange.com
