Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

HELP - Refresh variable - ORA-00920: invalid relational operator

Meapri-OracleNov 21 2008 — edited Feb 26 2009

I created a variable to be used as a filter. When the LKM runs it fails at the first step with the ORA-00920: invalid relational operator.
ODI cannot parse the syntax of the variable. I don't know where the error is . Has someone already encountered this king of error ?

Thanks Meapri.

edit : The Variable is :

- alphanumeric
- historic
- in (select business_unit from SYSADM.PS_S1_ODI_FILTR_BU)

The generated SQL :

create or replace view SYSADM_ODI.C$_0BCT_CTL
(
C1_BUSINESS_UNIT,
C2_EIP_CTL_ID,
C3_TRANS_PROGRAM,
C4_TRANSACTION_CODE,
C5_DT_TIMESTAMP,
C6_BCT_STATUS,
C7_BCT_SOURCE,
C8_PROCESS_INSTANCE,
C9_OPRID,
C10_DEVICE_ID,
C11_DEVICE_LAST_SEQ,
C12_DEVICE_DTTIME,
C13_EIP_TRANS_SRC_REF,
C14_EIP_TRANS_SRC,
C15_PUBNODE,
C16_CHNLNAME,
C17_PUBID,
C18_MSGNAME,
C19_SUBNAME,
C20_ERRORS_FLG,
C21_PARTIAL_FLG
)
as select
BCT_CT.BUSINESS_UNIT,
BCT_CT.EIP_CTL_ID,
BCT_CT.TRANS_PROGRAM,
BCT_CT.TRANSACTION_CODE,
BCT_CT.DT_TIMESTAMP,
BCT_CT.BCT_STATUS,
BCT_CT.BCT_SOURCE,
BCT_CT.PROCESS_INSTANCE,
BCT_CT.OPRID,
BCT_CT.DEVICE_ID,
BCT_CT.DEVICE_LAST_SEQ,
BCT_CT.DEVICE_DTTIME,
BCT_CT.EIP_TRANS_SRC_REF,
BCT_CT.EIP_TRANS_SRC,
BCT_CT.PUBNODE,
BCT_CT.CHNLNAME,
BCT_CT.PUBID,
BCT_CT.MSGNAME,
BCT_CT.SUBNAME,
BCT_CT.ERRORS_FLG,
BCT_CT.PARTIAL_FLG
from SYSADM.PS_BCT_CTL BCT_CT
where (1=1)
And (BCT_CT.BUSINESS_UNIT #JDC2_v2.ALL_BU)

Edited by: Meapri on Nov 21, 2008 8:38 AM

Chris Antognini

Shirley

Since the syntax is FOR ALL INDEXED COLUMNS, you are gathering statistics for all indexed columns only. I.e. not for all columns. FOR ALL COLUMNS should be used for that...

HTH
Chris

591186

FOR ALL INDEXED COLUMNS size skewonly',

This is for generating histograms on the indexed columns where column data is skewed. Collects the optimizer statistics for the table, columns, indexes and histograms for columns where the data shape is skewed. Only collects histograms for skewed column. In general it is slower to execute therefore it would not be suitable when the optimizer statistic collection window is short.

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(
ownname => '<table owner>',
tabname => '<table name>',
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
granularity => 'ALL',
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE)

Chris Antognini

Anantha

This is wrong. Sorry. When the option FOR ALL INDEXED COLUMNS is specified, columns statistics are gathered only for the indexed columns.

Best regards,
Chris

71610

Hello All:
I also believe when you specify "FOR ALL INDEXED COLUMNS " in the method_opt , it generates histogram for the said columns.

Thanks
S~

Chris Antognini

I also believe when you specify "FOR ALL INDEXED COLUMNS " in
the method_opt , it generates histogram for the said columns.

If the size clause is not specified, whether the histograms are gathered depends on the version and, as of 10g, on the configuration.

My point was that the parameter method_opt not only impacts histograms, but also column statistics.

Cheers,
Chris

614497

Thanks, Chris.

If I want to gather stats for all columns in a table what value I should pass on to parameter method_opt?

Shirley

Jaffy

Hi,

You can use for all columns in method_opt option.

http://www.psoug.org/reference/dbms_stats.html

Regards

Jafar

591186

I agree.

method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY', - For Generating Histograms.

Thanks for correction.

1 - 8

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 26 2009

Added on Nov 21 2008

#business-intelligence-applications, #data-integrator, #filter, #odi, #system-management-and-integration, #variable

4 comments

1,479 views

Analytics Software

HELP - Refresh variable - ORA-00920: invalid relational operator

Comments

Post Details