dynamic generation of rows
Moderators: chulett, rschirm, roy
dynamic generation of rows
Hi,
I will get input as follows
ID|deptlist|platformlist|status
Here deptlist will have dyanmic values as dept1|dept2|dept3|...
and platformlist will have dyanmic values as plat1|plat2|...
Now there are few tables where i want to insert only 1st dept or 1st plat
And there are a table for platform where i wann to insert all platfrom values for a record (if there are n platform values the there will be n entries in platform table)
also there are a table for department where i wann to insert all department values for a record (if there are n department values the there will be n entries in department table)
plz help, how to handle dynamic part?
I will get input as follows
ID|deptlist|platformlist|status
Here deptlist will have dyanmic values as dept1|dept2|dept3|...
and platformlist will have dyanmic values as plat1|plat2|...
Now there are few tables where i want to insert only 1st dept or 1st plat
And there are a table for platform where i wann to insert all platfrom values for a record (if there are n platform values the there will be n entries in platform table)
also there are a table for department where i wann to insert all department values for a record (if there are n department values the there will be n entries in department table)
plz help, how to handle dynamic part?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Is the same delimiter used within lists as well as between fields (lists)? If so you have a major parsing problem on your hands.
The easiest way would be to employ the multi-value handling capabilities of hashed files or UniVerse tables - write multi-valued fields to, say, a UV stage then read from the stage normalized on the multi-valued fields in question.
The main trick is getting the metadata set up for the UV table. You will need a manual CREATE TABLE statement. Something like:
The easiest way would be to employ the multi-value handling capabilities of hashed files or UniVerse tables - write multi-valued fields to, say, a UV stage then read from the stage normalized on the multi-valued fields in question.
Code: Select all
Source -----> UV stage -----> Depts
|
+--------> Platforms
Code: Select all
CREATE TABLE MyStuff (
ID INTEGER NOT NULL PRIMARY KEY,
DEPT INTEGER NOT NULL MULTIVALUED,
PLATFORM INTEGER NOT NULL MULTIVALUED,
STATUS INTEGER );
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
now is there any way , by which i can convert those dynamic part into that number of rows
e.g
i/p row
ID|dept1,dept2,dept3|status
o/p
ID|dept1|status
ID|dept2|status
ID|dept3|status
e.gi/p row
ID|dept1,dept2,dept3.dept4,dept5|status
o/p
ID|dept1|status
ID|dept2|status
ID|dept3|status
ID|dept4|status
ID|dept5|status
e.g
i/p row
ID|dept1,dept2,dept3|status
o/p
ID|dept1|status
ID|dept2|status
ID|dept3|status
e.gi/p row
ID|dept1,dept2,dept3.dept4,dept5|status
o/p
ID|dept1|status
ID|dept2|status
ID|dept3|status
ID|dept4|status
ID|dept5|status
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
My error. It doesn't work like that - you need to parse the values into separate columns for the Pivot stage to work. However, the technique outlined above for leveraging the "multi-valued field" technology will work - all you have to do is to change the internal delimiters to @VM before writing the record.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.