RE: Where clause in DBMS_RECTIFIER_DIFF.DIFFERENCES package

39 - is ASCII code for a single quote.
So, if you have single quotes inside "single-quoted" string - that's one way to do it (use CHR(39)).
As always, there are other options.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of GUPTA, DEEPENDER Sent: Monday, February 14, 2005 9:38 AM
To: 'Igor Neyman'; 'ORACLE-L'
Subject: RE: Where clause in DBMS_RECTIFIER_DIFF.DIFFERENCES package

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_at_perceptron.com] 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_at_perceptron.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_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

<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


--
http://www.freelists.org/webpage/oracle-l