# Licensed Materials - Property of IBM # IBM Cognos Products: OQP # (C) Copyright IBM Corp. 2005, 2022 # US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM corp. # This properties file contains default configuration attributes for all # relational data sources. Any data source that is different must override # the value in their own properties file. # # The #define values in comments are the values used by UDA to initialize # their dbInfo data structure. We are using this as a starting point for # the XQE default values. At some point these can be removed from this file. # # Function parameter types are defined using the following domains/types: # # domain types # ------ ----- # any any data type. Use with care! # binary binary # blob blob, clob # boolean boolean # collection row, array, multiset # datetime date, time, time_with_time_zone, timestamp, timestamp_with_time_zone # interval_day_time interval_day, interval_day_to_hour, interval_day_to_minute, interval_day_to_second # interval_hour, interval_hour_to_minute, interval_hour_to_second # interval_minute, interval_minute_to_second # interval_second # interval_year_month interval_year, interval_year_to_month # interval_month # numeric decimal, double, float, integer, levelNumber, long, smallint # exact decimal, integer, levelNumber, long, smallint # text businessKey, char, currencyCode, dimensionUniqueName, externalBuffer, # hierarchyUniqueName, levelLabel, levelUniqueName, memberCaption, # memberDescription, memberRollup, memberUniqueName, nchar, nvarchar, # parentUniqueName, queryItemModelID, string, varchar, xml # variant variant # # The natural function overloads are listed herein, but with empty patterns # to act as a reference when creating overrides in DB specific properties files. # # Product information. # product.name= # # Delimiters. # delimiters.catalogDelimiter= delimiters.schemaDelimiter= delimiters.tableDelimiter= delimiters.columnDelimiter= delimiters.identifierQuoteString= delimiters.searchStringEscape= delimiters.catalogSeparator= delimiters.literalQuoteEscape= delimiters.commentBegin=/* delimiters.commentEnd=*/ # # Keywords. # keywords.columnAlias=AS # # Various limits. A value of 0 means no limit, or the limit is unknown. # limits.maxBinaryLiteralLength=0 limits.maxCharLiteralLength=0 limits.maxColumnNameLength=0 limits.maxColumnsInGroupBy=0 limits.maxColumnsInIndex=0 limits.maxColumnsInOrderBy=0 limits.maxColumnsInSelect=0 limits.maxColumnsInTable=0 limits.maxConnections=0 limits.maxCursorNameLength=0 limits.maxIndexLength=0 limits.maxSchemaNameLength=0 limits.maxProcedureNameLength=0 limits.maxCatalogNameLength=0 limits.maxRowSize=0 limits.maxStatementLength=0 limits.maxStatements=0 limits.maxTableNameLength=0 limits.maxTablesInSelect=0 limits.maxUserNameLength=0 limits.defaultTransactionIsolation=0 limits.maxLengthInClause=0 limits.castClobToVarcharMaxSize=1024 limits.maxDecimalPrecision=31 # # General settings. # general.nullsAreSortedHigh=false general.nullsAreSortedLow=false general.nullsAreSortedAtStart=false general.nullsAreSortedAtEnd=false general.nullsOrdering=true general.nullsOrderingInWindowSpecification=true # # Cursor options - appended to end of generated SELECT statement. general.cursorOptions= # # RTC 61393 # Indicates whether to set nulls ok indicator to true when processing JDBC result set metadata. # This is set to true only if the value returned by invoking java.sql.ResultSetMetadata.isNullable() is # possibly incorrect. # general.forceNullsOk=false # # Override sampling policy with a different one. # 1. tablesample accepting values such as BERNOULLI or SYSTEM # 2. rowsample accepting values such as NTH or RANDOM # sampling.tablesample=BERNOULLI sampling.rowsample=NTH # # Various features. # supports.duplicateColumnsInSelectList=true supports.duplicateColumnNamesInSelectList=true supports.columnAliasing=true supports.tableCorrelationNames=true supports.expressionsInOrderBy=true supports.expressionsInOrderByOnGroupedQuery=true supports.distinct.WindowAggregation=true # Determines whether the expression should be built off of the FID # of the table expression (true), or the expression of the underlying # projection that the FID refers to (false) supports.aliasInOrderByExpression=true supports.expressionsInINPredicate=true supports.expressionsInSelectList=true supports.literalsInSelectList=true supports.expressionsInLikePattern=true supports.booleanExpressionsInSelectList=true supports.fieldsOfComplexTypeInSelectList=true supports.likeEscapeClause=true supports.outerJoins=true supports.fullOuterJoins=true supports.limitedOuterJoins=false supports.subqueriesInComparisons=true supports.subqueriesInExists=true supports.subqueriesInIns=true supports.subqueriesInQuantifieds=true supports.subqueriesInCase=true supports.subqueriesInAggregate=true supports.subqueriesInGroupBy=true supports.correlatedSubqueries=true supports.correlatedSubqueriesInSelectList=true supports.correlatedSubqueriesInIns=true supports.transactions=true supports.withClauseInDerivedTable=true supports.nestedWithClause=true supports.integerDivision=true supports.nestedOlap=false supports.groupedWindowedTables=true supports.derivedColumnLists=true supports.orderByAlias=true supports.orderByName=true supports.orderByOrdinal=true supports.groupByAlias=false supports.blobsInGroupBy=false supports.blobsInOrderBy=false supports.scalarSubqueries=true supports.emptyStringIsNull=false supports.concatNullIsNull=true supports.sqlserverWindowBehaviour=false supports.mixedCaseIdentifiers=false supports.mixedCaseQuotedIdentifiers=false supports.charLiteralRoundTrip=true supports.expressionsInGroupBy=true supports.equiJoins=true supports.thetaJoins=true supports.stitchJoins=true supports.crossProducts=true supports.rewriteImplicitCrossJoins=false supports.recursiveWithClause=true supports.storedProcedureColumnUnique=true supports.implicitTypeConversion=true supports.orderByUnrelated=true supports.groupByUnrelated=true supports.schemasInDataManipulation=true #classic stack compatibility supports.nonStandardDatetimeComparison=false supports.multipleDistinctAggregates=true supports.hints=true supports.rowNumberNoOrderBy=false supports.parameterMarkers=true supports.constantsInWindows=true supports.orderByInDerivedTable=true supports.callProcedureInDerivedTable=true #join condition supports.join.betweenInOnClause=true supports.join.inPredicateInOnClause=true supports.join.isNullInOnClause=true supports.join.likeInOnClause=true supports.join.notInOnClause=true supports.join.orInOnClause=true supports.join.subqueriesInOnClause=true supports.join.onlyEquiWithAnd=false supports.join.inner.limitedThetaJoins=false supports.join.outer.thetaJoins=true supports.join.full.thetaJoins=true supports.join.full.distinctJoins=true supports.castClobToVarcharWithoutSubstring=false #isolation level support supports.isolationLevelReadUncommitted=true supports.isolationLevelReadCommitted=true supports.isolationLevelRepeatableRead=true supports.isolationLevelSerializable=true supports.constantsInCount=true supports.columnFiltersOnly=false supports.hanaInputParameters=false supports.top.distinct=true supports.top.set=true supports.top.0=true #casting with formatting pattern support supports.formatters.string_to_date=true supports.formatters.string_to_time=true supports.formatters.string_to_time_with_time_zone=true supports.formatters.string_to_timestamp=true supports.formatters.string_to_timestamp_with_time_zone=true #preparedstatement supports.preparedstatement=true # APAR 121556 # If true, the db connection associated with a request is no longer # reuseable for subsequent requests. For certain dbs, this happens # after a cancel request is sent to the db. supports.connectionDroppedOnCancel=false # # Performance properties (force certain transformations to be applied). # performance.convertGroupByToDistinct=false performance.convertHavingToWhere=false performance.convertDistinctToGroupBy=false performance.transitiveClosure=false performance.predicatePushdown=false performance.semiJoin=false # RTC 377496 # Set this entry to F to avoid generation of predicates of the form # T1.C1 = T2.C1 OR ( T1.C1 IS NULL AND T2.C1 IS NULL ). Care must be # taken, however, since doing so may cause data integrity problems if # data contains null values. performance.generateEqualOrNull=true # # Command. # commands.Select=SELECT commands.Call=CALL # # Tables. # tables.derived=true tables.lateral.derived=true tables.joined=true # # Constructors. # constructors.table=true constructors.row=true constructors.array=true constructors.period=true constructors.map=true # # Constructors - context overrides. # constructors.row.between=true constructors.row.comparison=true constructors.row.in=true constructors.row.isDistinctFrom=true constructors.row.simpleCase=true #DB2 supports table value constructor in IN clause, but not row expression list. constructors.row.inListToTable=false # # Clauses. # clauses.From=FROM clauses.Where=WHERE clauses.GroupBy=GROUP BY clauses.Having=HAVING clauses.With=WITH clauses.WithRecursive=WITH RECURSIVE clauses.OrderBy=ORDER BY clauses.Distinct=DISTINCT clauses.Top=FETCH FIRST %1$s ROWS ONLY clauses.Top.Position=standard clauses.At=AT clauses.Window=WINDOW clauses.TableSampleBeforeAlias=false clauses.TableSampleSystem=TABLESAMPLE SYSTEM (%1$s)@2[ REPEATABLE (%2$s)] clauses.TableSampleBernoulli=TABLESAMPLE BERNOULLI (%1$s)@2[ REPEATABLE (%2$s)] clauses.ForSystemTimeAsOf=FOR SYSTEM_TIME AS OF %1$s clauses.ForSystemTimeFrom=FOR SYSTEM_TIME FROM %1$s TO %2$s clauses.ForSystemTimeBetween=FOR SYSTEM_TIME BETWEEN %1$s AND %2$s # # Joins. # joins.Cross=%1$s CROSS JOIN %2$s joins.Inner=%1$s INNER JOIN %2$s ON %3$s joins.LeftOuter=%1$s LEFT OUTER JOIN %2$s ON %3$s joins.RightOuter=%1$s RIGHT OUTER JOIN %2$s ON %3$s joins.FullOuter=%1$s FULL OUTER JOIN %2$s ON %3$s joins.RightNested=%1$s # Bracket inner join groups in order to avoid parsing error # A LOJ # B INNER JOIN C ON B.x = C.x # ON A.x = B.x # is converted to: # A LOJ # (B INNER JOIN C ON B.x = C.x) # ON A.x = B.x # joins.BracketInner=false # # Set operators. # operators.set.Union=%1$s UNION %2$s operators.set.Union.all=%1$s UNION ALL %2$s operators.set.Intersect=%1$s INTERSECT %2$s operators.set.Intersect.all=%1$s INTERSECT ALL %2$s operators.set.Except=%1$s EXCEPT %2$s operators.set.Except.all=%1$s EXCEPT ALL %2$s # # Logical operators. # operators.logical.And=%1$s AND %2$s operators.logical.Or=%1$s OR %2$s operators.logical.Not=NOT ( %1$s ) operators.logical.Is=( %1$s ) IS %2$s operators.logical.IsNot=( %1$s ) IS NOT %2$s operators.logical.IsJson=( %1$s ) IS JSON operators.logical.IsNotJson=( %1$s ) IS NOT JSON # # Arithmetic operators. # operators.arithmetic.Add[any,any]=%1$s + %2$s operators.arithmetic.Subtract[any,any]=%1$s - %2$s operators.arithmetic.Multiply[any,any]=%1$s * %2$s operators.arithmetic.Divide[any,any]=%1$s / %2$s operators.arithmetic.Negate[any]=-%1$s operators.arithmetic.UnaryPlus[any]=+%1$s operators.arithmetic.Concat[any,any]=%1$s || %2$s # # Group By Operators # operators.groupBy.Rollup=ROLLUP operators.groupBy.Cube=CUBE operators.groupBy.GroupingSets=GROUPING SETS # # Comparison predicates. # predicates.comparison.LessThan[any,any]=%1$s < %2$s predicates.comparison.LessThanOrEquals[any,any]=%1$s <= %2$s predicates.comparison.Equals[any,any]=%1$s = %2$s predicates.comparison.NotEquals[any,any]=%1$s <> %2$s predicates.comparison.GreaterThan[any,any]=%1$s > %2$s predicates.comparison.GreaterThanOrEquals[any,any]=%1$s >= %2$s # # Various predicates. # predicates.Between[any,any,any]=%1$s BETWEEN %2$s AND %3$s predicates.In[any,any]=%1$s IN ( %2$s ) predicates.Overlaps[any,any,any,any]=(%1$s, %2$s) OVERLAPS (%3$s, %4$s) predicates.IsNull=%1$s IS NULL predicates.IsNotNull=%1$s IS NOT NULL predicates.Like=%1$s LIKE %2$s predicates.Like.escape=%1$s LIKE %2$s ESCAPE %3$s predicates.LikeRegex=%1$s LIKE_REGEX %2$s predicates.LikeRegex.flag=%1$s LIKE_REGEX %2$s FLAG %3$s predicates.Similar=%1$s SIMILAR TO %2$s predicates.Similar.escape=%1$s SIMILAR TO %2$s ESCAPE %3$s predicates.Exists=EXISTS %1$s predicates.All=ALL %1$s predicates.Any=ANY %1$s predicates.Some=SOME %1$s predicates.IsDistinctFrom[any,any]=%1$s IS DISTINCT FROM %2$s predicates.IsNotDistinctFrom[any,any]=%1$s IS NOT DISTINCT FROM %2$s # # Period predicates. # predicates.PeriodOverlaps[any,any]=%1$s OVERLAPS %2$s predicates.PeriodEquals[any,any]=%1$s EQUALS %2$s predicates.PeriodContains[any,any]=%1$s CONTAINS %2$s predicates.PeriodPrecedes[any,any]=%1$s PRECEDES %2$s predicates.PeriodSucceeds[any,any]=%1$s SUCCEEDS %2$s predicates.PeriodImmediatelyPrecedes[any,any]=%1$s IMMEDIATELY PRECEDES %2$s predicates.PeriodImmediatelySucceeds[any,any]=%1$s IMMEDIATELY SUCCEEDS %2$s # # Expressions. # expressions.SearchedCase=CASE expressions.SimpleCase=CASE # NULLIF(%1$s, %2$s) is equivalent to CASE WHEN %1$s = %2$s THEN NULL ELSE %1$s END expressions.NullIf=NULLIF(%1$s, %2$s) expressions.Coalesce=COALESCE(%1$s) expressions.SearchedCase.compatibleResults=true # Minimum number of arguments for Coalesce function. expressions.Coalesce.minArgs=1 # # Cast expression. # expressions.Cast[any,any]=CAST(%1$s AS %2$s) expressions.Cast[any,any,any]=CAST(%1$s AS %2$s %5$s) # # Extract expression. # expressions.Extract.YEAR[any]=EXTRACT(YEAR FROM %1$s) expressions.Extract.MONTH[any]=EXTRACT(MONTH FROM %1$s) expressions.Extract.DAY[any]=EXTRACT(DAY FROM %1$s) expressions.Extract.HOUR[any]=EXTRACT(HOUR FROM %1$s) expressions.Extract.MINUTE[any]=EXTRACT(MINUTE FROM %1$s) expressions.Extract.SECOND[any]=EXTRACT(SECOND FROM %1$s) expressions.Extract.TIMEZONE_HOUR[any]=EXTRACT(TIMEZONE_HOUR FROM %1$s) expressions.Extract.TIMEZONE_MINUTE[any]=EXTRACT(TIMEZONE_MINUTE FROM %1$s) expressions.Extract.EPOCH[any]=EXTRACT(EPOCH FROM %1$s) # # Trim expression. # expressions.Trim.BOTH[any]=TRIM(BOTH FROM %1$s) expressions.Trim.LEADING[any]=TRIM(LEADING FROM %1$s) expressions.Trim.TRAILING[any]=TRIM(TRAILING FROM %1$s) expressions.Trim.BOTH[any,any]=TRIM(BOTH %1$s FROM %2$s) expressions.Trim.LEADING[any,any]=TRIM(LEADING %1$s FROM %2$s) expressions.Trim.TRAILING[any,any]=TRIM(TRAILING %1$s FROM %2$s) # # Windowed aggregates (SQL/OLAP). # olap.Max[any]=MAX(%1$s) olap.Min[any]=MIN(%1$s) olap.Sum[any]=SUM(%1$s) olap.Avg[any]=AVG(%1$s) olap.Count[any]=COUNT(%1$s) olap.CountStar[]=COUNT(*) olap.StdDevSamp[any]=STDDEV_SAMP(%1$s) olap.StdDevPop[any]=STDDEV_POP(%1$s) olap.VarSamp[any]=VAR_SAMP(%1$s) olap.VarPop[any]=VAR_POP(%1$s) olap.Rank[]=RANK() olap.DenseRank[]=DENSE_RANK() olap.PercentRank[]=PERCENT_RANK() olap.CumeDist[]=CUME_DIST() olap.PercentileCont[any,any]=PERCENTILE_CONT(%1$s) WITHIN GROUP (ORDER BY %2$s) olap.PercentileDisc[any,any]=PERCENTILE_DISC(%1$s) WITHIN GROUP (ORDER BY %2$s) olap.Median[any]=MEDIAN(%1$s) olap.RowNumber[]=ROW_NUMBER() olap.FirstValue[any]=FIRST_VALUE(%1$s) olap.LastValue[any]=LAST_VALUE(%1$s) olap.NTile[any]=NTILE(%1$s) olap.Tertile[]=TERTILE() olap.RatioToReport[any]=RATIO_TO_REPORT(%1$s) olap.Difference[any]=DIFFERENCE(%1$s) olap.Lag[any]=LAG(%1$s) olap.Lag[any,any]=LAG(%1$s, %2$s) olap.Lag[any,any,any]=LAG(%1$s, %2$s, %3$s) olap.Lag[any,any,any,any]=LAG(%1$s, %2$s, %3$s) %4$s olap.Lead[any]=LEAD(%1$s) olap.Lead[any,any]=LEAD(%1$s, %2$s) olap.Lead[any,any,any]=LEAD(%1$s, %2$s, %3$s) olap.Lead[any,any,any,any]=LEAD(%1$s, %2$s, %3$s) %4$s olap.NthValue[any,any]=NTH_VALUE(%1$s, %2$s) olap.NthValue[any,any,any]=NTH_VALUE(%1$s, %2$s) %3$s olap.NthValue[any,any,any,any]=NTH_VALUE(%1$s, %2$s) %3$s %4$s olap.Collect[any]=COLLECT(%1$s) # # Window clause. # olap.Window=OVER(%1$s) olap.PartitionBy=PARTITION BY %1$s olap.OrderBy=ORDER BY %1$s # # Window specification # A list of windows specifications that are supported by the DB # P = PARTITION BY # O = ORDER BY # F = FRAME # These settings are NOT consulted for the following functions: # RANK, DENSE_RANK, PERCENT_RANK, LAG, LEAD, NTILE and ROW_NUMBER. # olap.Window.Specification[POF]=true olap.Window.Specification[PO]=true olap.Window.Specification[OF]=true olap.Window.Specification[PF]=true olap.Window.Specification[P]=true olap.Window.Specification[O]=true olap.Window.Specification[F]=true olap.Window.Specification[]=true olap.Window.Frame.Moving=true # # Olap (distinct). # olap.Max.distinct[any]=MAX(DISTINCT %1$s) olap.Min.distinct[any]=MIN(DISTINCT %1$s) olap.Sum.distinct[any]=SUM(DISTINCT %1$s) olap.Avg.distinct[any]=AVG(DISTINCT %1$s) olap.Count.distinct[any]=COUNT(DISTINCT %1$s) # # Aggregates. # aggregates.Max[any]=MAX(%1$s) aggregates.Min[any]=MIN(%1$s) aggregates.Sum[any]=SUM(%1$s) aggregates.Avg[any]=AVG(%1$s) aggregates.Count[any]=COUNT(%1$s) aggregates.CountStar[]=COUNT(*) aggregates.StdDevSamp[any]=STDDEV_SAMP(%1$s) aggregates.StdDevPop[any]=STDDEV_POP(%1$s) aggregates.VarSamp[any]=VAR_SAMP(%1$s) aggregates.VarPop[any]=VAR_POP(%1$s) aggregates.Rank[any,any]=RANK(%1$s) WITHIN GROUP (ORDER BY %2$s) aggregates.DenseRank[any,any]=DENSE_RANK(%1$s) WITHIN GROUP (ORDER BY %2$s) aggregates.PercentRank[any,any]=PERCENT_RANK(%1$s) WITHIN GROUP (ORDER BY %2$s) aggregates.CumeDistH[any,any]=CUME_DIST(%1$s) WITHIN GROUP (ORDER BY %2$s) aggregates.PercentileCont[any,any]=PERCENTILE_CONT(%1$s) WITHIN GROUP (ORDER BY %2$s) aggregates.PercentileDisc[any,any]=PERCENTILE_DISC(%1$s) WITHIN GROUP (ORDER BY %2$s) aggregates.Median[any]=MEDIAN(%1$s) aggregates.XMLAgg=XMLAGG(%1$s@2[ ORDER BY %2$s]) aggregates.Grouping[any]=GROUPING(%1$s) aggregates.ArrayAgg[any]=ARRAY_AGG(%1$s) aggregates.ArrayAgg[any,any]=ARRAY_AGG(%1$s ORDER BY %2$s) aggregates.Collect[any]=COLLECT(%1$s) aggregates.ApproxCountDistinct[any]=APPROX_COUNT_DISTINCT(%1$s) # # Aggregates (distinct). # aggregates.Max.distinct[any]=MAX(DISTINCT %1$s) aggregates.Min.distinct[any]=MIN(DISTINCT %1$s) aggregates.Sum.distinct[any]=SUM(DISTINCT %1$s) aggregates.Avg.distinct[any]=AVG(DISTINCT %1$s) aggregates.Count.distinct[any]=COUNT(DISTINCT %1$s) # # Linear regression aggregates. # aggregates.Corr[any,any]=CORR(%1$s, %2$s) aggregates.CovarPop[any,any]=COVAR_POP(%1$s, %2$s) aggregates.CovarSamp[any,any]=COVAR_SAMP(%1$s, %2$s) aggregates.RegrAvgX[any,any]=REGR_AVGX(%1$s, %2$s) aggregates.RegrAvgY[any,any]=REGR_AVGY(%1$s, %2$s) aggregates.RegrCount[any,any]=REGR_COUNT(%1$s, %2$s) aggregates.RegrIntercept[any,any]=REGR_INTERCEPT(%1$s, %2$s) aggregates.RegrR2[any,any]=REGR_R2(%1$s, %2$s) aggregates.RegrSlope[any,any]=REGR_SLOPE(%1$s, %2$s) aggregates.RegrSXX[any,any]=REGR_SXX(%1$s, %2$s) aggregates.RegrSXY[any,any]=REGR_SXY(%1$s, %2$s) aggregates.RegrSYY[any,any]=REGR_SYY(%1$s, %2$s) # # JSON aggregates. # aggregates.JSONArrayAgg=JSON_ARRAYAGG(%1$s@2[ ORDER BY %1$s]) aggregates.JSONObjectAgg=JSON_OBJECTAGG(%1$s, %2$s@3[ ORDER BY %3$s]]) # # Character scalar functions. # functions.CharLength[any]=CHAR_LENGTH(%1$s) functions.OctetLength[any]=OCTET_LENGTH(%1$s) functions.BitLength[any]=BIT_LENGTH(%1$s) functions.Upper[any]=UPPER(%1$s) functions.Lower[any]=LOWER(%1$s) functions.Substring[any,any]=SUBSTRING(%1$s FROM %2$s) functions.Substring[any,any,any]=SUBSTRING(%1$s FROM %2$s FOR %3$s) functions.Position[any,any]=POSITION(%1$s IN %2$s) functions.Index[any,any]=INDEX(%1$s, %2$s) functions.Ascii[any]= functions.Translate[any,any]=TRANSLATE(%1$s USING %2$s) functions.Normalize[any]=NORMALIZE(%1$s) functions.Normalize[any,any]=NORMALIZE(%1$s, %2$s) functions.Normalize[any,any,any]=NORMALIZE(%1$s, %2$s, %3$s) #Substring function to negative START value to parse the input string from its rightmost end. #It's not a standard SQL function, so leave the definition empty. functions.SubstringR[any,any]= functions.SubstringR[any,any,any]= # # Regular expression functions. # functions.SubstringRegex[any,any,any,any,any]=SUBSTRING_REGEX(%1$s@5[ FLAG %5$s] IN %2$s@3[ FROM %3$s]@4[ OCCURRENCE %4$s]) functions.OccurrencesRegex[any,any,any,any]=OCCURRENCES_REGEX(%1$s@4[ FLAG %4$s] IN %2$s@3[ FROM %3$s]) functions.PositionRegex[any,any,any,any,any,any]=POSITION_REGEX(@1[%1$s ]%2$s@6[ FLAG %6$s] IN %3$s@4[ FROM %4$s]@5[ OCCURRENCE %5$s]) # # Numeric scalar functions. # functions.Abs[any]=ABS(%1$s) functions.Ceiling[any]=CEILING(%1$s) functions.Exp[any]=EXP(%1$s) functions.Floor[any]=FLOOR(%1$s) functions.Ln[any]=LN(%1$s) functions.Log10[any]=LOG10(%1$s) functions.Mod[any,any]=MOD(%1$s, %2$s) functions.Power[any,any]=POWER(%1$s, %2$s) functions.Random[]=RANDOM() functions.Random[any]=RANDOM(%1$s) functions.Round[any]=ROUND(%1$s) functions.Round[any,any]=ROUND(%1$s, %2$s) functions.Round[any,any,any]=ROUND(%1$s, %2$s, %3$s) functions.Sign[any]=SIGN(%1$s) functions.Sqrt[any]=SQRT(%1$s) functions.WidthBucket[any,any,any,any]=WIDTH_BUCKET(%1$s, %2$s, %3$s, %4$s) # # Array scalar functions. # functions.Cardinality[any]=CARDINALITY(%1$s) functions.TrimArray[any,any]=TRIM_ARRAY(%1$s, %2$s) # # Trigonometric functions. # functions.Arccos[any]=ACOS(%1$s) functions.Arcsin[any]=ASIN(%1$s) functions.Arctan[any]=ATAN(%1$s) functions.Cos[any]=COS(%1$s) functions.Coshyp[any]=COSH(%1$s) functions.Sin[any]=SIN(%1$s) functions.Sinhyp[any]=SINH(%1$s) functions.Tan[any]=TAN(%1$s) functions.Tanhyp[any]=TANH(%1$s) # # Datetime value functions. # functions.CurrentDate[]=CURRENT_DATE functions.CurrentTime[]=CURRENT_TIME functions.CurrentTimestamp[]=CURRENT_TIMESTAMP functions.LocalTime[]=LOCALTIME functions.LocalTimestamp[]=LOCALTIMESTAMP functions.CurrentTime[numeric]=CURRENT_TIME(%1$s) functions.CurrentTimestamp[numeric]=CURRENT_TIMESTAMP(%1$s) functions.LocalTime[numeric]=LOCALTIME(%1$s) functions.LocalTimestamp[numeric]=LOCALTIMESTAMP(%1$s) # # XML functions. # functions.XMLAttributes=XMLATTRIBUTES(%1$s) functions.XMLComment=XMLCOMMENT(%1$s) functions.XMLConcat=XMLCONCAT(%1$s) functions.XMLDocument=XMLDOCUMENT(%1$s) functions.XMLElement=XMLELEMENT(NAME %1$s@2[, %2$s]@3[, %3$s]@5[, %5$s]@4[ OPTION %4$s]) functions.XMLExists=XMLEXISTS(%1$s@2[ PASSING %2$s]) functions.XMLForest=XMLFOREST(@1[%1$s, ]%2$s) functions.XMLParse=XMLPARSE(%1$s %2$s %3$s) functions.XMLPI=XMLPI(NAME %1$s@2[, %2$s]) functions.XMLNamespaces=XMLNAMESPACES(%1$s) functions.XMLQuery=XMLQUERY(%1$s@3[ PASSING %3$s] %2$s) functions.XMLSerialize=XMLSERIALIZE(%1$s %2$s AS %3$s) functions.XMLTable=XMLTABLE(@1[$1%s ]%2$s, %3$s %4$s) functions.XMLText=XMLTEXT(%1$s) functions.XMLTransform=XMLTRANSFORM(%1$s, %2$s) functions.XMLValidate=XMLVALIDATE(%1$s) functions.XMLElement.ContentOption.NULL_ON_NULL=true functions.XMLElement.ContentOption.EMPTY_ON_NULL=true functions.XMLForest.ContentOption.NULL_ON_NULL=true functions.XMLForest.ContentOption.EMPTY_ON_NULL=true functions.XMLParse.DocumentOrContent.DOCUMENT=true functions.XMLParse.DocumentOrContent.CONTENT=true functions.XMLParse.WhitespaceOption.STRIP_WHITESPACE=true functions.XMLParse.WhitespaceOption.PRESERVE_WHITESPACE=true functions.XMLQuery.EmptyHandlingOption.NULL_ON_EMPTY=true functions.XMLQuery.EmptyHandlingOption.EMPTY_ON_EMPTY=true functions.XMLSerialize.DeclarationOption.INCLUDING_XMLDECLARATION=true functions.XMLSerialize.DeclarationOption.EXCLUDING_XMLDECLARATION=true # # JSON functions. # functions.JSONObject=JSON_OBJECT(%2$s@1[ %1$s]) functions.JSONArray=JSON_ARRAY(%2$s@1[ %1$s]) functions.JSONExists=JSON_EXISTS(%1$s, %2$s) functions.JSONQuery=JSON_QUERY(%1$s, %2$s@3[ %3$s]) functions.JSONTable=JSON_TABLE($1%s, %2$s %3$s) functions.JSONValue=JSON_VALUE(%1$s, %2$s@3[ %3$s]) # # Business date functions. # functions.AddHours[any,any]=_ADD_HOURS(%1$s, %2$s) functions.AddMinutes[any,any]=_ADD_MINUTES(%1$s, %2$s) functions.AddSeconds[any,any]=_ADD_SECONDS(%1$s, %2$s) functions.AddFractionalSeconds[any,any]=_ADD_FRACTIONAL_SECONDS(%1$s, %2$s) functions.AddDays[any,any]=_ADD_DAYS(%1$s, %2$s) functions.AddWeeks[any,any]=_ADD_WEEKS(%1$s, %2$s) functions.AddMonths[any,any]=_ADD_MONTHS(%1$s, %2$s) functions.AddQuarters[any,any]=_ADD_QUARTERS(%1$s, %2$s) functions.AddYears[any,any]=_ADD_YEARS(%1$s, %2$s) functions.Age[any]=_AGE(%1$s) functions.FractionalSecondsBetween[any,any]=_FRACTIONAL_SECONDS_BETWEEN(%1$s, %2$s) functions.SecondsBetween[any,any]=_SECONDS_BETWEEN(%1$s, %2$s) functions.MinutesBetween[any,any]=_MINUTES_BETWEEN(%1$s, %2$s) functions.HoursBetween[any,any]=_HOURS_BETWEEN(%1$s, %2$s) functions.DaysBetween[any,any]=_DAYS_BETWEEN(%1$s, %2$s) functions.WeeksBetween[any,any]=_WEEKS_BETWEEN(%1$s, %2$s) functions.MonthsBetween[any,any]=_MONTHS_BETWEEN(%1$s, %2$s) functions.QuartersBetween[any,any]=_QUARTERS_BETWEEN(%1$s, %2$s) functions.YearsBetween[any,any]=_YEARS_BETWEEN(%1$s, %2$s) functions.DayOfWeek[any,any]=_DAY_OF_WEEK(%1$s, %2$s) functions.DayOfYear[any]=_DAY_OF_YEAR(%1$s) functions.DaysToEndOfMonth[any]=_DAYS_TO_END_OF_MONTH(%1$s) functions.FirstOfMonth[any]=_FIRST_OF_MONTH(%1$s) functions.LastOfMonth[any]=_LAST_OF_MONTH(%1$s) functions.MakeTimestamp[any,any,any]=_MAKE_TIMESTAMP(%1$s, %2$s, %3$s) functions.WeekOfYear[any]=_WEEK_OF_YEAR(%1$s) functions.YMDIntBetween[any,any]=_YMDINT_BETWEEN(%1$s, %2$s) # # Table functions. # functions.Unnest=UNNEST(%1$s) # # Literals. # literals.binary=true literals.blob=false literals.clob=false literals.boolean=true literals.date=true literals.time=true literals.time_with_time_zone=true literals.timestamp=true literals.timestamp_with_time_zone=true literals.interval_day=true literals.interval_day_to_hour=true literals.interval_day_to_minute=true literals.interval_day_to_second=true literals.interval_hour=true literals.interval_hour_to_minute=true literals.interval_hour_to_second=true literals.interval_minute=true literals.interval_minute_to_second=true literals.interval_second=true literals.interval_year=true literals.interval_year_to_month=true literals.interval_month=true literals.smallint=true literals.integer=true literals.long=true literals.float=true literals.double=true literals.decimal=true literals.char=true literals.nchar=true literals.varchar=true literals.nvarchar=true literals.xml=false literals.datalink=false # # Literal constraints. # literals.time.fractional_seconds=true # # Literal format specifications. Formats are compatible with String.format(). # Values for default behaviour are listed. # Only char, temporal and string types can be overridden. # Fractional seconds are presented as a string of up to 10 characters: '.' followed by 9 character # 0-padded string representing nanoseconds or empty. # Literal format in specific context: literals.format.. # Currently only 'procedure' is supported and denotes stored procedure parameter. # ex. literals.format.date.procedure={d '%1$04d-%2$02d-%3$02d'} # 1 parameter (string) literals.format.binary=X'%s' # 1 parameter (string) literals.format.clob='%s' # colon separated values for TRUE, FALSE and UNKNOWN literals.format.boolean=TRUE:FALSE:UNKNOWN # 3 parameters (int year, int month, int day) literals.format.date=DATE '%1$04d-%2$02d-%3$02d' # 4 parameters (int hour, int minute, int seconds, string fractional seconds) literals.format.time=TIME '%1$02d:%2$02d:%3$02d%4$.10s' # 7 parameters (int hour, int minute, int seconds, string fractional seconds, int tz hour, int tz minute, char tz sign) literals.format.time_with_time_zone=TIME '%1$02d:%2$02d:%3$02d%4$.10s%7$c%5$02d:%6$02d' # 7 parameters (int year, int month, int day, int hours, int minute, int seconds, string fractional seconds) literals.format.timestamp=TIMESTAMP '%1$04d-%2$02d-%3$02d %4$02d:%5$02d:%6$02d%7$.10s' # 10 parameters (int year, int month, int day, int hours, int minute, int seconds, string fractional seconds, int tz hour, int tz minute, char tz sign) literals.format.timestamp_with_time_zone=TIMESTAMP '%1$04d-%2$02d-%3$02d %4$02d:%5$02d:%6$02d%7$.10s%10$c%8$02d:%9$02d' # 3 parameters (int day, int leading precision, char sign) literals.format.interval_day=INTERVAL '%3$s%1$d' DAY@2[(%2$s)] # 4 parameters (int day, int hour, int leading precision, char sign) literals.format.interval_day_to_hour=INTERVAL '%4$s%1$d %2$02d' DAY@3[(%3$s)] TO HOUR # 5 parameters (int day, int hour, int minute, int leading precision, char sign) literals.format.interval_day_to_minute=INTERVAL '%5$s%1$d %2$02d:%3$02d' DAY@4[(%4$s)] TO MINUTE # 8 parameters (int day, int hour, int minute, int seconds, string fractional seconds, int leading precision, int fractional precision, char sign) literals.format.interval_day_to_second=INTERVAL '%8$s%1$d %2$02d:%3$02d:%4$02d%5$.10s' DAY@6[(%6$s)] TO SECOND@7[(%7$s)] # 3 parameters (int hour, int leading precision, char sign) literals.format.interval_hour=INTERVAL '%3$s%1$d' HOUR@2[(%2$s)] # 4 parameters (int hour, int minute, int leading precision, char sign) literals.format.interval_hour_to_minute=INTERVAL '%4$s%1$d:%2$02d' HOUR@3[(%3$s)] TO MINUTE # 7 parameters (int hour, int minute, int seconds, string fractional seconds, int leading precision, int fractional precision, char sign) literals.format.interval_hour_to_second=INTERVAL '%7$s%1$d:%2$02d:%3$02d%4$.10s' HOUR@5[(%5$s)] TO SECOND@6[(%6$s)] # 3 parameters (int minute, int leading precision, char sign) literals.format.interval_minute=INTERVAL '%3$s%1$d' MINUTE@2[(%2$s)] # 6 parameters (int minute, int seconds, string fractional seconds, int leading precision, int fractional precision, char sign) literals.format.interval_minute_to_second=INTERVAL '%6$s%1$d:%2$02d%3$.10s' MINUTE@4[(%4$s)] TO SECOND@5[(%5$s)] # 5 parameters (int seconds, string fractional seconds, int leading precision, int fractional precision, char sign) literals.format.interval_second=INTERVAL '%5$s%1$02d%2$.10s' SECOND@3[(%3$s]@4[, %4$s)] # 3 parameters (int year, int leading precision, char sign) literals.format.interval_year=INTERVAL '%3$s%1$d' YEAR@2[(%2$s)] # 4 parameters (int year, int month, int leading precision, char sign) literals.format.interval_year_to_month=INTERVAL '%4$s%1$d-%2$02d' YEAR@3[(%3$s)] TO MONTH # 3 parameters (int month, int leading precision, char sign) literals.format.interval_month=INTERVAL '%3$s%1$d' MONTH@2[(%2$s)] # 1 parameter (string) literals.format.decimal=%s # 1 parameter (string) literals.format.char='%s' # 1 parameter (string) literals.format.nchar=N'%s' # 1 parameter (string) literals.format.varchar='%s' # 1 parameter (string) literals.format.nvarchar=N'%s' # # Data types. # dataType.binary=true dataType.blob=true dataType.clob=true dataType.boolean=true dataType.date=true dataType.time=true dataType.time_with_time_zone=true dataType.timestamp=true dataType.timestamp_with_time_zone=true dataType.interval_day=true dataType.interval_day_to_hour=true dataType.interval_day_to_minute=true dataType.interval_day_to_second=true dataType.interval_hour=true dataType.interval_hour_to_minute=true dataType.interval_hour_to_second=true dataType.interval_minute=true dataType.interval_minute_to_second=true dataType.interval_second=true dataType.interval_year=true dataType.interval_year_to_month=true dataType.interval_month=true dataType.smallint=true dataType.integer=true dataType.long=true dataType.float=true dataType.double=true dataType.decimal=true dataType.char=true dataType.nchar=true dataType.varchar=true dataType.nvarchar=true dataType.xml=true dataType.period=true dataType.array=true dataType.struct=true dataType.map=true dataType.json=true dataType.datalink=true # # dataType.comparable # Used to indicate that some data types that are comparable locally may not # be supported by the DB. # #e.g. dataType.comparable[varchar,nvarchar]=false # # dataType.promotion # Used to indicate what direction the promotion needs to occur # -> these properties are not symetrical # #e.g. dataType.promotion[char,nvarchar]=true # # Collation Sequence SQL (SQL statement for retrieving the collation sequence) # This statement returns a single row and single column containing the collation sequence # collation.sequence.sql= # # Datbase Encoding SQL. This statement retrieves the charset name for the non-unicode character data. # This statement returns a single row and single column with the charset name for use in a java.nio.CharsetEncoder. # database.charset.sql= # # SQL DateTime Formatter validators. # To be used in order to verify the formatting patterns that users can give when they use # the following cognos-sql syntax: CAST('12- 8-13/1:52 PM' as TIMESTAMP FORMAT 'YYYY- MM-DD/HH12:MI AM') # formatters.delimiter=[-./,';: TZ] formatters.year=Y{1,4}|R{2}|R{4} formatters.month_number_of_year=M{1,2} formatters.day_number_of_month=D{1,2} formatters.day_number_of_year=D{3} formatters.twelve_hour_clock=H{2}(12)? formatters.twenty_four_hour_clock=H{2}24 formatters.am_pm=[AaPp][Mm]|[AaPp]\\.[Mm]\\. #formatters.am_pm=[AP][M] formatters.minutes_of_hour=MI formatters.seconds_of_minute=S{2} formatters.seconds_past_midnight=S{5} formatters.fractional_seconds=F{2}[1-9] formatters.time_zone_hour=TZH formatters.time_zone_minute=TZM #formatters.time_zone_second=TZS formatters.string_to_date=( \ (year)(delimiter)+(month_number_of_year)(delimiter)+(day_number_of_month) \ | (month_number_of_year)(delimiter)+(year)(delimiter)+(day_number_of_month) \ | (month_number_of_year)(delimiter)+(day_number_of_month)(delimiter)+(year) \ | (day_number_of_month)(delimiter)+(month_number_of_year)(delimiter)+(year) \ | (day_number_of_month)(delimiter)+(year)(delimiter)+(month_number_of_year) \ | (year)(delimiter)+(day_number_of_month)(delimiter)+(month_number_of_year) \ | (year)(delimiter)+(day_number_of_month) \ | (day_number_of_month)(delimiter)+(year) \ | (month_number_of_year)(delimiter)+(year) \ | (year)(delimiter)+(month_number_of_year) \ | (day_number_of_month)(delimiter)+(month_number_of_year) \ | (month_number_of_year)(delimiter)+(day_number_of_month) \ | (year) \ | (month_number_of_year) \ | (day_number_of_year) \ ) formatters.string_to_time=( \ (twelve_hour_clock)(delimiter)+(minutes_of_hour)(delimiter)+(seconds_of_minute)((delimiter)+((am_pm)|(fractional_seconds)|(fractional_seconds)(delimiter)+(am_pm)))? \ | (minutes_of_hour)(delimiter)+(twelve_hour_clock)(delimiter)+(seconds_of_minute)((delimiter)+((am_pm)|(fractional_seconds)|(fractional_seconds)(delimiter)+(am_pm)))? \ | (minutes_of_hour)(delimiter)+(seconds_of_minute)(delimiter)+(twelve_hour_clock)((delimiter)+((am_pm)|(fractional_seconds)|(fractional_seconds)(delimiter)+(am_pm)))? \ | (seconds_of_minute)(delimiter)+(minutes_of_hour)(delimiter)+(twelve_hour_clock)((delimiter)+((am_pm)|(fractional_seconds)|(fractional_seconds)(delimiter)+(am_pm)))? \ | (seconds_of_minute)(delimiter)+(twelve_hour_clock)(delimiter)+(minutes_of_hour)((delimiter)+((am_pm)|(fractional_seconds)|(fractional_seconds)(delimiter)+(am_pm)))? \ | (twelve_hour_clock)(delimiter)+(seconds_of_minute)(delimiter)+(minutes_of_hour)((delimiter)+((am_pm)|(fractional_seconds)|(fractional_seconds)(delimiter)+(am_pm)))? \ | (twenty_four_hour_clock)(delimiter)+(minutes_of_hour)(delimiter)+(seconds_of_minute)((delimiter)+(fractional_seconds))? \ | (minutes_of_hour)(delimiter)+(twenty_four_hour_clock)(delimiter)+(seconds_of_minute)((delimiter)+(fractional_seconds))? \ | (minutes_of_hour)(delimiter)+(seconds_of_minute)(delimiter)+(twenty_four_hour_clock)((delimiter)+(fractional_seconds))? \ | (seconds_of_minute)(delimiter)+(minutes_of_hour)(delimiter)+(twenty_four_hour_clock)((delimiter)+(fractional_seconds))? \ | (seconds_of_minute)(delimiter)+(twenty_four_hour_clock)(delimiter)+(minutes_of_hour)((delimiter)+(fractional_seconds))? \ | (twenty_four_hour_clock)(delimiter)+(seconds_of_minute)(delimiter)+(minutes_of_hour)((delimiter)+(fractional_seconds))? \ | (seconds_past_midnight) \ | (twenty_four_hour_clock) \ | (twelve_hour_clock)((delimiter)+(am_pm))? \ | (minutes_of_hour) \ | (seconds_of_minute) \ | (twelve_hour_clock)(delimiter)+(minutes_of_hour)((delimiter)+(am_pm))? \ | (minutes_of_hour)(delimiter)+(twelve_hour_clock)((delimiter)+(am_pm))? \ | (twelve_hour_clock)(delimiter)+(seconds_of_minute)((delimiter)+(am_pm))? \ | (seconds_of_minute)(delimiter)+(twelve_hour_clock)((delimiter)+(am_pm))? \ | (twenty_four_hour_clock)(delimiter)+(minutes_of_hour) \ | (minutes_of_hour)(delimiter)+(twenty_four_hour_clock) \ | (twenty_four_hour_clock)(delimiter)+(seconds_of_minute) \ | (seconds_of_minute)(delimiter)+(twenty_four_hour_clock) \ | (minutes_of_hour)(delimiter)+(seconds_of_minute) \ | (seconds_of_minute)(delimiter)+(minutes_of_hour) \ ) formatters.string_to_time_with_time_zone=( \ (string_to_time)(delimiter)*(time_zone_hour)(([:])(time_zone_minute))?(delimiter)* \ ) formatters.string_to_timestamp=( \ (string_to_date)(delimiter)+(string_to_time) \ ) formatters.string_to_timestamp_with_time_zone=( \ (string_to_date)(delimiter)+(string_to_time_with_time_zone) \ )