select Distinct

[sureshchandra]

Hi all,
I need the output should be distinct.
For Eg:i'm getting date 2 or more than that but i need only one time.
How to distinct that?
There is any option in transformer


Thanks in advance

[ArndW]

You can use the Aggregator or, if your data is sorted, you can use stage variables in a transform stage to remove duplicates.

[sureshchandra]

Hi Andrew,
Thanks for ur response.
Can u help me how can i do it in transformer.
In stage variable what should i select?

[sureshchandra]

Hi Andrew,
Thanks for ur response.
Can u help me how can i do it in transformer.
In stage variable what should i select?

[ArndW]

1. Sort the input data by the column you need to have distinct. Let us assume it is "In.CustNo"
2. Create a stage variable svIsSameCustNo, derive it from "IF In.CustNo=svLastCustNo THEN 1 ELSE 0"
3. Create a stage variable svLastCustNo which is derived from "In.CustNo"
4. Create a constraint "NOT(svLastCustNo)" to only pass rows where the CustNo is not the same as the previous CustNo.

[sureshchandra]

[sureshchandra]

Hi ArndW,
I'm getting the out put like this
eg:29-3-2008
29-3-2008
29-3-2008
29-3-2008
30-3-2008
30-3-2008
30-3-2008
30-3-2008
30-3-2008
30-3-2008
31-3-2008
31-3-2008
31-3-2008
31-3-2008
I have to distinct into
29-3-2008
30-3-2008
31-3-2008
Pls help me how to do.I'm using ODBC------>Trans-------->Flatfile

[chulett]

Can you not simply do a distinct select in your source query?

[sureshchandra]

Hi Chulett,
Pls be clear.Any option is there to do distinct.Or i have to change the query to distinct.



Thanks in advance

[chulett]

Didn't realize I needed to be more clear, especially with your subject of 'select distinct'.

Normal sql supports the 'distinct' clause, any reason you can't change your sql so that it comes from your database into your job already 'distinct' - with unique values? The only reason you'd need to do it in the job is if you had to manipulate the data first, then make the transformed results unique - there you've got the Aggregator or Sort then Transformer to do the dirty work. Otherwise, push the work back onto the source database when possible.

[Minhajuddin]

Hi ArndW,
I'm getting the out put like this
eg:29-3-2008
29-3-2008
29-3-2008
29-3-2008
30-3-2008
30-3-2008
30-3-2008
30-3-2008
30-3-2008
30-3-2008
31-3-2008
31-3-2008
31-3-2008
31-3-2008
I have to distinct into
29-3-2008
30-3-2008
31-3-2008
Pls help me how to do.I'm using ODBC------>Trans-------->Flatfile

If this is the only field your input has, then you can do a distinct in your query and you'll get the expected output. But, I guess you have some more fields... People here can help you more if you give them an example of your input and the expected output.

[ray.wurlod]

If you are getting these dates from a database, simply add the word DISTINCT to the derivation of the first column.

[baglasumit21]

Hi ArndW,
I'm getting the out put like this
eg:29-3-2008
29-3-2008
29-3-2008
29-3-2008
30-3-2008
30-3-2008
30-3-2008
30-3-2008
30-3-2008
30-3-2008
31-3-2008
31-3-2008
31-3-2008
31-3-2008
I have to distinct into
29-3-2008
30-3-2008
31-3-2008
Pls help me how to do.I'm using ODBC------>Trans-------->Flatfile

Hi Suresh,
The simplest way you can achieve it is by writing the data to a intermediate hashed file. It will solve your problem. You can then read the data from the hash file which will be distinct

[ray.wurlod]

I disagree. The simplest way is to add DISTINCT to the extraction query, as suggested earlier.

[baglasumit21]

I disagree. The simplest way is to add DISTINCT to the extraction query, as suggested earlier. ...

Hi ray,
I too agree with you. but i think its the simplest way to remove duplicate records when you want to do something using datastage stages.