;***********************************************************************
;Licensed Materials - Property of IBM
;
;BI and PM: UDA
;
;(C) Copyright IBM Corp. 2005, 2011, 2014
;
;
; Keith Bolam, Actian corporation, 2014
;Modified for the Trial of the recomendations from testing Vectorwise 3.0.1 and 
; Cognos 10.2.1 FP2 in preparation for Cognos 10.2.1 FP3 as advised by Xia Chen 
; Updated the prefix to 'VW301_FP3b' so that SQL can be distinguished.
;
;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.
;***********************************************************************

 
[DATABASE:INGRES_VECTORWISE]
Disable_Subquery_With_Parameters=T
Keyword Alias=" as \"%s\" "
SQL_API_SQLDESCRIBEPARAM="FALSE"
 
[Exceptions Commands DATABASE:INGRES_VECTORWISE]
Select="select "
Insert="insert "
Update="update "
Delete="delete "
Call=
Select_Star="T"
Max_Stmt_Len=""

[Exceptions Clauses DATABASE:INGRES_VECTORWISE]
From="from"
Where="where"
Group_By="Group By"
Having="having"
Window=
With=
With_Recursive=

[Exceptions Tables DATABASE:INGRES_VECTORWISE]
Derived=T
Joined=T
Simple_And_Joined=T
Tbl_Ref_Limit="126"
SubSelect_Limit=""
Table_Constructor=F
UniqueName_Prefix="VW301_FP3b"

[Exceptions Joins DATABASE:INGRES_VECTORWISE]
Inner=T
Left_Outer=T
Right_Outer=T
Full_Outer=T
Cross=T
On_Condition_Or_Predicate=T
Nested_Outer=T
Inner_Tbl_Restrict=F
Optnl_Tbl_Restrict=F
Non_Equi_Joins=T
One_Outer=F
Natural=F
Left_Nested=T
Right_Nested=T
Two_Sided_Join_Restrict=F
Optnl_Tbl_Join_Restrict=F
Optnl_Tbl_Join_Filter=T
Optnl_Tbl_Filter=T
Prsrv_Tbl_Join_Filter=T
Prsrv_Tbl_Filter=T
On_Condition_Set_Functions=T
On_Condition_Subqueries=T
Full_Non_Equi_Join=T
On_Condition_In_Predicate=T
On_Condition_Not_Predicate=T
On_Condition_Like_Predicate=T
On_Condition_Between_Predicate=T
On_Condition_IsNull_Predicate=T
Non_Join_Expr=T
Non_Join_Non_Equi_Expr=T
Only_Eql_With_And=F
On_Condition_Functions=T
Two_Sided_Join_Restrict_Filter=F
Nested_Inner=T
Product_Join=T
Outer_Non_Equi_Join=T
On_Condition_No_Expr=T
Outer_Syntax=T
Left_Pre=
;Left_Eql=" LEFT OUTER JOIN "
Left_Post=
Right_Pre=
;Right_Eql=" RIGHT OUTER JOIN "
Right_Post=
;Full_Syntax=" FULL OUTER JOIN "
Full_Pre=
Full_Post=
;Inner_Syntax=" INNER JOIN "
Inner_Col_Pre=
Inner_Col_Post=
;Cross_Syntax=" CROSS JOIN "
;Union_Syntax=" UNION "
;Natural_Syntax=" NATURAL JOIN "

[Exceptions Predicates DATABASE:INGRES_VECTORWISE]
Between="between"
In="in"
Like="like"
Is_Null="is null"
Any="any"
All="all"
Exists="exists"
NotLike="not like"

[Exceptions Aggregates DATABASE:INGRES_VECTORWISE]
Max="max"
Min="min"
Sum="sum"
Avg="avg"
Count="count"
Count_Star="count(*)"
Stddev="stddev_samp"
Stddev_Pop="stddev_pop"
Stddev_Samp="stddev_samp"
Variance="var_samp"
Var_Pop="var_pop"
Var_Samp="var_samp"
Grouping=
Rank=
Dense_Rank=
Percent_Rank=
Cume_Dist=
Percentile_Cont=
Percentile_Disc=
First_Value=
Last_Value=
Percentile=

[Exceptions OLAP Functions DATABASE:INGRES_VECTORWISE]
Olap_Min="min"
Olap_Max="max"
Olap_Sum=
Olap_Avg="avg"
Olap_Count="count"
Olap_Count_Star="count(*)"
Olap_Stddev="stddev_samp"
Olap_Stddev_Pop="stddev_pop"
Olap_Stddev_Samp="stddev_samp"
Olap_Variance="var_samp"
Olap_Var_Pop="var_pop"
Olap_Var_Samp="var_samp"
Olap_Rank="rank"
Olap_Dense_Rank="dense_rank"
Olap_Percent_Rank="percent_rank"
Olap_Cume_Dist=
Olap_Percentile_Cont=
Olap_Percentile_Disc=
; Vendor imposing ordering in window
Olap_First_Value=
; Vendor imposing ordering in window
Olap_Last_Value=
Olap_Row_Number="row_number"
Olap_Ratio_To_Report=
Olap_Ntile="ntile"
; Olap_Count_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
; Olap_Sum_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
; Olap_Last_Value_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"


[Exceptions OLAP Functions DATABASE:INGRES_VECTORWISE VERSION:02]
Olap_Min=
Olap_Max=
Olap_Sum=
Olap_Avg=
Olap_Count=
Olap_Count_Star=
Olap_Stddev=
Olap_Stddev_Pop=
Olap_Stddev_Samp=
Olap_Variance=
Olap_Var_Pop=
Olap_Var_Samp=
Olap_Ntile=
; Olap_Count_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
; Olap_Sum_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
; Olap_Last_Value_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"

[Exceptions OLAP Functions DATABASE:INGRES_VECTORWISE VERSION:01]
Olap_Min=
Olap_Max=
Olap_Sum=
Olap_Avg=
Olap_Count=
Olap_Count_Star=
Olap_Stddev=
Olap_Stddev_Pop=
Olap_Stddev_Samp=
Olap_Variance=
Olap_Var_Pop=
Olap_Var_Samp=
Olap_Ntile=
; Olap_Count_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
; Olap_Sum_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"
; Olap_Last_Value_Default="ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING"


[Exceptions Set Operators DATABASE:INGRES_VECTORWISE]
Union="union"
Union_All=T
Intersect=
Intersect_All=F
Except=
Except_All=F
Distinct="distinct"
Order_By="order by"

[Exceptions Operators DATABASE:INGRES_VECTORWISE]
Add="+"
Sub="-"
Mul="*"
Div="/"
Eql="="
Neq="<>"
Grt=">"
Geq=">="
Les="<"
Leq="<="
Neg="-"
And="and"
Or="or"
Not="not"
Assign="="

[Exceptions Value Expressions DATABASE:INGRES_VECTORWISE]
Case_Simple=T
Case_Searched=T
Coalesce=T
Nullif=T
Grouping_Sets=T
Rollup=T
Cube=T
Position=T
Char_Length=F
Octet_Length=F
Substring=T
Upper=T
Lower=T
StrCat=T
Cast=C
Abs=T
Floor=T
Ceiling=C
ln=T
Exp=T
Mod=T
Sqrt=T
Power=T
Current_Date=T
;Unable to get timezone type via ODBC so disabled
Current_Time=F
Current_TimeStamp=F
LocalTime=C
LocalTimestamp=C
Extract=T
Trim=F
Trim_Leading_Spaces=C
Trim_Trailing_Spaces=C
Trim_Both_Spaces=F
Row_Value_Constructors=F
Olap_Window_Order_By=T



[Exceptions Value Expressions DATABASE:INGRES_VECTORWISE VERSION:02]
Nullif=F
Grouping_Sets=F
Rollup=F
Cube=F
StrCat=F
Olap_Window_Order_By=F
; Unable to order by a literal value



[Exceptions Value Expressions DATABASE:INGRES_VECTORWISE VERSION:01]
Nullif=F
Grouping_Sets=F
Rollup=F
Cube=F
StrCat=F
Olap_Window_Order_By=F
; Unable to order by a literal value


 
[Exceptions General DATABASE:INGRES_VECTORWISE]
Aggregate_In_Expr=T
Aggregate_Value_Expr=T
Correlated_Subqueries=T
Cross_Product=T
Distinct_Aggregates=T
Distinct_Aggr_In_Case=T
Group_By_Expr=T
; New specification allows for the Group by Ordinal to be enabled but issues have been seen by Vendor.
; Therefore may not be enabled.
Group_By_Ordinal=T
Group_By_Alias=F
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=T
Subquery_In_Case=F
Count_Blob=T
Expression_In_In=T
Null_Order=T
Olap_Null_Order=T
Olap_Distinct=T
Nested_Olap_Functions=F
Empty_String_Is_Null=F
Order_By_In_Derived_Table=F
Interval_As_VarChar=F
Boolean_Comparison=T
Derived_Column_List=T
With_Column_List=F
Aggr_With_Interval=T
Nested_Case=T
Not_In_Subquery=T
Nulls_Sort_Last=T
Order_By_Alias=T
Order_By_Name=T
Substring_On_Expr=T
Subqueries=T
Subquery_Column_Alias=T
Parameter_In_Select_List=F
Remove_Parameter_Cast=T
Subquery_With_Parameters=T
Like_With_Parameters=T
Like_With_Escape=T
Comments=T
Integer_Division_Truncation=T

[Exceptions General DATABASE:INGRES_VECTORWISE VERSION:02]
Group_By_Ordinal=F
Subquery_In_Group_By=F
Null_Order=F
Olap_Null_Order=F
Olap_Distinct=F
Not_In_Subquery=F

[Exceptions General DATABASE:INGRES_VECTORWISE VERSION:01]
Group_By_Ordinal=F
Subquery_In_Group_By=F
Null_Order=F
Olap_Null_Order=F
Olap_Distinct=F
Not_In_Subquery=F
 
[Exceptions Literals DATABASE:INGRES_VECTORWISE]
Date_Literal=T
Timestamp_Literal=T
Time_Literal=T
; only returns two of the interval types
Interval_Literal=F
IntervalYM_Literal=F
TimeTZ_Literal=F
TimestampTZ_Literal=F
NChar_Literal=T

[Exceptions Literals DATABASE:INGRES_VECTORWISE VERSION:02]
; issues with national literals
NChar_Literal=F

[Exceptions Literals DATABASE:INGRES_VECTORWISE VERSION:01]
; issues with national literals
NChar_Literal=F
 
[Exceptions Misc DATABASE:INGRES_VECTORWISE]

[Exceptions Delimiters DATABASE:INGRES_VECTORWISE]
Comment_Begin="/* "
Comment_End=" */ "
 
[Exceptions Separators DATABASE:INGRES_VECTORWISE]
Catalog_Separator="."
Schema_Separator="."
Table_Separator="."
 
[Exceptions Blob Expressions DATABASE:INGRES_VECTORWISE]
Blob_In_Substring=T
Blob_In_Trim=T
Blob_In_StrCat=T
Blob_In_IsNull=T
Blob_In_Substring_Return_Blob=F
 
;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
 
[Database Functions DATABASE:INGRES_VECTORWISE]
n:ceiling(%1n)=CEIL(%1);
c:trim_leading_spaces(%1c)=trim (leading from %1);
c:trim_trailing_spaces(%1c)=trim (trailing from %1);


 
[Builtin Functions DATABASE:INGRES_VECTORWISE]
d:_add_days(%1d,%2n)=(%1 + int8(%2));
;d:_add_days(%1d,%2n)=(%1 + (interval '1' day * floor(%2)));
d:_add_months(%1d,%2n)=(add_months(%1 , floor(%2)));
d:_add_years(%1d,%2n)=(add_months(%1 , floor(%2) * 12));
d:_first_of_month(%1d)=(trunc(%1,'MM'));
d:_last_of_month(%1d)=(last_day(%1));
s:_add_days(%1s,%2n)=(%1 + (interval '1' day * floor(%2)));
s:_add_months(%1s,%2n)=(add_months(%1 , floor(%2)));
s:_add_years(%1s,%2n)=(add_months(%1 , floor(%2) * 12));
s:_first_of_month(%1s)=(cast(cast(ansidate(trunc(%1,'MM')) as char)||' '||char(time(%1))as timestamp without time zone));
s:_last_of_month(%1s)=(last_day(%1));
s:_make_timestamp(%1n,%2n,%3n)=(cast(TO_TIMESTAMP(CHAR(ANSIDATE('%1-%2-%3')),'YYYY-MM-DD') as timestamp without time zone));
;s:_add_hours(%1s,%2n)=(TIMESTAMPADD(HOUR,%2,%1));
;s:_add_minutes(%1s,%2n)=(TIMESTAMPADD(MINUTE,%2,%1));
;s:_add_seconds(%1s,%2n)=(TIMESTAMPADD(SECOND,%2,%1));
n:_days_between(%1ds,%2ds)=day((trunc(%1) - trunc(%2)));
;n:_months_between(%1ds,%2ds)=(months_between(%1,%2));
n:_years_between(%1ds,%2ds)=((year(%1) - year(%2)));
;n:_hours_between(%1ds,%2ds)=(TIMESTAMPDIFF(HOUR,%2,%1));
;n:_minutes_between(%1ds,%2ds)=(TIMESTAMPDIFF(MINUTE,%2,%1));
;n:_seconds_between(%1ds,%2ds)=(TIMESTAMPDIFF(SECOND,%2,%1));
n:_days_to_end_of_month(%1ds)=(cast(last_day(%1) as date) - cast(%1 as date));
n:_day_of_year(%1ds)=(dayofyear(%1));
;n:_day_of_week(%1ds, %2n)=(dayofweek(%1,%2));
;n:_age(%1ds)=;
;n:_ymdint_between(%1ds,%2ds)=;
; must be iso 8601 compliant
n:_week_of_year(%1ds)=(week_iso(%1));
; added on advice from Vinhson.Nguyen (IBM)
n:_round(%1n, %2n)=(ROUND(%1,%2)); 
n:round(%1n, %2n)=(ROUND(%1,%2)); 




t:localtime({%1n})=cast(current_time as time);
s:localtimestamp()=cast(current_timestamp as timestamp);

[Builtin Functions DATABASE:INGRES_VECTORWISE VERSION:02]
d:_add_months(%1d,%2n)=(%1 + interval '1' month * floor(%2));
d:_add_years(%1d,%2n)=(%1 + interval '1' year * floor(%2));
d:_first_of_month(%1d)=(cast(year(%1)||'-'||month(%1)||'-01'as date));
d:_last_of_month(%1d)=(cast(year(%1)||'-'||month(%1)||'-01'as date)+interval '1' month - interval '1' day);
s:_add_days(%1s,%2n)=(timestamp(%1)+ interval '1' day * floor(%2));
s:_add_months(%1s,%2n)=(timestamp(%1)+(interval '1' month * floor(%2)));
s:_add_years(%1s,%2n)=(timestamp(%1)+(interval '1' year * floor(%2)));
s:_first_of_month(%1s)=(cast(year(%1)||'-'||month(%1)||'-01'||' '||char(time(%1))as timestamp));
s:_last_of_month(%1s)=(cast(year(%1)||'-'||month(%1)||'-01'||' '||char(time(%1))as timestamp)+ interval '1' month - interval '1' day);
;s:_make_timestamp(%1n,%2n,%3n)=;
s:_add_hours(%1s,%2n)=(%1+(interval '1' hour * floor(%2)));
s:_add_minutes(%1s,%2n)=(%1+(interval '1' minute * floor(%2)));
s:_add_seconds(%1s,%2n)=(%1+(interval '1' second * floor(%2)));
n:_days_between(%1ds,%2ds)=day(timestamp(%1)-timestamp(%2));
n:_months_between(%1ds,%2ds)=(12*(year(%1)-year(%2))+month(%1)-month(%2)-(CASE WHEN day(%2)>day(%1)THEN 1 ELSE 0 END));
n:_years_between(%1ds,%2ds)=((year(%1)-year(%2)));
;n:_hours_between(%1ds,%2ds)=;
;n:_minutes_between(%1ds,%2ds)=;
;n:_seconds_between(%1ds,%2ds)=;
n:_days_to_end_of_month(%1ds)=(day((cast(YEAR(%1)||'-'||MONTH(%1)||'-01'as date)+ INTERVAL '1' MONTH - INTERVAL '1' day))-day(%1));
n:_day_of_year(%1ds)=(day(ansidate(%1)-ansidate(year(%1)||'-01-01'))+1);
n:_day_of_week(%1ds,%2n)=(mod(int(extract('dayofweek',%1)+6-%2),7)+1);
;n:_age(%1ds)=;
;n:_ymdint_between(%1ds,%2ds)=;
; must be iso 8601 compliant

[Builtin Functions DATABASE:INGRES_VECTORWISE VERSION:01]
d:_add_months(%1d,%2n)=(%1 + interval '1' month * floor(%2));
d:_add_years(%1d,%2n)=(%1 + interval '1' year * floor(%2));
d:_first_of_month(%1d)=(cast(year(%1)||'-'||month(%1)||'-01'as date));
d:_last_of_month(%1d)=(cast(year(%1)||'-'||month(%1)||'-01'as date)+interval '1' month - interval '1' day);
s:_add_days(%1s,%2n)=(timestamp(%1)+ interval '1' day * floor(%2));
s:_add_months(%1s,%2n)=(timestamp(%1)+(interval '1' month * floor(%2)));
s:_add_years(%1s,%2n)=(timestamp(%1)+(interval '1' year * floor(%2)));
s:_first_of_month(%1s)=(cast(year(%1)||'-'||month(%1)||'-01'||' '||char(time(%1))as timestamp));
s:_last_of_month(%1s)=(cast(year(%1)||'-'||month(%1)||'-01'||' '||char(time(%1))as timestamp)+ interval '1' month - interval '1' day);
;s:_make_timestamp(%1n,%2n,%3n)=;
s:_add_hours(%1s,%2n)=(%1+(interval '1' hour * floor(%2)));
s:_add_minutes(%1s,%2n)=(%1+(interval '1' minute * floor(%2)));
s:_add_seconds(%1s,%2n)=(%1+(interval '1' second * floor(%2)));
n:_days_between(%1ds,%2ds)=day(timestamp(%1)-timestamp(%2));
n:_months_between(%1ds,%2ds)=(12*(year(%1)-year(%2))+month(%1)-month(%2)-(CASE WHEN day(%2)>day(%1)THEN 1 ELSE 0 END));
n:_years_between(%1ds,%2ds)=((year(%1)-year(%2)));
;n:_hours_between(%1ds,%2ds)=;
;n:_minutes_between(%1ds,%2ds)=;
;n:_seconds_between(%1ds,%2ds)=;
n:_days_to_end_of_month(%1ds)=(day((cast(YEAR(%1)||'-'||MONTH(%1)||'-01'as date)+ INTERVAL '1' MONTH - INTERVAL '1' day))-day(%1));
n:_day_of_year(%1ds)=(day(ansidate(%1)-ansidate(year(%1)||'-01-01'))+1);
n:_day_of_week(%1ds,%2n)=(mod(int(extract('dayofweek',%1)+6-%2),7)+1);
;n:_age(%1ds)=;
;n:_ymdint_between(%1ds,%2ds)=;
; must be iso 8601 compliant



[Operator Addition DATABASE:INGRES_VECTORWISE]
 
[Operator Subtraction DATABASE:INGRES_VECTORWISE]
 
[Operator Multiplication DATABASE:INGRES_VECTORWISE]
 
[Operator Division DATABASE:INGRES_VECTORWISE]
 
[Operator Comparison DATABASE:INGRES_VECTORWISE]

[Operator Cast DATABASE:INGRES_VECTORWISE]
AD=AD,NA
BL=AD,NA
CH=CH,CH:DB,DB:DM,DM:DT,DT:FL,FL:IT,IT:NC,NC:NU,NU:QD,QD:SM,SM:TM,TM:TS,TS:VC,VC
DB=DB,DB:DM,DM:FL,FL:IT,IT:NU,NU:QD,QD:SM,SM
DM=CH,CH:DB,DB:DM,DM:FL,FL:IT,IT:NC,NC:NU,NU:QD,QD:SM,SM:VC,VC
DT=CH,CH:DT,DT:NC,NC:TS,TS:VC,VC
FL=DB,DB:DM,DM:FL,FL:IT,IT:NU,NU:QD,QD:SM,SM
IT=CH,CH:DB,DB:DM,DM:FL,FL:IT,IT:NC,NC:NU,NU:QD,QD:SM,SM:VC,VC
IV=AD,NA
IY=AD,NA
NA=CH,CH:DB,DB:DM,DM:DT,DT:FL,FL:IT,IT:NC,NC:NU,NU:QD,QD:SM,SM:TM,TM:TS,TS:VC,VC
NC=CH,CH:DB,DB:DT,DT:FL,FL:IT,IT:NC,NC:QD,QD:SM,SM:TS,TS:VC,VC
NU=CH,CH:DB,DB:DM,DM:FL,FL:IT,IT:NC,NC:NU,NU:QD,QD:SM,SM:VC,VC
QD=CH,CH:DB,DB:DM,DM:FL,FL:IT,IT:NC,NC:NU,NU:QD,QD:SM,SM:VC,VC
SM=CH,CH:DB,DB:DM,DM:FL,FL:IT,IT:NC,NC:NU,NU:QD,QD:SM,SM:VC,VC
TM=NC,NC:TM,TM
TS=CH,CH:DT,DT:TM,TM:TS,TS
TT=AD,NA
TZ=AD,NA
VC=CH,CH:DB,DB:DM,DM:DT,DT:FL,FL:IT,IT:NC,NC:NU,NU:QD,QD:SM,SM:TM,TM:TS,TS:VC,VC

[Directives Session DATABASE:INGRES_VECTORWISE]