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
Announcement
Collapse
No announcement yet.
Array Data structure - Embedded SQL - Selecting multiple fields from a table
Collapse
X
-
Originally posted by sri8707 View PostI 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.
You could use an externally described file with 1,000 fields but that still requires you code - 1,000 fields.
Leave a comment:
-
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
This only works if all 100 fields are the exact same size, and are not a variable size field like a varchar.
Leave a comment:
-
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.
Leave a comment:
-
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.
Tags: None
Leave a comment: