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@xxxxxxxxxxxxxx



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@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.

 

 



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

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


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

Other related posts: