analytics/bin64/cogdmor.ini

;***********************************************************************
;Licensed Materials - Property of IBM
;
;BI and PM: UDA
;
;(C) Copyright IBM Corp. 2005, 2020
;
;U.S. Government Users Restricted Rights - Use, duplication, or disclosure 
;by GSA ADP Schedule Contract with IBM Corp.
;
;Unless specifically authorized by IBM, you may not modify any part of this
;file. Where modification is authorized, you must reproduce any copyright
;notices contained in this file and specifically identify which
;modifications have been made by your organization. YOU ARE SOLELY
;RESPONSIBLE FOR DETERMINING THE APPROPRIATENESS OF ANY MODIFICATIONS TO
;THIS FILE AND ASSUME ALL RISKS ASSOCIATED WITH THE USE AND DISTRIBUTION
;OF THE MODIFIED FILE. IBM will not provide support relating to
;unauthorized changes you make to this file.
;***********************************************************************
; $Header: $
;
; Module:
;    cogdmor.ini
;
; Purpose:
;    This module contains information used by the Oracle gateway.
;
; Notes:
;    Do NOT modify this file.  Doing so could result in unknown behavior
;    by the Oracle gateway, possibly resulting in application aborts. 
;
; ************************************************************************

; To create a trace of OCI routines called, uncomment the following two lines
; and specify a vaild trace file name.
;[TRACE]
;Output=<my trace file>
;
; To enable printing the elapsed times for OCI function calls, uncomment the following line:
;Timer=yes


[Expression Results]

[Exceptions Commands]
Call="call "
Max_Literal_Len="4000"

[Exceptions Clauses]
With="with "

[Exceptions Tables]
Joined=T
Derived=T
UniqueName_Prefix="coguda"
; Maximum length of aliases.
SQL_MAX_TABLE_NAME_LEN="128"

[Exceptions Tables ORACLE12.1.0]
SQL_MAX_TABLE_NAME_LEN="30"

[Exceptions Tables ORACLE11.2.0]
SQL_MAX_TABLE_NAME_LEN="30"

[Exceptions Tables ORACLE10.2.0]
SQL_MAX_TABLE_NAME_LEN="30"

[Exceptions Tables ORACLE10.1.0]
SQL_MAX_TABLE_NAME_LEN="30"

[Exceptions Joins]
Inner=T
Left_Outer=T
Right_Outer=T
Full_Outer=T
Cross=T
Nested_Inner=T
Nested_Outer=T
Left_Nested=T
Right_Nested=T
Inner_Tbl_Restrict=F
Optnl_Tbl_Restrict=T
Non_Equi_Joins=T
Optnl_Tbl_Join_Filter=T
Optnl_Tbl_Filter=T
Two_Sided_Join_Restrict=F
Optnl_Tbl_Join_Restrict=F
On_Condition_Between_Predicate=T
On_Condition_Like_Predicate=T
On_Condition_Not_Predicate=T
On_Condition_IsNull_Predicate=T
On_Condition_In_Predicate=T
On_Condition_Set_Functions=F
On_Condition_Subqueries=T
On_Condition_Functions=T
On_Condition_Or_Predicate=T
Prsrv_Tbl_Join_Filter=T
Full_Syntax=" FULL OUTER JOIN "
Inner_Syntax=" INNER JOIN "
Left_Eql=" LEFT OUTER JOIN "
Right_Eql=" RIGHT OUTER JOIN "


[Exceptions Joins ORACLE10.1.0]
Full_Outer=F

[Exceptions Predicates]
All="all "
Exists="exists "

[Exceptions Aggregates]
Max="max"
Min="min"
Sum="sum"
Avg="avg"
Count="count"
Count_Star="count(*)"
Stddev="stddev"
Variance="variance"
Stddev_Pop="stddev_pop"
Var_Pop="var_pop"
Stddev_Samp="stddev_samp"
Var_Samp="var_samp"
Rank="rank"
Dense_Rank="dense_rank"
Percent_Rank="percent_rank"
Cume_Dist="cume_dist"
Grouping="grouping"


[Exceptions OLAP Functions]
Olap_Max="max"
Olap_Min="min"
Olap_Sum="sum"
Olap_Avg="avg"
Olap_Count="count"
Olap_Count_Star="count(*)"
Olap_Stddev_Pop="stddev_pop"
Olap_Var_Pop="var_pop"
Olap_Stddev_Samp="stddev_samp"
Olap_Var_Samp="var_samp"
Olap_Rank="rank"
Olap_Dense_Rank="dense_rank"
Olap_Percent_Rank="percent_rank"
Olap_Cume_Dist="cume_dist"
Olap_Row_Number="row_number"
Olap_Ratio_To_Report="ratio_to_report"
Olap_Ntile="ntile"
Olap_First_Value="first_value"
Olap_Last_Value="last_value"
Olap_Percentile_Cont="percentile_cont"
Olap_Percentile_Disc="percentile_disc"
;Trakker 577783
Olap_Last_Value_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"

[Exceptions Set Operators]
Distinct="distinct"
Union="union"
Intersect="intersect"
Except=" minus "
Union_All=T

[Exceptions Operators]
Neq=" <> "

[Exceptions Blob Expressions]
;Even though Oracle allows some expression on blobs, but
;LONG type is different story, and since UDA does not 
;distinguish LONG from CLOB, so there might be some problem here
Blob_In_Substring=T
Blob_In_StrCat=T
Blob_In_Trim=T
Blob_In_Substring_Return_Blob=T

[Exceptions Value Expressions]
Extract=C
Extract_Year=coguda#extract_year
Extract_Month=coguda#extract_month
Extract_Day=coguda#extract_day
Extract_Hours=coguda#extract_hour
Extract_Minutes=coguda#extract_minute
Extract_Seconds=coguda#extract_second
Position=C
Char_Length=C
Substring=C
Upper=T
Lower=T
StrCat=T
Case=T
Coalesce=T
Nullif=T
Cast=C
Trim=T
Cube=T
Rollup=T
Grouping_Sets=T
Bit_Length=C
Octet_Length=C
Current_Date=C
Current_Time=F
Current_Timestamp=T
Abs=T
Mod=T
Ln=T
Exp=T
Power=T
Sqrt=T
Floor=T
Ceiling=C
Localtime=F
Localtimestamp=T
Row_Value_Constructors=T

; SGI will only support TIMESTAMP literal, other entries are written for future reference.
; Although format string for Date and Time are provided but they are not used as
; we only set Timestamp_Literal=C, if user wants to pass DATE/TIME expressions 
; then set Date_Literal=C, Time_Literal=C and TimeTZ_Literal=C.
[Exceptions Literals]
Date_Literal=T
Time_Literal=F
TimeTZ_Literal=F
Interval_Literal=T
Interval_Format_Str="DDDDDDDDD HH MM SS FFFFFFFFF"
IntervalYM_Literal=T
IntervalYM_Format_Str="YYYYYYYYY MM"
Interval_Explicit_Precision=T
Timestamp_Literal=T
TimestampTZ_Literal=T
Date_Format_Str="YYYY MM DD"
Time_Format_Str="HH MM SS FFFFFFFFF"
TimeTZ_Format_Str="HH MM SS FFFFFFFFF HHMM"
Timestamp_Format_Str="YYYY MM DD HH MM SS FFFFFFFFF"
TimestampTZ_Format_Str="YYYY MM DD HH MM SS FFFFFFFFF HHMM" 
Date_Literal_Str="to_date('%s-%s-%s', 'YYYY-MM-DD')"
Time_Literal_Str="to_timestamp('%s:%s:%s.%s', 'HH24:MI:SS.FF9')"
TimeTZ_Literal_Str="to_timestamp_tz('%s:%s:%s.%s %s:%s', 'HH24:MI:SS.FF9 TZH:TZM' )"
Timestamp_Literal_Str="to_timestamp('%s-%s-%s %s:%s:%s.%s', 'YYYY-MM-DD HH24:MI:SS.FF9')"
TimestampTZ_Literal_Str="to_timestamp_tz('%s-%s-%s %s:%s:%s.%s %s:%s', 'YYYY-MM-DD HH24:MI:SS.FF9 TZH:TZM' )"
Date_Sub_Function_Str="to_timestamp(to_char(?1,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') - to_timestamp(to_char(?2,'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')"
DateRange_Start_Str="to_date('%s-%s-%s 00:00:00', 'YYYY-MM-DD HH24:MI:SS')"
DateRange_End_Str="( to_date('%s-%s-%s 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + INTERVAL '1' DAY )"
NChar_Literal=T
Boolean_Literal=F
True_Literal_Str=
False_Literal_Str=
Unknown_Literal_Str=


;Exception Literal section for Oracle 8 and Oracle 8.1 allows
;to generate Date Literals in the format that satisfies Oracle partitioning
;requirements; These entries should be uncommented only if the user requires
;to take advantage of table partitioning;


[Exceptions General]
Group_By_Expr=T
Multiple_Distinct=T
Count_Non_Distinct=T
Count_Value_Expr=T
Count_Literal=T
Is_Null_Value_Expr=T
Is_Null_Value_Parm=T
Like_Value_Expr=T
Subquery_In_Having=T
Subquery_In_Group_By=F
Count_Blob=F
Case_To_Decode=F
Expression_In_In=T
Null_Order=T
Integer_Division_Truncation=F
Use_Dbkey_For_Blob=T
Olap_Null_Order=T
Empty_String_Is_Null=T
Order_By_In_Derived_Table=T
Interval_As_VarChar=T
Boolean_Comparison=F
Derived_Column_List=F
With_Column_List=F
Aggr_With_Interval=F
;When setting to "PT", the native SQL in common table expression of a With clause
;is considered as a pass-through SQL only which is pushed to database.
;When setting to "DT", the native SQL is re-written as a derived table (10.1 and prior behaviour)
;Native_SQL_In_CTE="KEEP"

[Exceptions Delimiters]
Catalog_Delimiter="\""
Schema_Delimiter="\""
Table_Delimiter="\""
Column_Delimiter="\""
Procedure_Delimiter="\""
Wholename_Delimiter=
Table_Function_PreStr="TABLE( "
Table_Function_PostStr=" )"
Comment_Begin="/* "
Comment_End=" */ "

[Exceptions Separators]
Catalog_Separator="."
Schema_Separator="."
Table_Separator="."

[Exceptions Misc]
Session_Sort_Order=T
;Oracle applies implicit conversion function on DATE column
;when comparison with TIMESTAMP, and causes no index scan on DATE column
;Convert TIMESTAMP literal without time value to DATE literal to
;avoid Oracle performance issue.
Convert_Timestamp_Literal_To_Date=F

[Builtin Functions]
;Each of the parameter is identified by "%" followed by the number , followed by the type . The types can be the following: 
;"b" stands for boolean. 
;"c" stands for character. 
;"x" stands for text. 
;"d" stands for date. 
;"i" stands for the interval. 
;"n" stands for the numeric. 
;"s" stands for the timestamp. 
;"t" stands for the time. 
;"y" stands for time with time zone
;"z" stands for timestamp with time zone

n:char_length(%1cx)=LENGTH(%1);
n:character_length(%1cx)=LENGTH(%1);
bcdints:coalesce(%1bcdintsz,%2bcdintsz)=NVL(%1,%2);
bcdints:nullif(%1bcdintsz,%2bcdintsz)=decode(%1,%2,NULL,%1);
n:octet_length(%1cx)=LENGTHB(%1);
n:bit_length(%1cx)=LENGTHB(%1) * 8;
n:position(%1cx,%2cx)=INSTR(%2,%1);
c:substring(%1c,%2n {,%3n})=SUBSTR(%1,%2 {,%3});
x:substring(%1x,%2n {,%3n})=SUBSTR(%1,%2 {,%3});
n:ceiling(%1n)=CEIL(%1);
n:absolute(%1n)=ABS(%1);
c:downshift(%1c)=LOWER(%1);
n:floor(%1n)=FLOOR(%1);
n:index(%1c,%2c)=INSTR(%1,%2);
ds:lastday(%1dsz)=LAST_DAY(%1);
c:leftjustify(%1c)=LTRIM(%1);
n:nconvert(%1c)=TO_NUMBER(%1);
n:size(%1c)=LENGTH(%1);
c:truncate(%1c)=RTRIM(%1);
c:upshift(%1c)=UPPER(%1);
n:power(%1n,%2n)=POWER(%1,%2);
n:monthsbetween(%1dsz,%2dsz)=TRUNC(MONTHS_BETWEEN(%2,%1));
n:ascii(%1c)=ascii(%1);
n:round(%1n,%2n)=round(%1,%2);
c:cast_varchar(%1n)=TO_CHAR(%1);
c:cast_varchar(%1d)=TO_CHAR(%1, 'YYYY-MM-DD');
c:cast_varchar(%1s)=TO_CHAR( TO_TIMESTAMP(%1), 'YYYY-MM-DD HH24:MI:SS.FF9');
c:cast_varchar(%1z)=TO_CHAR((%1), 'YYYY-MM-DD HH24:MI:SS.FF9 TZR' );

;
; Functions required for the EXTRACT function
;
n:coguda#extract_hour(%1dstz)=EXTRACT( HOUR FROM CAST((%1) AS TIMESTAMP(2)) );
n:coguda#extract_hour(%1i)=EXTRACT( HOUR FROM (%1) );
n:coguda#extract_minute(%1dstz)=EXTRACT( MINUTE FROM CAST((%1) AS TIMESTAMP(2)) );
n:coguda#extract_minute(%1i)=EXTRACT( MINUTE FROM (%1) );
n:coguda#extract_second(%1dst)=EXTRACT( SECOND FROM CAST((%1) AS TIMESTAMP) );
n:coguda#extract_second(%1iz)=EXTRACT( SECOND FROM (%1) );
n:coguda#extract_year(%1dsiz)=EXTRACT( YEAR FROM (%1) );
n:coguda#extract_month(%1dsiz)=EXTRACT( MONTH FROM (%1) );
n:coguda#extract_day(%1dsiz)=EXTRACT( DAY FROM (%1) );

;
; Function mappings for CAST function
;
c:coguda#cast_char(%1nx,%2n)=CAST( %1 AS CHAR( %2 ) );
c:coguda#cast_char(%1c,%2n)=CAST( %1 AS CHAR( %2 CHAR ) );
c:coguda#cast_char(%1d,%2n)=CAST( TO_CHAR(%1, 'YYYY-MM-DD') AS CHAR( %2 ) );
c:coguda#cast_char(%1s,%2n)=CAST( TO_CHAR( cast(%1 as TIMESTAMP(9)), 'YYYY-MM-DD HH24:MI:SS.FF9') AS CHAR( %2 ) );
c:coguda#cast_char(%1z,%2n)=CAST( TO_CHAR( %1, 'YYYY-MM-DD HH24:MI:SS.FF9TZR') AS CHAR( %2 ) );
c:coguda#cast_varchar(%1nx,%2n)=CAST( %1 AS VARCHAR( %2 ) );
c:coguda#cast_varchar(%1c,%2n)=CAST( %1 AS VARCHAR( %2 CHAR ) );
c:coguda#cast_varchar(%1d,%2n)=CAST( TO_CHAR(%1, 'YYYY-MM-DD') AS VARCHAR( %2 ) );
c:coguda#cast_varchar(%1s,%2n)=CAST( TO_CHAR(cast(%1 as TIMESTAMP(9)), 'YYYY-MM-DD HH24:MI:SS.FF9') AS VARCHAR( %2 ) );
c:coguda#cast_varchar(%1z,%2n)=CAST( TO_CHAR((%1), 'YYYY-MM-DD HH24:MI:SS.FF9TZR' ) AS VARCHAR( %2 ) );
c:coguda#cast_nchar(%1ncx,%2n)=CAST( %1 AS NCHAR( %2 ) );
c:coguda#cast_nchar(%1d,%2n)=CAST( TO_CHAR(%1, 'YYYY-MM-DD') AS NCHAR( %2 ) );
c:coguda#cast_nchar(%1s,%2n)=CAST( TO_CHAR( cast(%1 as TIMESTAMP(9)), 'YYYY-MM-DD HH24:MI:SS.FF9') AS NCHAR( %2 ) );
c:coguda#cast_nchar(%1z,%2n)=CAST( TO_CHAR( %1, 'YYYY-MM-DD HH24:MI:SS.FF9TZR') AS NCHAR( %2 ) );
c:coguda#cast_nvarchar(%1ncx,%2n)=CAST( %1 AS NCHAR VARYING( %2 ) );
c:coguda#cast_nvarchar(%1d,%2n)=CAST( TO_CHAR(%1, 'YYYY-MM-DD') AS NCHAR VARYING( %2 ) );
c:coguda#cast_nvarchar(%1s,%2n)=CAST( TO_CHAR( cast(%1 as TIMESTAMP(9)), 'YYYY-MM-DD HH24:MI:SS.FF9') AS NCHAR VARYING( %2 ) );
c:coguda#cast_nvarchar(%1z,%2n)=CAST( TO_CHAR((%1), 'YYYY-MM-DD HH24:MI:SS.FF9TZR' ) AS NCHAR VARYING( %2 ) );
n:coguda#cast_double_precision(%1)=CAST( %1 AS DOUBLE PRECISION );
d:coguda#cast_date(%1ds)=TRUNC(cast(%1 as TIMESTAMP(9)));
d:coguda#cast_date(%1c)=TO_DATE( %1, 'YYYY-MM-DD' );
;
; an EE style cast( number as date). The number is YYYYMMDD. Trakker #536672 & 545831
d:coguda#cast_date(%1n)=CASE %1 WHEN 0 THEN TO_DATE( '00010101','YYYYMMDD')  ELSE TO_DATE( TO_CHAR( %1 ), 'YYYYMMDD') END; 

t:coguda#cast_timestampTZ(%1)=CAST( ( %1 ) AS TIMESTAMP WITH TIME ZONE );
s:coguda#cast_timestamp(%1c)=TO_TIMESTAMP(%1,'YYYY-MM-DD HH24:MI:SS.FF9');
s:coguda#cast_timestamp(%1dsz)=CAST( ( %1 ) AS TIMESTAMP );

; Business Functions for Date Calculations
;
s:_add_days(%1s,%2n)=(%1 + (INTERVAL '1' DAY * (%2)));
d:_add_days(%1d,%2n)=(%1 + (INTERVAL '1' DAY * (%2)));
z:_add_days(%1z,%2n)=(%1 + (INTERVAL '1' DAY * (%2)));
s:_add_months(%1s,%2n)=CASE WHEN EXTRACT( DAY FROM %1 - NUMTODSINTERVAL( EXTRACT( DAY FROM %1 ), 'DAY' ) + INTERVAL '1' DAY + NUMTOYMINTERVAL( %2, 'MONTH' ) + INTERVAL '1' MONTH - INTERVAL '1' DAY ) < EXTRACT( DAY FROM %1 ) THEN %1 - NUMTODSINTERVAL( EXTRACT( DAY FROM %1 ), 'DAY' ) + INTERVAL '1' DAY + NUMTOYMINTERVAL( %2, 'MONTH' ) + INTERVAL '1' MONTH - INTERVAL '1' DAY ELSE %1 + NUMTOYMINTERVAL( %2, 'MONTH' ) END;
d:_add_months(%1d,%2n)=CASE WHEN EXTRACT( DAY FROM %1 - NUMTODSINTERVAL( EXTRACT( DAY FROM %1 ), 'DAY' ) + INTERVAL '1' DAY + NUMTOYMINTERVAL( %2, 'MONTH' ) + INTERVAL '1' MONTH - INTERVAL '1' DAY ) < EXTRACT( DAY FROM %1 ) THEN %1 - NUMTODSINTERVAL( EXTRACT( DAY FROM %1 ), 'DAY' ) + INTERVAL '1' DAY + NUMTOYMINTERVAL( %2, 'MONTH' ) + INTERVAL '1' MONTH - INTERVAL '1' DAY ELSE %1 + NUMTOYMINTERVAL( %2, 'MONTH' ) END;
z:_add_months(%1z,%2n)=CASE WHEN EXTRACT( DAY FROM %1 - NUMTODSINTERVAL( EXTRACT( DAY FROM %1 ), 'DAY' ) + INTERVAL '1' DAY + NUMTOYMINTERVAL( %2, 'MONTH' ) + INTERVAL '1' MONTH - INTERVAL '1' DAY ) < EXTRACT( DAY FROM %1 ) THEN %1 - NUMTODSINTERVAL( EXTRACT( DAY FROM %1 ), 'DAY' ) + INTERVAL '1' DAY + NUMTOYMINTERVAL( %2, 'MONTH' ) + INTERVAL '1' MONTH - INTERVAL '1' DAY ELSE %1 + NUMTOYMINTERVAL( %2, 'MONTH' ) END;
s:_add_years(%1s,%2n)=CASE WHEN TO_CHAR(%1,'MMDD') = '0229' AND MOD(%2,4) <> 0 THEN (%1 + INTERVAL '1' DAY) + NUMTOYMINTERVAL(%2,'YEAR') - INTERVAL '1' DAY ELSE %1 + NUMTOYMINTERVAL(%2,'YEAR') END;
d:_add_years(%1d,%2n)=CASE WHEN TO_CHAR(%1,'MMDD') = '0229' AND MOD(%2,4) <> 0 THEN (%1 + INTERVAL '1' DAY) + NUMTOYMINTERVAL(%2,'YEAR') - INTERVAL '1' DAY ELSE %1 + NUMTOYMINTERVAL(%2,'YEAR') END;
z:_add_years(%1z,%2n)=CASE WHEN TO_CHAR(%1,'MMDD') = '0229' AND MOD(%2,4) <> 0 THEN (%1 + INTERVAL '1' DAY) + NUMTOYMINTERVAL(%2,'YEAR') - INTERVAL '1' DAY ELSE %1 + NUMTOYMINTERVAL(%2,'YEAR') END;
n:_days_between(%1dsz,%2dsz)=(TRUNC( CAST( %1 AS TIMESTAMP ) ) - TRUNC( CAST( %2 AS TIMESTAMP ) ));
n:_months_between(%1dsz,%2dsz)=TRUNC( MONTHS_BETWEEN( %1, %2 ), 0 );
n:_years_between(%1dsz,%2dsz)=TRUNC( ( MONTHS_BETWEEN( %1, %2) / 12 ), 0 );
n:_days_to_end_of_month(%1dsz)=(EXTRACT( DAY FROM LAST_DAY(%1) ) - EXTRACT( DAY FROM %1 ));
n:_age(%1dsz)=;
s:_first_of_month(%1s)=(%1 - NUMTODSINTERVAL( EXTRACT( DAY FROM %1 ), 'DAY' ) + INTERVAL '1' DAY);
d:_first_of_month(%1d)=(%1 - NUMTODSINTERVAL( EXTRACT( DAY FROM %1 ), 'DAY' ) + INTERVAL '1' DAY);
z:_first_of_month(%1z)=(%1 - NUMTODSINTERVAL( EXTRACT( DAY FROM %1 ), 'DAY' ) + INTERVAL '1' DAY);
d:_last_of_month(%1d)=(%1 - NUMTODSINTERVAL( EXTRACT( DAY FROM %1 ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY);
s:_last_of_month(%1s)=(%1 - NUMTODSINTERVAL( EXTRACT( DAY FROM %1 ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY);
z:_last_of_month(%1z)=(%1 - NUMTODSINTERVAL( EXTRACT( DAY FROM %1 ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY);
s:_make_timestamp(%1n,%2n,%3n)=TO_TIMESTAMP( ( LPAD( %1, 4, '0' ) || '-' || LPAD( %2, 2, '0' ) || '-' || LPAD( %3, 2, '0' ) ), 'YYYY-MM-DD' );
n:_ymdint_between(%1dsz,%2dsz)=;
n:_day_of_year(%1dsz)=TO_NUMBER( TO_CHAR( %1, 'DDD' ) );
n:_week_of_year(%1dsz)=TO_NUMBER( TO_CHAR( %1, 'IW' ) );
n:_day_of_week(%1dsz, %2n)=(MOD( MOD( TO_NUMBER( TO_CHAR( %1, 'D' ) ) - TO_NUMBER( TO_CHAR( TO_DATE( '2003-01-06', 'YYYY-MM-DD' ), 'D' ) ) + 7, 7 ) + 1 - %2 + 7, 7 ) + 1);

;
; Additional Business Functions for Date Calculations
;
s:_add_hours(%1s,%2n)=(%1 + (INTERVAL '1' HOUR * (%2)));
t:_add_hours(%1t,%2n)=(%1 + (INTERVAL '1' HOUR * (%2)));
s:_add_minutes(%1s,%2n)=(%1 + (INTERVAL '1' MINUTE * (%2)));
t:_add_minutes(%1t,%2n)=(%1 + (INTERVAL '1' MINUTE * (%2)));
s:_add_seconds(%1s,%2n)=(%1 + (INTERVAL '1' SECOND * (%2)));
t:_add_seconds(%1t,%2n)=(%1 + (INTERVAL '1' SECOND * (%2)));
n:_hours_between(%1ds,%2ds)=;
n:_minutes_between(%1ds,%2ds)=;
n:_seconds_between(%1ds,%2ds)=;

[Builtin Functions ORACLE10.2.0]
;
; There are a number of oracle bugs w.r.t TRUNC, OLAP sum and sorts (ORA-0600).
; 4655998, 4736198, 5202144
; Rewriting cast_date in this form avoids these errors.
; The other alternative is to disable OLAP_SUM which would be very computationally expensive.
; 
; this goal of this expression is to 'zero out' the time portion of the 
; Oracle DATE object using TRUNC( X, 'DD' ) however we must ensure that 
; Oracle uses TRUNC ( date, char) instead of TRUNC( numeric, char) when 
; working with parameters. Hence the innermost cast to DATE
;
d:coguda#cast_date(%1ds)=cast( TRUNC( CAST( %1 as DATE), 'DD' ) AS DATE );

[Database Functions]
c:translate(%1c,%2c,%3c)=TRANSLATE(%1,%2,%3);
n:_round(%1n, %2n)=round(%1, %2);
d:current_date()=TRUNC( CURRENT_DATE );
ds:adddays(%1dsz,%2n)=((%1) + (%2));
n:datedaydiff(%1dsz,%2dsz)=((%2) - (%1));
n:or_ascii(%1c)=ASCII(%1);
n:or_avg(%1c,%2n)=AVG(%1 %2);
bcdints:or_count(%1c,%2bcdintsz)=COUNT(%1 %2);
bcdints:or_max(%1c,%2bcdintsz)=MAX(%1 %2);
bcdints:or_min(%1c,%2bcdintsz)=MIN(%1 %2);
dns:or_round(%1dns {,%2cn})=ROUND(%1 {,%2});
c:or_soundex(%1c)=SOUNDEX(%1);
n:or_sum(%1c,%2n)=SUM(%1 %2);
c:or_user()=USER;
ds:sysdate()=SYSDATE;
n:uid()=UID;
c:userenv(%1c {,%2c})=SUBSTR(USERENV(%1),1);
n:rownum()=ROWNUM;
   

[Directives Session]
Ordered_Pos=Block_Comment
Ordered_Text="ORDERED"
Star_Pos=Block_Comment
Star_Text="STAR"
Block_Comment_Pre_Text="/*+"
Block_Comment_Post_Text="*/"
Append_Pre_Text=""
Append_Post_Text=""

; First Row optimization has been disabled due to performance issues. (Trakker 493725)
; To enable first row optimization, set the First_Row_Text entry to "FIRST_ROWS".  
; Please note that first row optimization would cause the Oracle error "ORA-00600: internal 
; error code, arguments: [12410], [], [], [], [], [], [], []" when running against an
; Oracle 10g server. (Trakker 448744)  This issue was resolved in Oracle 10.1.0.4.0.
First_Row_Text=
First_Row_Pos=Block_Comment

; Row Limit in Oracle is achieved by using
; the pseudo-rownum which is assigned in run time
; by Oracle db engine during fetch time, thus by
; limiting the rownum we limit the total row count
; for the fetch.
; The ROWNUM is added to the WHERE clause of an outer select
Query_Row_Limit_Pos=Outer_Select_Predicate
Query_Row_Limit_Text="ROWNUM <= %d"

;Oracle does not allow to execute a few sql statements at the same time!!!
;All statements have to be separated by a semicolon.
[UDA USER OPTIONS]
;Attach=ALTER SESSION SET NLS_LANGUAGE=FRENCH;ALTER SESSION SET NLS_TERRITORY=FRANCE NLS_SORT=BINARY
;
;Trakker 586412:[TLC3] Unable to reopen a certain filtered QS Reports with a specific relational source
;The problem is caused by Oracle bug 6051782, which is fixed in 10.2.0.4 and 11.1.0.7.
;Workarounds provided by Oracle to either remove DISTINCT, ORDER BY or 
;ALTER SESSION SET "_COMPLEX_VIEW_MERGING" = FALSE, which can be set at the
;connection command block in the content store as follow:
;<commandBlock>
;<commands>
;<sqlCommand>
;<sql>ALTER SESSION SET "_COMPLEX_VIEW_MERGING" = FALSE</sql>
;</sqlCommand>
;</commands>
;</commandBlock>
;
;The setting of ALTER SESSSION via command blocks is the preferred method. 
;User can also set the alter session by uncomment the following line but beaware that
;all ORACLE connections get this setting:
;Attach=ALTER SESSION SET "_COMPLEX_VIEW_MERGING" = FALSE

[I18N Encoding]
UTF8=utf-8
AL32UTF8=utf-8
AL16UTF16=utf-16
US7ASCII=US-ASCII
WE8ISO8859P1=iso-8859-1
BLT8ISO8859P1=iso-8859-1
CEL8ISO8859P1=iso-8859-1
EE8ISO8859P2=iso-8859-2
E8ISO8859P3=iso-8859-3
NEE8ISO8859P4=iso-8859-4
CL8ISO8859P5=iso-8859-5
AR8ISO8859P6=iso-8859-6
AR8ISO8859P6=iso-8859-6
EL8ISO8859P7=iso-8859-7
IW8ISO8859P8=iso-8859-8
WE8ISO8859P9=iso-8859-9
NE8ISO8859P10=iso-8859-10
WE8ISO8859P15=iso-8859-15
EE8MSWIN1250=cp1250
CL8MSWIN1251=cp1251
WE8MSWIN1252=cp1252
EL8MSWIN1253=cp1253
TR8MSWIN1254=cp1254
IW8MSWIN1255=cp1255
AR8MSWIN1256=cp1256
BLT8MSWIN1257=cp1257
VN8MSWIN1258=cp1258
;Simplified Chinese
ZHS16CGB231280=ibm-eucCN
ZHS16CGB231280FIXED=ibm-eucCN
ZHS16GBK=cp936
;Traditional Chinese
ZHT16BIG5=cp950
ZHT16MSWIN950=cp950
ZHS32GB18030=gb18030
;Korean
KO16KSC5601=5601
KO16MSWIN949=cp949
;Japanese Shift JIS
JA16SJIS=sjis
;Japanese EUC JIS
JA16EUC=eucjis
;Thai
TH8TISASCII=cp874
TH8TISEBCDICS=cp9030
TH8TISEBCDIC=cp9030

[Misc]
; Specifies which conversion algorithm to use
Numeric_binding=C

;
; The Transparent Gateways for DB/2, SQL/400 and DRDA have, depending
; upon the version of the gateway, exhibited a problem with distinct
; clauses and aggregates.  The backend databases have restrictions for 
; which the gateways should account, but sometimes do not.  Setting
; the 'Distinct Restriction' entry to zero indicates that this problem
; has been corrected.
;
Distinct Restriction=1
;
;
; The entry 'Fetch Number of Rows' is used to determine how many rows to fetch
; per fetch operation.  Increasing this number can provide better performance
; on some systems.  Note that the OCI currently limits this number to 32767.
; Also note that numbers larger than 100 may actually degrade performance on
; some systems.
;
; The entry 'Fetch Buffer Size' is used to determine the size of buffer to 
; use when fetching.  Larger values can provide better performance on
; some systems.  Note that on 16bit Windows platforms, this is restricted
; to approximately 64k.
; 
; If both 'Fetch Buffer Size' and 'Fetch Number of Rows' are set, the latter
; will take precedence and the former will be ignored.
;
; By default, the buffer size used is 2048 bytes, to change this default,
; uncomment one of the following entries and set it accordingly.
;
;Fetch Buffer Size=2048
;Fetch Number of Rows=10

; The entry 'Not Support Scaled Integer' is used to change the Oracle scaled
; integer type from interger types to sqlDecimal type. The default of this entry
; is 0 for 'Not Support Scaled Integer' is FALSE. Set the value to 1, users will
; get sqlDecimal for all the non-zero scaled integers.
;
Not Support Scaled Integer=0

; This flag allows the user to alter the session details as required.
; Please make sure that the user enters the valid parameters ( specific
; to Oracle ) to be altered. Please do not put double qoutes around
; each parameter , however, user can put single quotes around some
; of the parameters like :- NLS_DATE_FORMAT='YYYY-DD-MM' . Check
; Oracle SQL reference manual for details for uasge.
;
; ALTER_SESSION_PARAMETERS=NLS_LANGUAGE=FRENCH NLS_TERRITORY=FRANCE NLS_SORT=BINARY

; Default processing mode
;
Processing Mode=Database Only
;

[UDA Misc]
; Set this entry to one of the following values to force the type of
; transformation performed on queries containing aggregates computed
; at different levels of granularity. A value other than 0 will cause
; a query to generated that is more materialized view friendly.
;
; 0 = default
; 1 = derived tables
; 2 = union
;
Multigrain_Query_XForm_Strategy=0

;
; Operations supported and results of the supported operation.
; The format is;
; [Operator <operator name>]
; <LHS>=<RHS>,<RES>{:<RHS>,<RES>}
;
; where
;
; LHS = data type of left hand side of operation
; RHS = data type of right hand side of operation
; RES = result data type of operation
;
; LHS, RHS and RES values may be one of:
;
;   Name			Abbreviation			DMS Data Type
;	CHAR			CH						sqlChar
;	VARCHAR			VC						sqlVarChar
;	LVARCHAR		LC						sqlLongVarChar
;	NCHAR			NC						sqlNChar
;	NVARCHAR		NV						sqlNVarChar
;	BINARY			BN						sqlBinary
;	VARBINARY		VB						sqlVarBinary
;	BOOLEAN			BO						sqlBoolean
;	SMALLINT		SM						sqlSmallInt
;	INTEGER			IT						sqlInteger
;	QUAD			QD						sqlQuad
;	DECIMAL			DM						sqlDecimal
;	NUMERIC			NU						sqlNumeric
;	FLOAT			FL						sqlFloat
;	DOUBLE			DB						sqlDouble
;	DATE			DT						sqlDate
;	TIME			TM						sqlTime
;	TIMESTAMP		TS						sqlDateTime
;	INTERVAL		IV						sqlInterval
;	TIME_TZ			TT						sqlTimeTZ
;	TIMESTAMP_TZ	TZ						sqlTimestampTZ
;	INTERVAL_YM		IY						sqlIntervalYM
;   TEXT            TX                      sqlText
;   BLOB            BL                      sqlBlob
;	ALL DATATYPES	AD                      -------------
;	----			NA						sqlUnknownType
;

;The following entries are needed for Date/Time/DateTime/Interval data type
;arithmetic operations.  Unless set here, any arithmetic operations on those
;data types will return UDA-SQL-0492 Date/Time aritmetic is not supported. error
;Oracle supports arthmetic ADD/SUBTRACT on DateTime datatype

[Operator Addition]
Date=SM,DT:IT,DT:DM,DT:NU,DT:FL,DT:DB,DT
Timestamp=SM,TS:IT,TS:DM,TS:NU,TS:FL,TS:DB,TS

[Operator Subtraction]
Date=SM,DT:IT,DT:DM,DT:NU,DT:FL,DT:DB,DT
Timestamp=SM,TS:IT,TS:DM,TS:NU,TS:FL,TS:DB,TS

[Operator Comparison]
Date=DT,OK:TS,OK
Timestamp=DT,OK:TS,OK

;
; These SQL-92 CAST expressions are supported by Oracle 
; To furthur expand cast support, we could use built-in cast_functions
;
; Notes:
;  - Casting as SMALLINT, INTEGER and BIGINT cannot be supported for
;	 Oracle because Oracle only has one datatype (NUMBER) to represent  
;	 exact numeric values and its precision is the number of digits, 
;	 which does not help UDA map to the correct datatype.
;	 ie. The maximum value of a SMALLINT is 32767 and this value has
;		 a precision of 5.  As the maximum value of a NUMBER with 
;		 precision 5 is 99999, UDA maps this precision value to an 
;		 INTEGER, not a SMALLINT.
;
[Operator Cast]
AD=AD,NA
NA=CH,CH:VC,VC:NC,NC:NV,NV:DB,DB:DT,DT:TS,TS:TZ,TZ:NU,NU:DM,DM
CH=CH,CH:VC,VC:NU,NU:DM,DM:DB,DB:DT,DT:TS,TS
VC=CH,CH:VC,VC:NU,NU:DM,DM:DB,DB:DT,DT:TS,TS
NC=NC,NC:NV,NV:NU,NU:DM,DM:DB,DB
NV=NC,NC:NV,NV:NU,NU:DM,DM:DB,DB
SM=CH,CH:VC,VC:NC,NC:NV,NV:NU,NU:DM,DM:DB,DB:DT,DT
IT=CH,CH:VC,VC:NC,NC:NV,NV:NU,NU:DM,DM:DB,DB:DT,DT
NU=CH,CH:VC,VC:NC,NC:NV,NV:NU,NU:DM,DM:DB,DB:DT,DT
DM=CH,CH:VC,VC:NC,NC:NV,NV:NU,NU:DM,DM:DB,DB:DT,DT
FL=CH,CH:VC,VC:NC,NC:NV,NV:FL,FL:DB,DB:NU,NU:DM,DM
DB=CH,CH:VC,VC:NC,NC:NV,NV:DB,DB:NU,NU:DM,DM
DT=CH,CH:VC,VC:NC,NC:NV,NV:DT,DT:TS,TS
TS=CH,CH:VC,VC:NC,NC:NV,NV:DT,DT:TS,TS
TZ=CH,CH:VC,VC:NC,NC:NV,NV
IY=CH,CH:VC,VC:NC,NC:NV,NV
QD=CH,CH:VC,VC:NC,NC:NV,NV:NU,NU:DM,DM:DB,DB:DT,DT
TX=AD,AD
BL=AD,AD