Hi all,
I want to perform the minus which we will do in oracle through datastage .Is there any stage which will perform this .
Thanks in advance
Ramesh .
how to implement the oracle minus keyword in data stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am
Re: how to implement the oracle minus keyword in data stage
HI ,
To implement MINUS (of oracle ) in datastage you can do in two ways:
1. Write the query containing MINUS in userdefined sql
2. Use a hash lookup making all the columns as keys (with a non unique contstraint) and reject if all the columns match.
Regards
Sreeni
To implement MINUS (of oracle ) in datastage you can do in two ways:
1. Write the query containing MINUS in userdefined sql
2. Use a hash lookup making all the columns as keys (with a non unique contstraint) and reject if all the columns match.
Regards
Sreeni
ramesh_c wrote:Hi all,
I want to perform the minus which we will do in oracle through datastage .Is there any stage which will perform this .
Thanks in advance
Ramesh .
-
- Participant
- Posts: 73
- Joined: Tue May 10, 2005 6:21 pm
- Location: Sydney
Re: how to implement the oracle minus keyword in data stage
Try the combination of Outer join and filter stages.
ramesh_c wrote:Hi all,
I want to perform the minus which we will do in oracle through datastage .Is there any stage which will perform this .
Thanks in advance
Ramesh .
Edward Yuan
-
- Participant
- Posts: 73
- Joined: Tue May 10, 2005 6:21 pm
- Location: Sydney
Thanks all ,
I tried by using change data capture stage .But it is not giving the exact resullt which is coming through query .
I think i am giving wrong keys &values in the properties. What my requirement is "the records which are coming in current months from the file minus the rexcords that are coming prior month".
The value column i kept is Quantity. And rest of all columns from the file i kept as keys. I cant able to find what is wrong in it .
Thanks,
Ramesh.
I tried by using change data capture stage .But it is not giving the exact resullt which is coming through query .
I think i am giving wrong keys &values in the properties. What my requirement is "the records which are coming in current months from the file minus the rexcords that are coming prior month".
The value column i kept is Quantity. And rest of all columns from the file i kept as keys. I cant able to find what is wrong in it .
Thanks,
Ramesh.
-
- Participant
- Posts: 73
- Joined: Tue May 10, 2005 6:21 pm
- Location: Sydney
I took back what I said in my previous post. All the columns have to be keys to get the exact result (in this case only keep the Deletes). You may also want to remove the duplicates after change data capture.
=============================================
Does the metadata matches between the before and after data streams? Only the records with change code "Delete" or "Edit" should be kept as the result of "before data MINUS after data".
By the way i guess you don't have to give so many keys.
=============================================
Does the metadata matches between the before and after data streams? Only the records with change code "Delete" or "Edit" should be kept as the result of "before data MINUS after data".
By the way i guess you don't have to give so many keys.
ramesh_c wrote:Thanks all ,
I tried by using change data capture stage .But it is not giving the exact resullt which is coming through query .
I think i am giving wrong keys &values in the properties. What my requirement is "the records which are coming in current months from the file minus the rexcords that are coming prior month".
The value column i kept is Quantity. And rest of all columns from the file i kept as keys. I cant able to find what is wrong in it .
Thanks,
Ramesh.
Edward Yuan