Mark Haynes

 Profile Answers by Mark Haynes Questions by Mark Haynes

• Mar 2nd, 2012

 

I believe what you are looking for is a query like this:

select f3.deptname dept_name, sum(isnull(f2.salary,0)) amt_spent_each_dept
from file3 f3 left join file1 f1 on (f3.deptid = f1.deptid)
left join file2 f2 on (f1.empid = f2.empid)
group by f3.deptname
order by f3.deptname

vital_parlsey

 Profile Answers by vital_parlsey Questions by vital_parlsey

• Mar 3rd, 2012

 

Thanks for the reply...but i want to do this in ab initio .....could you be a bit more specific...
i will explain the question again..

Code
Hi,


 


I have 3 files  each having 2 cols


e.g


File1


empid       deptid


1                 100   


2                 200


3                 200


4                 100


5                 300


 


File2


empid       salary


1                 10  


2                  20


3                  30


4                  40


5                  50


6                  60


 


File3


deptid      deptname


100           A


200           B


300           C


400           D


 


in the output i want total amt spent on each dept i.e A,B,C,D which would be a sum in rollup for each dept(100,200,300,400) salary of each employee.


 


So here i will have to join 


File1.empid =File2.empid  (which gives salaries) AND


File1.deptid = File3.deptid (which gives dept name)


 


So... in o/p i want dept_name and amt_spent_each_dept


 


My solution has 


File1 ---


File2   ------[ JOIN] ---[Rollup] --- [File4 as output]


File3 ---


 


But what condition can i have in JOIN component to join these 3 tables ?


 

vital_parlsey

 Profile Answers by vital_parlsey Questions by vital_parlsey

• Mar 3rd, 2012

 

Setup the join component- Fine .. I will answer this myself You don't need to mention a key here ..neither key nor override key So in the transform you have find the columns u need to match and put a condition for them in each column. so u may get a cartesian product and after that check it with a filter by expression where you don't need null (check this with is_valid or is_null) and then fwd it to sort where key is dept_id and then to roll up where key is again dept_id and roll up on sal So here you get your result for each dept expense.

Malini

• Apr 3rd, 2012

 

Code
  SELECT C.DEPTNAME,sum(B.SALARY) FROM FILE1 A ,FILE2 B ,FILE3 C


  WHERE A.EMPLID=B.EMPLID AND A.DEPTID=C.DEPTID


  GROUP BY C.DEPTNAME

PLMS

• Mar 29th, 2013

 

Step 1: File1 - left outer Join {Key- empid} with -File2

Step 2: The above output flow should be sort{PBKS-if multifile} based on key empid

Step 3: The step 2s o/p flow to be left outer join {key: deptid} with -File 3

Step 4: In Rolloup-sum the the salary and map it to total_amt based on key deptid.

Step 5: FIle 4 will be ready now with required o/p (deptid,dept_name & total_amt)