RE: SQL Problem

That doesn't work when they change the select.

Basically it comes in the first time as 'Select * from XYZ where fld1 =
'2' order by fld3

Then if they change the screen value it could be:
Select * from XYZ where fld2 = 'DD' order by fld4
Or: Select * from XYZ where fld3 = 'D' and fld6 = 99 order by fld2

The where clause changes and the order by clause changes.

It doesn't allow the second prepare. Do I just not use prepare?


Sharon Wintermute

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of jdavis@xxxxxxxx
Sent: Monday, October 19, 2009 2:38 PM
To: RPG programming on the IBM i / System i
Subject: Re: SQL Problem

I would use dynamic SQL and not static. You will build your statement
before executing it.

Something like this

Sql_Statment = 'Select * From XYZ';

If Cond1 <> Blanks;
Sql_Statement = %Trim(Sql_Statement) + 'where XXXX'
EndIf;

and so on and so on.

The use Exec sql prepare statement and everything else the same.


Jeff Davis




"Wintermute, Sharon" <Sharon.Wintermute@xxxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
10/19/2009 02:29 PM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
SQL Problem






I have a new situation I have not encountered before. I have a work with
display that can have up to 13 different position to values with 13
different sorts. Seems like the perfect candidate for sql.



All my other sql routines use a standard prepare, declare, open, fetch
close with one cursor. I thought if I closed the cursor I might be able
to re-open it with a different prepare but no luck. It doesn't allow
that. (Figures).



Basically I can use 1 to 13 different position-to fields and one sort at
a time.



So how do I go about this?





Sharon Wintermute