RE: SQL Problem

I have programs that do the excact same thing as you are doing Sharon. The
Dynamic way works wonderful if it is coded correctly, and you will only
need to do it once. Then you will be able to handle all combinations of
the 13 through one section of code and one executable sql statment.

Jeff Davis






"Wintermute, Sharon" <Sharon.Wintermute@xxxxxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
10/19/2009 03:22 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
RE: SQL Problem






My problem is with all those different combos, it would take forever to
setup. Defeats the purpose. Must be some way to do it.

It appears I need an SQL Descriptor area. Should be interesting.



Sharon Wintermute

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jerry Adams
Sent: Monday, October 19, 2009 3:04 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL Problem

Sharon,

I don't know the answer, but I have a similar program (since put on the
back burners for the moment) that has three different select's. Two of
the three work, but the third one does not. I thought that closing the
cursor at the end would allow the PREPARE to again. Probably something
else I'm doing wrong since two of the variants work. Looking forward to
hearing the solution, if any, as I would hate to have to create separate
subprocedures for each variant (if that would work).

Jerry C. Adams
IBM System i Programmer/Analyst
--
B&W Wholesale
office: 615-995-7024
email: jerry@xxxxxxxxxxxxxxx


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Wintermute, Sharon
Sent: Monday, October 19, 2009 2:53 PM
To: RPG programming on the IBM i / System i
Subject: 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