DSXchange

Posted: **Wed Jun 08, 2011 1:52 pm**

I need to convert the following PLSQL code into DS stages:

I have a bunch of following insert statements appending to the same table:

Code: Select all

      INSERT INTO summary (
              f1
      ,       f2
      )
      SELECT
              f_fixed
      ,       f_var
      FROM
              tbl1    a
      ,       tbl2    b
	  	  
      WHERE
              a.pk1     = b.pk1
      AND     a.pk2     = b.pk2
      GROUP BY
              f_fixed
      ,       f_var;

whereas each insert statement differ in tbl2 and f_var only.

The purpose of these insert statements is to summarize from different lookup table sources (6 or 7 of them), ie join tbl1 to those lookup tables (tbl2), then lookup the f_var field from tbl2.

Here is my plan:

Use a copy stage hash on 'f_fixed' on the input (tbl1); each output link will be used as source for a different lookup stage for all those lookup tables (tbl2). The output link should each further hashed on 'f_var'. All lookup results will be garther together with a funnel stage, on the key f_fixed.

That's the only things I can think of due to my limited knowledge to DS (some might be even wrong).

Will that work? Have I neglected anything?
Is that the best solution (provided that the lookup tables are relatively small)?
Any other comments/suggestions?

Thanks

Posted: **Fri Jun 10, 2011 6:36 am**

Looks like you need to do some aggregation or remove duplicates as well (group by)

DSXchange

Need suggestions on a specific task

Need suggestions on a specific task