RE: Embedded SQL and Parameter Markers -- RPG400-L

Sure, you can use a case clause in the ORDER BY Clause.

Here an example:
Exec SQL Declare CsrC01 Cursor For
Select SalesYear, CustNo, CustName1, Amount
From SalesV03
Where SalesYear = Case When :ParYear > 2000
Then :ParYear
Else SalesYear End
and CustNo = Case When Trim(:ParCustNo)<>''
Then :ParCustNo
Else CustNo End
Order By Case When :ParSort in (1, 2)
Then SalesYear Else NULL End,
Case When :ParSort = 1
Then CustNo Else NULL End,
Case When :ParSort = 2
Then Amount Else NULL End Desc,
Case When :ParSort = 2
Then CustNo Else NULL End,
Case When :ParSort = 3
Then CustNo else NULL End,
Case When :ParSort = 3
Then SalesYear else NULL End
For Fetch Only;

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!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Donnerstag, 17. Februar 2022 15:18
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Embedded SQL and Parameter Markers

Hi Birgitta,

this is what I thought also - even if some „SQL-gurus“ think, that this kind of WHERE clause is a no-no.

Do you have any idea for doing the same with sorting?

We do dynamic (user-selectable) sorts, and it would be great to use static SQL instead of dynamic. But for sorting, I found no static solution so far.

Kind regards,
Daniel

Am 17.02.2022 um 15:08 schrieb Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>:

I'd use static SQL:

Declare Cursor ...
Select ...
From ...
Where Field1 = Case When :Filter1 <> '' Then :Filter1 else Field1
And Field2 = Case When :Filter2 <> 0 Then :Filter2 Else Field2
And Field3 = Case When :Fillter3 > '2000-01-01' Then
:Filter3 else Field3 ... and so on

If no value is entered in the filter, Field = Field is performed, i.e.
everything is returned.

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!"
„Train people well enough so they can leave, treat them well enough so
they don't want to.“ (Richard Branson)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Craig Richards
Sent: Donnerstag, 17. Februar 2022 10:15
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Embedded SQL and Parameter Markers

I'm building a few "Work With" screens with filters at the moment
Fairly typical maintenance programs where several of the columns shown
in a subfile have a "filter" field above them for refining the record selection.

Because the number of filters used is variable, I have traditionally
created dynamic SQL by building up a WHERE clause in a string and
adding in any of the filter fields which have been entered on the screen by the user.

I was wondering if there is a reasonable way to do this using
parameter markers.
Given that the number of filters entered is variable, so would the
list of parameter markers be.

To illustrate - I'm doing something like (simplified for brevity):

SqlStm = 'Select ... From ...';

If Filter1 <> *Blanks;
SqlWhere += 'and Field1 = Filter1'
EndIf;

If Filter2 <> *Blanks;
SqlWhere += 'and Field2 = Filter2'
EndIf;

If SqlWhere <> *Blanks;
SqlStm += SqlWhere;
EndIf;

etc.

In this simple example I could end up with:
- Select * from File
- Select *from File where Field1 = Value1
- Select *from File where Field2 = Value2
- Select *from File where Field1 = Value1 and Field2 = Value2

Is there a better way to build this dynamic sql?

Thanks in advance for any suggestions.
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com