1. SQL: SELECT * FROM ( SELECT EmpId, COUNT(*) AS CNT1 FROM EMP GROUP

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.

Question Posted / subhash

6 Answers
12055 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

Please Help Members By Posting Answers For Below Questions

What are the features of datastage flow designer?

990

How do you register plug-ins?

919

Why do we use link partitioner and link collector in datastage?

1073

Is the value of staging variable stored temporarily or permanently?

929

What is ibm datastage flow designer?

1078

What is a merge?

1072

How can you write parallel routines in datastage PX?

973

What is a quality stage in datastage tool?

1021

What could be a data source system?

939

What are some different alternative commands associated with "dsjob"?

1079

Field,NVL,INDEX,REPLACE,TRANSLATE,COLESC

1365

Which algorithm you used for your hashfile?

1120

table actions available in oracle connector?

1553

How many types of stage?

1131

what is the use of surogate key in datastage

2246