Page 1 of 1
how to implement the oracle minus keyword in data stage
Posted: Tue Jul 17, 2007 6:09 am
by ramesh_c
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 .
Re: how to implement the oracle minus keyword in data stage
Posted: Tue Jul 17, 2007 6:40 am
by Sreenivasulu
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
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 .
Re: how to implement the oracle minus keyword in data stage
Posted: Tue Jul 17, 2007 5:30 pm
by Yuan_Edward
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 .
Posted: Tue Jul 17, 2007 5:50 pm
by DSguru2B
What about the Change Data Capture stage?
Posted: Tue Jul 17, 2007 5:59 pm
by Yuan_Edward
Good idea!
DSguru2B wrote:What about the Change Data Capture stage? ...
Posted: Tue Jul 17, 2007 10:09 pm
by ramesh_c
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.
Posted: Tue Jul 17, 2007 10:49 pm
by Yuan_Edward
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.
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.