dynamic generation of rows

[harryhome]

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?

[ray.wurlod]

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.

Code: Select all

Source ----->  UV stage  ----->  Depts
                     |
                     +-------->  Platforms

The main trick is getting the metadata set up for the UV table. You will need a manual CREATE TABLE statement. Something like:

Code: Select all

CREATE TABLE MyStuff (
   ID INTEGER NOT NULL PRIMARY KEY,
   DEPT INTEGER NOT NULL MULTIVALUED,
   PLATFORM INTEGER NOT NULL MULTIVALUED,
   STATUS INTEGER );

[harryhome]

Thanks for reply.

I can modify the input data so as the delimiter within list is different.

So input data will look like this


ID|dept1,dept2,dept3|plat1,plat2|status

[harryhome]

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

[ray.wurlod]

That's exactly what the Pivot stage does.

[harryhome]

But here number of dept is dynamic and no upper limit is given . So plz tell me if we can pass parameters in pivot stage to do this.

[ray.wurlod]

You don't need to. You tell the Pivot stage what the internal delimiter is. It generates a sufficient number of rows. Read the Pivot.pdf manual.

[harryhome]

plaese tell me where extractly internal delimiter is in pivot stage.

i did go through pdf and stage but cant find it

[ray.wurlod]

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.