populating data??

[info_ds]

hi all,
i have 3 different oracle tables having same column names.
i would like to populate data from these tables to the target without any transformer stage.
how 2 do this?
any solutions

[kcbland]

First, your desire to not use a transformer stage is irrelevant, because DS Server will still create a transparent one anyway to do what you want, you're just losing a lot of functionality and ease in building your job design. Your requirement is a poorone.

If you're talking about OCI-->OCI job design, you're basically going to write a big SQL query in the first OCI stage.

The metadata for both stages are exactly the same, once you load column definitions in either stage it will be the column definitions in the other, because you have no transformer between this WILL be the case. Therefore, you MUST load the column definitions of the target table. All you can do at that point is write a USER-DEFINED SQL statement in the first OCI stage that returns the data into the column definitions of the target.

If you're not talking about OCI-->OCI job design, you need to clarify your requirements.

[sudharsanan]

Could you please give us more information on your design and tell us why you don't want to use Transformer. If you use Transformer stage you will have more visibility with the design and you can also resolve the data issues easily if any..

[ray.wurlod]

You can use the three lots of identical column definitions in an OCI stage, or a DRS, or any other stage that accesses the database. Here's how.

• Load qualified table definition for the first table. If the column names are not qualified in the Repository, edit the Derivations column and make them qualified (see also note below about table aliases).
  
  Edit the Column Name column and give every column a different name than the one it has in the derivation column (this is akin to using AS in SQL).
  
  Now load qualified table definition for the second table. Edit its column names as before.
  
  Then load qualified table definition for the third table. Edit its column names also.
  
  On the General tab, create a column-separated list of table names, just as you would in SQL, in the Table Name field. Optionally add table name aliases, again just as you would in SQL. If you do this, however, it must be the alias names that appear in the Derivations column on the Columns tab.
  
  On the Selection tab add any conditions required to specify the join paths between the tables.
  
  Inspect the SQL that DataStage has generated, and go back and amend anything necessary.

Eschew user-defined SQL wherever possible. For future reference, you can also edit the Derivations column to do anything that you might do in the SELECT clause, for example DISTINCT or COUNT() or MAX().