From oracle-l-bounce@freelists.org Mon Feb 14 09:25:42 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j1EFPeNx023173 for ; Mon, 14 Feb 2005 09:25:40 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j1EFPeem023168 for ; Mon, 14 Feb 2005 09:25:40 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CA015713CD; Mon, 14 Feb 2005 09:24:31 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09646-03; Mon, 14 Feb 2005 09:24:31 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4E60B71465; Mon, 14 Feb 2005 09:24:31 -0500 (EST) From: "Igor Neyman" To: , "'ORACLE-L'" Subject: RE: Where clause in DBMS_RECTIFIER_DIFF.DIFFERENCES package Date: Mon, 14 Feb 2005 09:22:26 -0500 Message-ID: <001e01c512a0$9bb14300$2004a8c0@development.perceptron.com> MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 In-Reply-To: X-archive-position: 16128 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ineyman@perceptron.com Precedence: normal Reply-To: ineyman@perceptron.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on air891.startdedicated.com X-Spam-Status: No, hits=0.2 required=5.0 tests=AWL,LINES_OF_YELLING, LINES_OF_YELLING_2 autolearn=no version=2.60 X-Spam-Level: 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@perceptron.com -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] 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 <> 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