Defining a sort variable (ORDER by) in RPG SQL

940 pts.
Tags:
iseries v5r4
RPG
RPGSQL
SQL
V5R4M0
I have a file that I would like to sort on a specific column that a user would choose. I sort the file by an imbedded SQL statement: ORDER by PRDNO. Works fine.

If I try and make it a variable (ORDER by :Sort_Seq), the statement is ignored. Is there a way to make the ORDER by a variable in RPG SQL?

Tks Phil



Software/Hardware used:
V5R4M0

Answer Wiki

Thanks. We'll let you know when a new response is added.

This requires a dynamic SQL
You build the select string as a char variable (SQLCMD) including pasting the name on the column in the right place in the string

Then

C/Exec Sql
C+ Prepare S1 FROM :SqlCmd
C/End-exec

C/Exec Sql
C+ Declare C1 Cursor For S1
C/End-exec

C/exec sql
C+ Open C1
C/end-exec
—————————–

Don’t know .. how long have you been under that rock?
Here’s an example from v5r2

http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzajp/rzajpmst146.htm

Remember dynamic SQL takes more cycles than static SQL

the other Phil

=================================================================
Thanks Phil (the other Phil)
It worked great

Phil
PS: Now all I have to do is underst and the code below.

=================================================================
I prefer this method
<pre>
d sort s 1a inz(’0′)
d fpitmmas e ds
/free
exec sql declare csr cursor for
select * from fpitmmas
order by case when :sort = ’1′
then itvndn else itmnum end;
exec sql open csr;
exec sql fetch csr into :fpitmmas;
exec sql close csr;
dump(a);
sort = ’1′;
exec sql open csr;
exec sql fetch csr into :fpitmmas;
exec sql close csr;
dump(a);
*inlr = *on;
/end-free
</pre>

BigKat
=================================================================
BigKat
that’s great — big improvement over my recomendation
Phil
*****************************************************************
Try this:
order by
case
when :inSortOption = ‘1′ then PRDNUM
when :inSortOption = ‘2′ then DEPT
when :inSortOption = ‘3′ then DIVISION
end,
case
when :inSortOption = ‘1′ then VENDOR
when :inSortOption = ‘2′ then VENDOR
when :inSortOption = ‘3′ then ITEMNBR
end
Note this uses Case to get the first field name
then a comma
then a second case to get the next field name.
Phil
****************************************************************
Thanks Phil, that does handle multiple sort fields.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • MurrayInfoSys
    EXCELLENT! Was I under a rock when that SQL staetment was made available? Anybody know when? Phil
    940 pointsBadges:
    report
  • Chance
    BigKat, Your solution looks good. 1. By doing the sql this way it is not considered dynamic? There is no need to "prepare"? 2. How can you specify more than 1 sort field? Such as: order by case when :inSortOption = '1' then PRDNUM, VENDOR when :inSortOption = '2' then DEPT, VENDOR when :inSortOption = '3' then DIVISION, ITEMNBR end When I try this, I get "Token , was not valid. Valid tokens: END." Thanks, Chance
    105 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following