DSXchange

Hi,

Iam new to datastage. Iam having a problem with db2/udb enterprise stage . I have sql statement like this for lookup

Select MANDT,EE,VARCOND,SWO......From.... WHERE ..... . I want to modify the Sql statement by adding distinct. When Iam Trying to add this distinct in output page .... general tab .....query type(Enter custom sql statement) Iam getting an error and I am not able to add distinct with option Generate Select Clause from Column list;enter other sql.
In query types which option I have to select to modify my query .


Thanks,
Somu

Try user-defined SQL

If this is the DB2/UDB Enterprise stage then you're talking about a parallel job. Is it a parallel job? Techniques are different in parallel and server jobs; you have posted in the server forum, yet mentioned the DB2/UDB Enterprise stage.

Hi,

It's a server job .Its a DB2/UDB stage. Which option I have to select in this four option to make distinct work


Enter Custom SQL statement

Use SQL Builder tool

LOad SQL from a file at run time

Generate SELECT clause from column list;enter other clauses




Thanks,
somu

Custom SQL means that you write the SQL. It must have exactly the columns in its SELECT clause as you have columns in your Columns grid in the stage. Apart from that restriction it can be any SQL statement that is legal for the database and which returns rows containing those columns.

If you for some reason don't want to use custom sql you can in this case still use "generate select clause from column list".

In the derivation field in the DB2 plugin you will have to enter 'distinct ColName' - do check the sql-tab to see that the generated sql looks fine.

Regards
Peter

peterbaun wrote:In the derivation field in the DB2 plugin you will have to enter 'distinct ColName'

Exactly.

Specifically, insert the keyword 'distinct' before the derivation of the first column. Don't use custom sql for something simple like that.