Where clause in DBMS_RECTIFIER_DIFF.DIFFERENCES package 2005-02-14 - By Igor Neyman
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@(protected)
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)] 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 (See ORA-06550.ora-code.com): 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
--
http://www.freelists.org/webpage/oracle-l
|
|
