Hi Igor, Thanks a lot for the suggestions...Your suggestion worked without any problem.. Can you also pls tell me why we need to add "|| CHR(39) ||" before and after of Month.. Thanks again. Deep -----Original Message----- From: Igor Neyman [mailto:ineyman@xxxxxxxxxxxxxx] Sent: Monday, February 14, 2005 8:22 AM To: GUPTA, DEEPENDER; 'ORACLE-L' Subject: RE: Where clause in DBMS_RECTIFIER_DIFF.DIFFERENCES package Try this (note modified where_clause): BEGIN DBMS_RECTIFIER_DIFF.DIFFERENCES( sname1 => 'ROBOTAG', oname1 => 'T_INFO', reference_site => 'RBTD.WORLD', sname2 => 'ROBOTAG', oname2 => 'T_MISC_INFO', comparison_site => 'DWHP.WORLD', where_clause => '(tag_id in (select tag_id from robotag.t_tag where stop_datetime < add_months(trunc(sysdate,' || CHR(39) || 'MONTH' || CHR(39) || '),-2)))', column_list => '', missing_rows_sname => 'dgupta', missing_rows_oname1 => 'MR_T_INFO', missing_rows_oname2 => 'MR_LOC_T_INFO', missing_rows_site => 'DWHD.WORLD', max_missing => 500, commit_rows => 50); END; / Igor Neyman, OCP DBA ineyman@xxxxxxxxxxxxxx -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of GUPTA, DEEPENDER Sent: Monday, February 14, 2005 9:11 AM To: 'ORACLE-L' Subject: Where clause in DBMS_RECTIFIER_DIFF.DIFFERENCES package Hi All, I was trying to use DBMS_RECTIFIER_DIFF.DIFFERENCES to find out the difference between two tables (one having all the historical data and other having only the last 3 months data). Below is the where clause which I am using but it is not working. Does anybody know if we can use functions like add_months or quotes within quotes... BEGIN DBMS_RECTIFIER_DIFF.DIFFERENCES( sname1 => 'ROBOTAG', oname1 => 'T_INFO', reference_site => 'RBTD.WORLD', sname2 => 'ROBOTAG', oname2 => 'T_MISC_INFO', comparison_site => 'DWHP.WORLD', where_clause => '(tag_id in (select tag_id from robotag.t_tag where stop_datetime < add_months(trunc(sysdate,'MONTH'),-2)))', column_list => '', missing_rows_sname => 'dgupta', missing_rows_oname1 => 'MR_T_INFO', missing_rows_oname2 => 'MR_LOC_T_INFO', missing_rows_site => 'DWHD.WORLD', max_missing => 500, commit_rows => 50); END; / SQL> / where_clause => '(tag_id in (select tag_id from robotag.t_tag where stop_datetime < add_months(trunc(sysdate,'MONTH'),-2)))', * ERROR at line 8: ORA-06550: line 8, column 111: PLS-00103: Encountered the symbol "MONTH" when expecting one of the following: . ( ) , * @ % & = - + < / > at in is mod not rem <an exponent (**)> <> or != or ~= >= <= <> and or like between || The symbol ". was inserted before "MONTH" to continue. -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l