RE: 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

I've used this approach in the past and have found that that they are /very poor/ performers when compared with a Dynamic SQL that just contains the statement required for the current filter.

So another reason to use dynamic SQL is when you have a number of different options for the filter and order by and want to create a better performing SQL statement.

The performance gain with Dynamic SQL may be negligible when the SQL statement is accessing a single file that has good indexing.

However, for SQL statements that contain a number of Joins and Order By multiple different fields from multiple files, I've seen huge gains from using Dynamic SQL.


Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.