select Distinct
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 92
- Joined: Mon May 07, 2007 4:26 am
select Distinct
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
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
-
- Participant
- Posts: 92
- Joined: Mon May 07, 2007 4:26 am
-
- Participant
- Posts: 92
- Joined: Mon May 07, 2007 4:26 am
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.
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.
-
- Participant
- Posts: 92
- Joined: Mon May 07, 2007 4:26 am
Last edited by sureshchandra on Mon Mar 31, 2008 7:14 am, edited 1 time in total.
-
- Participant
- Posts: 92
- Joined: Mon May 07, 2007 4:26 am
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
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
-
- Participant
- Posts: 92
- Joined: Mon May 07, 2007 4:26 am
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.
![Confused :?](./images/smilies/icon_confused.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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.sureshchandra wrote: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
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
Hi Suresh,sureshchandra wrote: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
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
![Smile :)](./images/smilies/icon_smile.gif)
SMB
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune