Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Where clause in DBMS_RECTIFIER_DIFF.DIFFERENCES package

Where clause in DBMS_RECTIFIER_DIFF.DIFFERENCES package

From: GUPTA, DEEPENDER <DGUPTA_at_entergy.com>
Date: Mon, 14 Feb 2005 08:11:05 -0600
Message-ID: <E9270A7736A5914C94E0D74EB6A513610374C803@tcbyexs02.ar.entergy.com>


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.    

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 14 2005 - 09:14:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US