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 perform incremental load in datastage?

0 Answers  

What r the existing server jobs in parallalism?

0 Answers   ITC Infotech,

how to achieve this output ? Two Input columns(ID & Name) - ID | Name 1 | Jack 1 | Kara In output there should be only 1 column which will be populated as - 1,Jack 1,Kara

0 Answers  

souce file having the columns like name company krish IBM pooja TCS nandini WIPRO krish IBM pooja TCS if first row will be repeat i want the result like this name company count krish IBM 1 pooja TCS 1 nandini WIPRO 1 krish IBM 2 pooja TCS 2

9 Answers  

how many types of sorting the data in data stage?

1 Answers   BoA, IBM,

What is the difference between validated and compiled in the datastage?

0 Answers  

How to write a left outer join condition by using Transformer stage in server jobs? Could any one help me?

1 Answers  

What are the components of datastage?

0 Answers  

1.which index is follows the dimensions tables?why? 2.what is the use of trigger in job sequence? 3.what is the mean of optimization? 4.what is the job control?when we use it? what is difference bet batch and sequencer? 6.seq--->seq,seq--->copy--->seq which one is best and efficient?

1 Answers  

what is the difference between lookup stage reject link and merge stage reject link in datastage Parallel jobs?

9 Answers   HCL,

why we use parameters instead of hard code in datastage

4 Answers   HCL,

How can we perform the 2nd time extraction of client database without accepting the data which is already loaded in first time extraction?

0 Answers   Reliance,