Answer / subhash

In DataStage:
SRC--->CPY---->JOIN----TFM---TGT
--------|---- /
--------|--- /
--------|-- /
--------|- /
--------AGG
In AGG, GROUP BY EmpId, calculate MIN and MAX for each
EmpId.
JOIN both one copy from CPY and 2nd Aggrigated copy from
AGG.
In TFM, put constraint: IF MIN=MAX, then populate to TGT
then u will get required output.

Answer / 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.

Answer / akila ramu

DB--->Transformer--->Output File

Sample data propagation through these stages:
In table->DB stage--->Tfm----->outputfile
101 0---->100 0 2 2-->100 0
100 0---->101 0 2 1-->100 0
101 1---->101 1 2 1
100 0

DB: Use the bvelow query in this stage
select emp_id, ind, count(emp_id) c1, count(emp_id ind) c2
from table_name
group by emp_id, ind
order by emp_id, ind
So similar empid-ind are grouped and the count of each
empid-ind pair is also sent in a seperate column c2. The
count of each emp_id is sent in c1.

Tfm: Output link Contraint:c1=c2
Looping contraint: @ITERATION<=c2
Looping variables: l_empid=emp_id
l_ind=ind

Pass these two looping variables as the emp_id and the ind
to the output file.

Answer / 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.

Answer / mahalakshmi

SRC--SRT---FLT---|
| LKP
CPY---| |
| |
|__FNL_|
|
TGT
1.In Sort Stage (Keys on Empid & Emp Ind), Create Key
changeColumn = True
2. In Filter WhereClause-(Keychange=0)OutputLink=0
WhereClause-(Keychange=1)OutputLink=1
3.In Lookup InnerJoin on EmpId
4.In Funnel, Funnel Type=Sorted Funnel

Answer / shar

Ok
1) sql:
select empno, rank() over(order by empno) Ind from e2;
then we get:
100 1
100 1
100 1
101 2
101 2
103 3

2) Datastage:
sequl file-->sort-->tx--> Target.
@sort:
create keychange column then we get
100 0
100 1
100 1
101 0
101 1
101 1
102 0
102 1
103 0
@ Tx use two stage variables:
sv1 integer = 0 value
sv2 integer = 0 value
and at derivations :
assign
1) keychange ---> sv1
2) if sv1=0 then sv2+1 else sv2 ----> sv2

map the columns empno and sv2
and we get the results.
Thats it.
shar

how to add a new records into source?

0 Answers  

What is the method of removing duplicates, without the remove duplicate stage?

0 Answers  

CAN ANY ONE TELL ME THE ARCHITECTURE OF DATASTAGE CLEARLY....

2 Answers   Wipro,

hi.... am facing typical problem in every interview " I need some critical scenarios faced in real time" plz help me guys

0 Answers  

what is the main difference between sorragate key n primary key in one word

2 Answers  

Out of 4 mill records only 3 mill records are loaded to target and then job aborted. How to load only those 1 mill(not loaded records) for next run. This job is not sequential job, it is stand alone parallel job.What are the possibilities available in datastage8.1?

7 Answers   IBM,

on how many columns we can perform aggregation in the Aggregator stage?

3 Answers   Reliance,

1)What is configuration your file structure 2)I have two databases both are Oracle while loading data from source to target the job takes 30 min but I want to load less time how?

1 Answers   Hexaware,

there are indexes on a table as index1 with col1, col2 index2 with col2 index3 with col1,col2,col3. if i run a query with col1='100' which index will be used and why

1 Answers   Virtusa,

What is a ds designer?

0 Answers  

Unix Qn asked in datastage interview: I have diff type(.txt, .tmp, .bat etc) of file in 4 diff directories, I want move all '.txt' file from 4 directories to other folder. And need to delete all the files except which are created TODAY?

2 Answers   TCS,

Differentiate between data file and descriptor file?

0 Answers