Problem in modifying sql statement in db2/udb stage

somu_june · Post by **somu_june** » Thu Jan 12, 2006 10:34 am

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

kcbland · Post by **kcbland** » Thu Jan 12, 2006 1:47 pm

Try user-defined SQL

ray.wurlod · Post by **ray.wurlod** » Thu Jan 12, 2006 5:18 pm

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.

somu_june · Post by **somu_june** » Fri Jan 13, 2006 1:57 pm

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

ray.wurlod · Post by **ray.wurlod** » Fri Jan 13, 2006 3:28 pm

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.

peterbaun · Post by **peterbaun** » Tue Jan 17, 2006 3:57 am

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

chulett · Post by **chulett** » Tue Jan 17, 2006 8:34 am

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.