Rather than using a CASE in the where clause, I typically use an IN predicate like so:
where :host-variable1 in ('', column1) or
:host-variable2 in (0, column2)
It is a little more concise, but when the user screen has complex filtering options that require different joins or sort orders, then dyanamic sql is the only way to go.
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
-----"Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx> wrote: -----
To: "'RPG programming on the IBM i \(AS/400 and iSeries\)'" <rpg400-l@xxxxxxxxxxxx>
From: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
Date: 10/01/2015 01:02AM
Subject: AW: Syntax for Insert into using an rpg data structure, 1 row
This is the only way to do some things like filtering a result set based
on user input in multiple fields or selecting from different tables based on
some varying criteria
... and even this can be done with static SQL!
Declare MyCursor Cursor For
Select ...
From ...
Where Column1 = Case when :HostInput1 <> 0 then :HostInput1 Else
Column1 End
And Column2 = Case When :HostInput <> '' then :HostInput2 Else
Column2 End
And Column3 > '2000-01-01' Then :HostInput3 else Column3 End
....
Order By Case When :Sort = 1 Then Column1 End, Case When :Sort = 1 Then
Column4 End
Case When :Sort = 2 Then Column2 End,
Case When :Sort = 3 Then Column3 End, Case When :Sort = 3
Then Column1
.....
The only situation where you really need dynamic SQL is when the
table/file/schema to be accessed is not known at compile time.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Mark
Murphy/STAR BASE Consulting Inc.
Gesendet: Wednesday, 30.9 2015 20:43
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: Syntax for Insert into using an rpg data structure, 1 row
There are a two flavors of embedded SQL in RPG. Static and Dynamic. In
Dynamic SQL you build the statement preferably with parameter markers, and
the prepare and execute your statement (or you may be able to do that in one
step using execute immediate). This is the only way to do some things like
filtering a result set based on user input in multiple fields or selecting
from different tables based on some varying criteria. Normally you will be
able to use Static SQL. That is where you use host variables directly in the
sql statement, and just execute it with exec sql as Darren suggested.
If your dynamic SQL looks like:
stmt = 'insert ...';
exec sql prepare s1 from :stmt;
exec sql execute s1 using :host-structure;
You should probably consider using static SQL like:
exec sql insert ...;
If you need a complex build for your sql statement:
exec sql declare s1 statement;
exec sql declare c1 cursor for s1;
stmt = 'select field1, field2, ... from table'
if someCondition;
stmt += ' where field3 = ?';
else;
stmt += ' where field4 = ?';
endif;
exec sql prepare s1 from :stmt;
exec sql open c1 using :host-structure;
Then you will likely need dynamic SQL unless you can adjust the statement so
that it can all be written in a single string. In addition, some SQL
statements, that contain neither variables nor parameter markers, can be
prepared and executed in a single step with:
exec sql execute immediate :stmt;
But be sure you are not concatenating user input into a statement that is
prepared in this manner (without parameter markers) as that is a vector for
SQL injection. Actually this caution applies to execute immediate, and
prepare and execute equally, though execute immediate sort of encourages
this type of behavior.
Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx
-----JRusling@xxxxxxxxxxx wrote: -----
To: "RPG programming on the IBM i \(AS/400 and iSeries\)"
<rpg400-l@xxxxxxxxxxxx>
From: JRusling@xxxxxxxxxxx
Date: 09/30/2015 02:06PM
Subject: Re: Syntax for Insert into using an rpg data structure, 1 row
Oh, i like that Darren.
Dont have to count question marks, as i had to. (below)
I did get this to work like this - the ?'s on the insert had to match the
number of fields in the data structure.
data struc -
myds ds
...field1
...field2 . . ... etc
the insert -
clear sql2;
sql2 = 'insert into qtemp/rpttossf ' +
'values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) with nc'; //ec sql insert
into qtemp/rpttossf using :myds; exec sql prepare stmt2 from :sql2; exec sql
execute stmt2 using :myds;
John Rusling
Senior Developer/Analyst
Quadion LLC
Minnesota Rubber & Plastics
952.927.1419
<br />
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by anyone else
is unauthorized. If you are not the intended recipient, any disclosure,
copying, distribution or any action taken or omitted to be taken in reliance
on it, is prohibited and may be unlawful.
--
This is the RPG programming on the IBM i (AS/400 and 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 IBM i (AS/400 and 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.
midrange.com
