Dynamic SQL multiple field selection in SQLRPGLE

360 pts.
Tags:
#AS400 #RPGLE #SQLRPGLE #DynamicSQL
I am writing a dynamic SQL query to select 4 fields from a database. Hence, I have coded my DS/subfield definitions as: 

D DSNAME DS 
D Field1 10 
D Field2 10 
D Field3 10 
D Field4 10 

And my DECLARE/FETCH cursor statement goes like: 

Declare C0 cursor for select field1, field2, field3, field4 from table 
Fetch C0 into :DSNAME

At the end of execution, I have the 4 fields selected properly from my table, which is fine so far.

However, my requirement now is to select 100 fields from table. As you can see, I cannot define 100 subfields manually in the DS. 

Can anyone tell me how the DS should be coded in this scenario? (Just to add, i am not doing multi-row search, just multi-field select from table)


Software/Hardware used:
AS400
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 7  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.
  • ToddN2000
    Not know the rest of the code and what you are trying to do for the project and it's end result it's hard to recommend something. There may be better methods than the one you are trying to do. Like using XMLINTO Provide more details on you project and we will be glad to help. 
    132,760 pointsBadges:
    report
  • sri8707
    Hi Todd,

    Thanks for getting back. Unfortunately XML clause wouldn't work here as we are interacting with user differently. The criteria is to process a dynamic query stored in a file.  

    Say a database file contains below SQL query-

    SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM CUSTOMER WHERE CUSTOMERID = 100

    Below is the glimpse of program code:

    D DSNAME           DS                    
    D  Val01                       500      
    D  Val02                       500      
    D  Val03                       500      
    D  Val04                       500      

    Declare C0 Cursor For <query>
    OPEN C0
    FETCH C0 INTO :DSNAME
    CLOSE C0

    The moment FETCH statement is completed, my data structure DSNAME and its 4 subfields hold the values fetched from the query.

    Now, my doubt is, is there a way to do this without specifying all the subfields in the data structure? (something like DIM? ) As you can see, for 4 fields, i can specify subfields. When fields are more, is there a better way of defining the data structure?
    360 pointsBadges:
    report
  • ToddN2000
    Tricky project. Let's see if I understand it.
    Am I right in assuming you have a database with multiple SQL statements? If so how are they selected for processing?
    Once a SQL is selected, I assume the fields selected will vary from SQL statement to statement returning a different number of fields, field types and different field lengths. 
    This would explain a DS definition like you have listed to handle any length field with a default of 500. This is fine as long as everything is alphanumeric and no numeric values are ever selected.
    Is there a problem defining a larger DS or are you looking to keep the code clean and neat? You could try doing a SELECT and INSERT into a work table with your 100+ fields. 
    This method may only be an issue if more than one user were to use the program at the same time. The last issue is, what are you  doing with the data after the DS is built? If the next SQL selected had say 25 fields, those 25 would need to be defined somewhere in order for the program to compile and be used for further processing.
    132,760 pointsBadges:
    report
  • TheRealRaven
    Why can't you declare the fields manually? They'll need to be declared somewhere. And why do you need a dynamic statement?

    You could create a SQL VIEW for the 100 columns. Then you might reference the VIEW for the DS. There really isn't a better way of doing it.

    Technically, you could make it fully dynamic to kind of bypass declaring DS subfields. But then you'd have to code the statements to pass all of the attributes for each column to SQL. That'd be far more than simply declaring the subfields.
    35,120 pointsBadges:
    report
  • sri8707
    Hi Todd, my fault, should have given you more information.

    Yes, a physical file holds all the SQL statements. And each statement may select different fields. Say, first SQL can select 100 fields and second SQL can select 10 fields. 100 is the maximum limit assumed. I have currently defined all the 100 fields (with length of 500) as subfields of DS, so that once FETCH statement runs, data gets populated into DS properly (into the 100 fields). But, as you can see, code looks lengthy. So, i was wondering if there is a way to define Array (or something of that kind) to reduce the code. I tried array but couldnt resolve the issue (Not sure if it is not supported in this case)

    And to add, once all the 100 fields are fetched from query, we have some additional processes to edit and build data to printers. 

    And note that, I can resolve this by defining an external file to hold 100 fields of length 500 each to do the processing. Again, my aim is to see if this can be made better
    360 pointsBadges:
    report
  • sri8707
    Hi TheRealRaven , thanks for your reply. It has to be dynamic SQL because of the requirements (Consider something like this - User selects SQL fields in java and WHERE clause could vary for each and every run. Hence, we store it as dynamic SQL and process at later point).

    Ya, if there is no better way of doing this, I will have to stick up with the current logic of either defining 100 subfields for datastructure or by defining external file holding 100 fields
    360 pointsBadges:
    report
  • ToddN2000
    @sri8707: Sounds like you may be trying to do too much in one application. From what you have mentioned it sounds like some of these SQL would be run in from batch at a later time.. Where are these selectable field coming from and does everyone have the same set of 100 or are the fields unique to each user? To me it sounds like you are looking for one global SQL solution for many  different users. I personally would look at doing this a different way of doing the SQL from the details you have provided. You could set rules for the SQL by department and have a few different base SQL's.  Even that can get tricky.  I'd look at using a web based interface that is running interactive SQL
    132,760 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.

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

Following

Share this item with your network: