Logic help
Moderators: chulett, rschirm, roy
Logic help
The source is oracle
id name St_Dt ed_Dt
-- --- ---- -----
1 a 10-jan-10 10-aug-11
1 a 11-jan-10 15-aug-11
2 b 11-jan-11 Null
2 b 12-feb-11 Null
O/p
--
id name St_Dt ed_Dt
-- --- ---- -----
1 a 10-jan-10 15-aug-11
2 b 12-feb-11 Null
For a particular id i need to fetch the record from source which is having maximum ed_dt,if it is null for all the records for particular key then i need to fetch the record which is having maximum St_Dt for that key
id name St_Dt ed_Dt
-- --- ---- -----
1 a 10-jan-10 10-aug-11
1 a 11-jan-10 15-aug-11
2 b 11-jan-11 Null
2 b 12-feb-11 Null
O/p
--
id name St_Dt ed_Dt
-- --- ---- -----
1 a 10-jan-10 15-aug-11
2 b 12-feb-11 Null
For a particular id i need to fetch the record from source which is having maximum ed_dt,if it is null for all the records for particular key then i need to fetch the record which is having maximum St_Dt for that key
Last edited by vamsi.4a6 on Tue Apr 03, 2012 3:51 am, edited 3 times in total.
I want implement through Datastage
1)extracted fields from source
2)In sort stage
Hashparition on id with both sorting and partition
ed_Dt with only sorting in desc
key column for stage is ed_Dt with desc
If it is null means then i need to fetch based upon St_Dt.How to achieve this logic through Datastage?
1)extracted fields from source
2)In sort stage
Hashparition on id with both sorting and partition
ed_Dt with only sorting in desc
key column for stage is ed_Dt with desc
If it is null means then i need to fetch based upon St_Dt.How to achieve this logic through Datastage?
Re: Logic help
I guess the Oracle tidbit is irrelevant.vamsi.4a6 wrote:The source is oracle
Not sure if this helps or not.... In sorting you can set the Nulls position so nulls will sort first or last.
Choose a job you love, and you will never have to work a day in your life. - Confucius
-
- Participant
- Posts: 91
- Joined: Wed Apr 20, 2005 7:59 pm
- Location: U.S.
Do this.
1. Read Oracle source.
2. Use Copy stage to split the rows in Two links as St_Dt and Ed_Dt.
3. Use Remove Duplicate Stage. Sort and Partition on ID and Sort only on St_Dt with Ascending and keep First Row.
4. Use Remove Duplicate Stage. Sort and Partition on ID and Sort only on Ed_Dt with Ascending and keep Last Row.
5. Use Join Stage to join the rows from St_Dt and Ed_Dt link.
You will get the desired result.
Hope this helps.
1. Read Oracle source.
2. Use Copy stage to split the rows in Two links as St_Dt and Ed_Dt.
3. Use Remove Duplicate Stage. Sort and Partition on ID and Sort only on St_Dt with Ascending and keep First Row.
4. Use Remove Duplicate Stage. Sort and Partition on ID and Sort only on Ed_Dt with Ascending and keep Last Row.
5. Use Join Stage to join the rows from St_Dt and Ed_Dt link.
You will get the desired result.
Hope this helps.
-
- Participant
- Posts: 251
- Joined: Mon Jun 09, 2008 5:52 am
seperate the data using filter stage. first link is having ed_date not null records and second link is having ed_date null records after that use remove duplicate stage(first link) sort desc based on ed_date and keep first and same do it for second link sort based on st_date then use funnel to combine the both the links.
hope this will help you.
hope this will help you.
Srinu Gadipudi
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Logic help
Your output doesn't match your rule, if you wanted the record the with max end date, you would havevamsi.4a6 wrote:The source is oracle
id name St_Dt ed_Dt
-- --- ---- -----
1 a 10-jan-10 10-aug-11
1 b 11-jan-10 15-aug-11
2 a 11-jan-11 Null
2 b 12-feb-11 Null
O/p
--
id name St_Dt ed_Dt
-- --- ---- -----
1 a 10-jan-10 15-aug-11
2 b 12-feb-11 Null
For a particular id i need to fetch the record from source which is having maximum ed_dt,if it is null then i need to fetch the record based on St_Dt
Code: Select all
1 b 11-jan-10 15-aug-11
Code: Select all
1 a 10-jan-10 15-aug-11
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Re: Logic help
Thanks for pointing the issue and please check the input and output.Now i hope it is correct.Can you please tell me know how to implement the above requirement
All you changed were the 'a' and 'b', so (to me anyway) that doesn't really change anything as I assumed they were wrong in the first place. However, you really haven't done anything to clarify your requirement.
"if it is null then i need to fetch the record based on St_Dt"
No idea what that means. We can see that for a 'normal' set you want the earliest start date with the latest end date, correct? However, when the end date is null it looks like you want the latest start date for that group, is that true?
"if it is null then i need to fetch the record based on St_Dt"
No idea what that means. We can see that for a 'normal' set you want the earliest start date with the latest end date, correct? However, when the end date is null it looks like you want the latest start date for that group, is that true?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
For a particulat key(id and name) i need to select the record which is having maximum start date and if it(start date) is null for all the records for that key i need to select based on end date
For better understanding i am explaining my requirement with two following examples
For ex-1
In this case St_Dt is not all for all the records for the key(1 and a)
id name St_Dt ed_Dt
-- --- ---- -----
1 a 10-jan-10 10-aug-11
1 a 11-jan-10 15-aug-11
1 a null 10-aug-11
o/p
--
id name St_Dt ed_Dt
-- --- ---- -----
1 a 11-jan-10 15-aug-11
For ex-2
id name St_Dt ed_Dt
-- --- ---- -----
2 b null 10-aug-11
2 b null 15-aug-11
2 b null 11-aug-11
here (start date) is null for all the records for the key ( 2 and b)so i need the fetch the record which is having maximum ed_dt
o/p
--
id name St_Dt ed_Dt
-- --- ---- -----
2 b null 15-aug-11
For better understanding i am explaining my requirement with two following examples
For ex-1
In this case St_Dt is not all for all the records for the key(1 and a)
id name St_Dt ed_Dt
-- --- ---- -----
1 a 10-jan-10 10-aug-11
1 a 11-jan-10 15-aug-11
1 a null 10-aug-11
o/p
--
id name St_Dt ed_Dt
-- --- ---- -----
1 a 11-jan-10 15-aug-11
For ex-2
id name St_Dt ed_Dt
-- --- ---- -----
2 b null 10-aug-11
2 b null 15-aug-11
2 b null 11-aug-11
here (start date) is null for all the records for the key ( 2 and b)so i need the fetch the record which is having maximum ed_dt
o/p
--
id name St_Dt ed_Dt
-- --- ---- -----
2 b null 15-aug-11
Suddenly, the direction & nullability of the dates have reversed.vamsi.4a6 wrote:For a particulat key(id and name) i need to select the record which is having maximum start date and if it(start date) is null for all the records for that key i need to select based on end date
Now we're just at an Aggregator with max() on each date.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers