Sql in RPGLE

Does anyone know how to use variable field in ORDER BY field in embedded sql in RPGLE to sort by specific order ? I don't want to hard code a field name.

Answer Wiki

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

The best way to do this is to create your SELECT statement in a program variable where you can substring in the field names you want for ORDER BY. Then use the PREPARE statement to tell the program in which variable your SELECT statement resides. After that you do your normal DECLARE cursor, OPEN cursor and FETCH statements.

The ISeries SQL reference manual will show you more detail on this.

Discuss This Question: 1  Reply

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.
  • JPLamontre
    look at http://jplamontre.free.fr/AS400/SQL2SYLK.htm, there is a SQL PREPARE inside (search for "C+ prepare k1prepa from : mySFW" note : SYLK is the text format of excel spreadsheets look at http://www.iseriesnetwork.com/artarchive/index.cfm?fuseaction=viewNewsletterArticle&webID=1001&newsID=5012&issueID=5176&articleID=50775, there is a complete sample here is a copy: RPG and SQL: The Dynamic Duo by Aaron Bartell Club Tech iSeries Programming Tips Contributor April 18, 2005 ? In the April 14th issue of the Club Tech iSeries Programming Tips newsletter, I introduced you to RPG and its ability to embed SQL. In this tip, I will expound on that first SELECT statement and show you the power of SQL?s ability to accept a dynamically built SELECT statement. I will show you how to pass parameters that tell the SQL statement to sort a customer credit code list by any field in the CUSTMST file. For review, here is the CUSTMST file structure: A R CUSTMST TEXT('CUSTOMER MASTER FILE') A NAM 30A TEXT('NAME') A ADR1 50A TEXT('ADDRESS1') A ADR2 50A TEXT('ADDRESS2') A CTY 50A TEXT('CITY') A STT 2A TEXT('STATE') A ZIP 5A TEXT('ZIP') A CTRY 50A TEXT('CTRY') A PHN 11A TEXT('PHN') A CRDCOD 1A TEXT('CREDIT CODE') Here is a rudimentary example of a credit code file that will be combined into the SQL result set along with the CUSTMST file: A R CRDCODMST TEXT('CREDIT CODE MASTER FILE') A CRDCOD 1A TEXT('CREDIT CODE') A DSCR 50A TEXT('DESCRIPTION') Like I stated above, the objective is to make the final result set dynamically sortable based on, for example, what a user clicks on. In addition to that requirement, it would be realistic for an end user to want a description of the credit code instead of a cryptic single character code. The following example is not tied to a user interface but instead allows you to call it from the command line as follows: CALL CUSTLIST2 ('cust.nam') Instead of using an *ENTRY PLIST to receive the parameter, this program uses a prototype. The code for the CUSTLIST2 program follows: h dftactgrp(*no) d sqlResult ds d custNam 30a d crdCodDscr 50a d CUSTLIST2 pr d ordby 10a d CUSTLIST2 pi d ordby 10a d sql s 256a /free sql = 'SELECT cust.nam, crd.dscr' + ' FROM custmst AS cust, crdcodmst AS crd' + ' WHERE cust.crdcod = crd.crdcod' + ' ORDER BY ' + ordby; /end-free C/exec sql C+ PREPARE S1 FROM :sql C/end-exec C/exec sql C+ DECLARE C1 CURSOR FOR S1 C/end-exec C/exec sql C+ OPEN C1 C/end-exec /free dou sqlcod 0; exsr fetchNxt; if sqlcod 0; iter; endif; dsply crdCodDscr; enddo; exsr closeCur; *inlr = *on; //---------------------------------------- // Get the next record from the result set //---------------------------------------- begsr fetchNxt; /end-free C/exec sql C+ FETCH NEXT FROM C1 INTO :sqlResult C/end-exec /free endsr; //---------------------------------------- // Close the SQL Cursor //---------------------------------------- begsr closeCur; /end-free C/exec sql C+ CLOSE C1 C/end-exec /free endsr; One thing that you will notice is different from last week?s example is that the sqlResult data structure has replaced the custDs data structure. CustDs was a mirror image of the CUSTMST file, but SqlResult is not. The reason for this can be found in the SELECT statement where it only asks for two fields ?- the customer name and the credit code description. There are four things that I want to point out in the SELECT statement above: a) "FROM custmst AS cust, crdcodmst AS crd" states that files custmst and crdcodmst are to be used in this SELECT statement and they are to have alias names of cust and crd, respectively. This allows you to make specific references to fields in the files without naming collisions. For example, the CRDCOD field from each file can be referred to independently as CRD.CRDCOD and CUST.CRDCOD. b) Look at the code "SELECT cust.name, crd.dscr". This code states that I want to select two specific fields rather than all fields. I only want the customer name and credit code description fields. Note that the dot is used to qualify which file and field is being referenced, much like using the QUALIFIED keyword on an RPG data structure. c) "WHERE cust.crdcod = crd.crdcod" states that there should be a join between files CUSTMST and CRDCODMST where the credit codes are equal to one another. There are other ways to "join" two tables together, but this is a simple way to show you a join for the first time. d) "'ORDER BY' + ordby" builds the string that will be used to order the sql result set. Note that ordby is passed into the program as a parameter and in this example will contain the value 'cust.nam'. The above article was written by Aaron Bartell. If you have any questions about this article, contact Aaron at aaronbartell@gmail.com.
    0 pointsBadges:

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.

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


Share this item with your network: