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

Hi All, Could you please let me know whether Datastage server and px supports oredb? OREDB:It's a Oracle Retail Embedded Database.Previousely It was called as Acumate data base. It's a multidimensional database. Please help me on this issue ASAP. Thanks in advance Ashok

1 Answers   Accenture,

Define ds designer?

0 Answers  

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,

What is exact difference between Parallel Jobs and server Jobs..

8 Answers   DELL, IBM,

How to read the length of word in unix?

0 Answers   CTS,

How the ipc stage work?

0 Answers  

How can one find bugs in job sequence?

0 Answers  

Hi guys, In sequencer job, we have 3 sources, in that 1st source have some records, Here requirement is 1st source records are 100 then only run the job otherwise total job will abort... How to calculate this. please design the job. Thanks.

1 Answers   IBM,

What are the steps needed to create a simple basic datastage job?

0 Answers  

What are system variables and sequencers in datastage

0 Answers  

1)what is the size of Fact table and dimension table? 2)how to find the size of Fact table and dimension table? 3)how to implement the surrogate key in transform stage? 4)write the configuration file path? 5)how many types of datasets explain? 6)diff b/w developed projects and migration projects? 7)how to delete the header and footer file of the sequencer file? 8)how can u call the parameters in DS in unix environment? 9) how much data ur getting daily ? 10)

0 Answers   Polaris,

eno ename esal acct1 acct2 amount1 amount2 100 suresh 10000 sbi1 sbi2 1000 2000 this is our sourse data i would loke to disply like this eno ename esal acct amount 100 suresh 10000 sbi1 1000 100 suresh 10000 sbi2 2000

2 Answers   HCL,