CODEWITHSUNDEEP
sqloracle-sqldeveloperinformatica-powercenter
User24146692918
User24146692918

Reputation: 11

Add a total row at the bottom of the output in informatica target flat file

I was looking for a solution in informatica powercenter which could allow me to add a total row at the bottom of the output.I am currently using code in Oracle Sql format and in Sql developer application

Current output

StudentName History Math Science
John Doe.   10.     20.  30
John Watts. 30.     50.  20

Wanted output

StudentName History Math Science
John Doe.   10.     20.  30
John Watts. 30.     50.  20
Total.      40.     70.  50

Upvotes: 0

Views: 412

Answers (2)

Koushik Roy
Koushik Roy

Reputation: 7387

You can try this as well in informatica.

  1. add an aggregator to calculate count, total number of records.
  2. use UNION transformation to union it with main flow. and make sure to add this to a proper field and below the main data. Flow should look like this -
SQ ->... -> EXP ------>| 
             |         UNI -> TGT
             |-> AGG-->|

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

The simplest method is probably union all. If you really want the row last, then:

select tt.*
from ((select studentname, history, math, science
       from t
      ) union all
      (select 'Total', sum(history), sum(math), sum(science)
       from t
      ) 
     ) tt
order by (case when studentname = 'Total' then 1 else 2 end) desc,
         studentname

Upvotes: 0

Related Questions