Multiple columns update

[prasad v]

Hi

I have a requirement to load data into database column based on the value coming from input field.

Eg:

Input:

EmpNo, DeptNo, Sal
1234, 10,1000
1234,20,2000
1234,30, 3000
1234,40,4000
2345,10,100
2345,20,200
2345,30,300

Output:

Emp No, Dept_10_Sal,Dept_20_Sal,Dept_30_Sal,Dept_40_Sal
1234, 1000, 2000,3000,4000
2345,100, 200, 300,0

Here we have more than 1000 columns like this Dept_10_Sal, Dept_20_Sal........Dept_1000_Sal.

Can any body advise on this how do we approach this result.

Thanks
Prasad

[prasad v]

Hi Chulett,

Unfortunately i have created this thread in General forum. Can you please move this thread to Parallel extender forum.

[samyamkrishna]

Use the trnasformer stage to do the vertical pivot.

Based on the key column.
If the value in the key column is same then append det_10_sal:',':Dept_20_sal and so till the key columns value changes.

[prasad v]

Use the trnasformer stage to do the vertical pivot.

Based on the key column.
If the value in the key column is same then append det_10_sal:',':Dept_20_sal and so till the key columns value changes.

Can you please explain a bit

Transformer processes the data row by row. how do we append this?

[samyamkrishna]

You take three stage variables.

stgvar1 = key column
stgvar2 = if stgvar1=stgvar3 then columnname:',':stgvar2 else ''
stgvar3 = stgvar1

now as long as the key columns are same it will keep appending.

make sure you sort the data on the key column and the transformer runs in a sequential mode.

[prasad v]

Thanks Sam,

You mean stage var2 will be mappped to the outputfield in transformer. then sort -->take the last row-->?How would we write this into seperate field in the database?

Also there may be no value for some of the fields like in the second key value doesn't have 40 deptno. where we should write zero.

[samyamkrishna]

yes the stgvar2 is mapped to the out put.
If you want to use for a value not present use another stgvar to verify that and put zero instead.

[prasad v]

Thanks sam,

But we will not be knowing which one is missing. Bit confusion on this. Not at all getting finally how do we update

[chulett]

Hi Chulett,

Unfortunately i have created this thread in General forum. Can you please move this thread to Parallel extender forum.

Done.

[pandeesh]

if your source and target is also a oracle database, then you can load into oracle table via WM_CONCAT or LISTAGG analytic functions which are oracle inbuilt.

[pandeesh]

Here we have more than 1000 columns like this Dept_10_Sal, Dept_20_Sal........Dept_1000_Sal.

I am curious to know which database you are referring?

In oracle we can't have more than 1000 columns.

[prasad v]

Thanks Pandeesh,

We are using Oracle database. I will check these functions.

And We have around thse columns in the table.

Thanks
Naga

[prasad v]

I tried in google for this fuction. but could not feel this suports my requirement. As i already mentioned, update should happen in the field based on the value of the column value. Column name is ends with value of column.

Code: Select all

SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Thanks
Naga

[Devendrudu]

Hi friends, this is will work by vertical pivoting.


|--------------------------------------filter
| | | | |
| | | | |
src------------->copy---------->removedupli------>lookup-----------trg



1. in copy stage take two o/ps

2.in remove duplicat based on key

3. in filter take 4 conditions
sal= deptno10
sal=deptno20
sal =deptno30
sal= deptno40

in o/p change col name deptno= deptno_10,

deptno= deptno_20,
like that u can give o/p and pass to target.

i think this will work i also done same type of scenario.
i am waiting for ur replay.