ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Array Data structure - Embedded SQL - Selecting multiple fields from a table

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Array Data structure - Embedded SQL - Selecting multiple fields from a table

    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 to the defined data structure (which is as expected).

    However, my requirement now is to select 100 fields from table (I do not want to perform SELECT * FROM TABLE, rather I will be doing SELECT FIELD1, FIELD2, ......, FIELD100 FROM TABLE).
    As you can see, the complication is it would not be feasible to define 100 subfields in the DS.

    Can anyone tell me how the DS should be coded in this scenario? I tried DIM statement, however in vain.

  • #2
    How many columns are there in this thing? If you do a select * you can use an externally described DS based on the table to define the fields.

    SQL attempts to assign the first field selected to the first in the DS, the second to the second and so on. That pretty much rules out using an externally described DS in most cases.

    So if Select * is not a possibility then you are probably stuck with defining the fields by hand.

    Comment


    • #3
      I assume all the fields are the same size, and they are all type char, not varchar? Then perhaps something like this:
      Code:
      Dcl-Ds myFields qualified;
        allFields     char(1000);
        field         char(10) dim(100) pos(1); // This overlays allfields.
      End-Ds;
      
      exec sql declare Co cursor for
        select field1 || field2 || field 3 || ... || field100
          from table;
      exec sql open Co;
      exec sql fetch next from Co into :myFields.allFields; //Note I am selecting into the allFields subfield of the myFields DS, not into the DS itself
      Your SQL concatenates all 100 fields into a single string. 100 fields x 10 chars each makes it 1000 chars long. You select this into a single 1000 char variable. Using DS magic you can then overlay that field a 100 element array of char 10's to make each field a separate array entry. Then you can access field35, for instance, as array entry myFields.field(35)

      This only works if all 100 fields are the exact same size, and are not a variable size field like a varchar.

      Comment


      • #4
        Originally posted by sri8707 View Post
        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 to the defined data structure (which is as expected).

        However, my requirement now is to select 100 fields from table (I do not want to perform SELECT * FROM TABLE, rather I will be doing SELECT FIELD1, FIELD2, ......, FIELD100 FROM TABLE).
        As you can see, the complication is it would not be feasible to define 100 subfields in the DS.

        Can anyone tell me how the DS should be coded in this scenario? I tried DIM statement, however in vain.
        I don't get why it's not feasible to define 100 subfields in the DS. The only limitation is the total length and you're no where even remotely in the ballpartk.

        You could use an externally described file with 1,000 fields but that still requires you code - 1,000 fields.

        Comment


        • #5
          You could also do an "unpivot" (which converts columns into rows) to make each field return as a separate row. See here for explanations of pivot and unpivot SQL operations: https://www.ibm.com/developerworks/c...bles56?lang=en

          Unpovoting 100 columns would make for a large SQL statement however

          Comment

          Working...
          X