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.
- 6 Answers
- 10041 Views
- IBM, I also Faced
- E-Mail Answers
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
What is the difference between Datastage 7.5 and 7.0?
Define APT_CONFIG in Datastage?
What is merge stage?
What all the types of jobs you developed?
What are the steps needed to create a simple basic datastage job?
what is the use of skid in reporting?
Difference between in process and inter process?
How to convert RGB Value to Hexadecimal values in datastage?
What is use Array size in datastage
1)How will u implement SCD2 by using surrogate key. 2)What are the disadvantages with surrogate key. 3)How will you handle nulls in your project for the varchar, integer data types. 4)Can I use two fact tables in star schema. 5)3 jobs are running on the 2 nodes after I added one more node so can I compile those jobs to run on three nodes.
Define ds designer?
Can you explain kafka connector?
I have a few records just I want to store data in to targets cycling way how?
What is oci?
What are routines in datastage?
