Input Data is:
Emp_Id, EmpInd
100, 0
100, 0
100, 0
101, 1
101, 1
102, 0
102, 0
102, 1
103, 1
103, 1
I want Output
100, 0
100, 0
100, 0
101, 1
101, 1
Means Indicator should either all ZEROs or all ONEs per
EmpId.
Impliment this using SQL and DataStage both.
Answer Posted / subhash
1. SQL:
SELECT * FROM
( SELECT EmpId, COUNT(*) AS CNT1 FROM EMP GROUP BY
EmpId) E1,
( SELECT EmpId, COUNT(*) AS CNT2 FROM EMP GROUP BY
EmpId, EmpInd) E2,
WHERE E1.EmpID = E2.EmpId AND
E1.CNT1 = E2.CNT2;
2.DataStage:
SRC--->CPY---->JOIN----TFM---TGT
| /
| /
| /
| /
AGG
In AGG, GROUP BY EmpId, calculate CNT and SUM.
JOIN both one copy from CPY and 2nd Aggrigated copy from
AGG.
In TFM, put constraint: IF CNT=SUM, then populate to TGT
then u will get required output.
| Is This Answer Correct ? | 5 Yes | 2 No |
Post New Answer View All Answers
how to add a new records into source?
AGGREGATOR default datatype
What is the purpose of pivot stage and types of containers in datastage
Explain datastage architecture?
what is 'reconsideration error' and how can i respond to this error and how to debug this
How do u convert the columns to rows in datastage?
What is developer responsibilities in UAT (user acceptance testing and Post implementation phase?
What is meta stage?
What is the use of datastage designer?
In Datastage, how you can fix the truncated data error?
Hi All , in PX Job I have passed 4 Parameters and when i run the same job in sequence i dont want to use those parameters , is this possible if yes then how
Which commands are used to import and export the datastage jobs?
1)what is the size of Fact table and dimension table? 2)how to find the size of Fact table and dimension table? 3)how to implement the surrogate key in transform stage? 4)write the configuration file path? 5)how many types of datasets explain? 6)diff b/w developed projects and migration projects? 7)how to delete the header and footer file of the sequencer file? 8)how can u call the parameters in DS in unix environment? 9) how much data ur getting daily ? 10)
How many Key we can define in remove duplicate stage?
Difference between in process and inter process?