Splitting the rows
Moderators: chulett, rschirm, roy
Splitting the rows
Hi,
We have a table where the layout looks as below:
BRANCH JOBNO REGION DISTRIBUTOR SALESMAN1 SALESMANPCT1 SALESMAN2 SALESMANPCT2 SALESMAN3 SALESMANPCT3
We are supposed to fetch the records into a new table with the following layout:
BRANCH JOBNO REGION DISTRIBUTOR SALESMAN SALESMANPCT
We could fetch the records in to the new table using the UNION operator as follows:
SELECT BRANCH,
JOBNUMBER,
DISTRIBUTOR,
SALESMAN1,
SALESMANPCT
FROM SALESMAN1)
UNION
(SELECT BRANCH,
JOBNUMBER,
DISTRIBUTOR,
SALESMAN2,
SALESMANPCT2
FROM SALESMAN2)
UNION
(SELECT BRANCH,
JOBNUMBER,
DISTRIBUTOR,
SALESMAN3,
SALESMANPCT3
FROM SALESMAN3)
But i would like to do the same thing using datastage job as well. Please advise.
Thanks
Sue
We have a table where the layout looks as below:
BRANCH JOBNO REGION DISTRIBUTOR SALESMAN1 SALESMANPCT1 SALESMAN2 SALESMANPCT2 SALESMAN3 SALESMANPCT3
We are supposed to fetch the records into a new table with the following layout:
BRANCH JOBNO REGION DISTRIBUTOR SALESMAN SALESMANPCT
We could fetch the records in to the new table using the UNION operator as follows:
SELECT BRANCH,
JOBNUMBER,
DISTRIBUTOR,
SALESMAN1,
SALESMANPCT
FROM SALESMAN1)
UNION
(SELECT BRANCH,
JOBNUMBER,
DISTRIBUTOR,
SALESMAN2,
SALESMANPCT2
FROM SALESMAN2)
UNION
(SELECT BRANCH,
JOBNUMBER,
DISTRIBUTOR,
SALESMAN3,
SALESMANPCT3
FROM SALESMAN3)
But i would like to do the same thing using datastage job as well. Please advise.
Thanks
Sue
-
- 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: