The sql query would be SELECT * FROM EMPTEST WHERE EMP_ID IN (SELECT EMP

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 / lb14447

6 Answers
12396 Views
IBM, I also Faced
E-Mail Answers

Answer Posted / lb14447

The sql query would be
SELECT * FROM EMPTEST WHERE EMP_ID IN (SELECT EMP_ID FROM EMPTEST GROUP BY EMP_ID HAVING SUM(EMP_IND)/COUNT(EMP_IND) = 0
OR SUM(EMP_IND)/COUNT(EMP_IND) = 1);

Datastage implementation:
SRC --> CPY ----> AGG---> FILTER
- |
- |
- |
- |
- |
--------> Look up ----> TGT
In the Aggregator stage calculate the Sum and Count fields.In the filter stage bypass the unwanted records using Sum and Count calculated in Aggr stage.

Is This Answer Correct ?

1 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

Difference between sequential file and data set?

1209

Can you explain repository tables in datastage?

1411

How to reverse the string using SQL?

1538

What are the main features of datastage?

1285

How can we improve the performance in datastage?

1107

How do y read Sequential file from job control?

15970

What are the types of containers and how to create them?

982

What are the functionalities of link partitioner?

1022

Differentiate between validated and Compiled in the Datastage?

1288

how to abort the job its matain duplicates?

2576

What are the components of datastage?

1123

What could be a data source system?

1023

What all the types of jobs you developed?

1181

how do u catch bad rows from OCI stage? And what CLI stands for?

2800

Can we use target hash file as a lookup ?

3327