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
12383 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

Name the different sorting methods in datastage.

1051

How we can covert server job to a parallel job?

1148

Does datastage support slowly changing dimensions ?

1106

AGGREGATOR default datatype

1461

What are data elements?

1177

Difference between data warehousing and olap?

1093

Differentiate between Join, Merge and Lookup stage?

1094

What can we do with datastage director?

1238

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

1169

What are routines in datastage? Enlist various types of routines.

1102

Why fact table is in normal form?

1252

Have you used Unstructured data?

1321

Can you explain players in datastage?

1199

Describe link sort?

1015

How can we improve performance of data stage jobs?

1017