analytics/configuration/xqe/netezza.properties

# 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.

#
# Delimiters.
#

#
# Various limits.
#
limits.maxDecimalPrecision=38

#
# General settings.
#
# Null ordering.
#
general.nullsAreSortedLow=true
general.nullsAreSortedHigh=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=
sampling.rowsample=RANDOM

#
# Various features.
#
supports.nestedWithClause=false
supports.aliasInOrderByExpression=false
supports.subqueriesInAggregate=false
supports.recursiveWithClause=false
supports.booleanExpressionsInSelectList=false
supports.correlatedSubqueriesInSelectList=false
supports.correlatedSubqueriesInIns=false
supports.callProcedureInDerivedTable=false
#casting with formatting pattern support
supports.formatters.string_to_date=false
supports.formatters.string_to_time=false
supports.formatters.string_to_time_with_time_zone=false
supports.formatters.string_to_timestamp=false
supports.formatters.string_to_timestamp_with_time_zone=false

#
# Tables.
#
tables.lateral.derived=false

#
# Constructors.
#
constructors.table=false
constructors.row=false
constructors.array=false
constructors.period=false

#
# Constructors - context overrides.
#
constructors.row.simpleCase=false
constructors.row.between=false

#
# Clauses.
#
clauses.Top=LIMIT %1$s
clauses.At=
clauses.Window=
clauses.WithRecursive=
clauses.TableSampleSystem=
clauses.TableSampleBernoulli=
clauses.ForSystemTimeAsOf=
clauses.ForSystemTimeFrom=
clauses.ForSystemTimeBetween=

#
# Joins.
#

#
# Set operators.
#

#
# Logical operators.
#
operators.logical.Is=
operators.logical.IsNot=

#
# Arithmetic operators.
#
operators.arithmetic.Subtract[any,datetime]=

#
# Group By operators.
#

#
# Comparison predicates.
#

#
# Various predicates.
#
predicates.IsDistinctFrom[any,any]=(%1$s IS NULL AND %2$s IS NOT NULL) OR (%1$s IS NOT NULL AND %2$s IS NULL) OR %1$s <> %2$s
predicates.IsNotDistinctFrom[any,any]=%1$s = %2$s OR (%1$s IS NULL AND %2$s IS NULL)
predicates.LikeRegex=
predicates.LikeRegex.flag=
predicates.Similar=
predicates.Similar.escape=

#
# Period predicates.
#
predicates.PeriodOverlaps[any,any]=
predicates.PeriodEquals[any,any]=
predicates.PeriodContains[any,any]=
predicates.PeriodPrecedes[any,any]=
predicates.PeriodSucceeds[any,any]=
predicates.PeriodImmediatelyPrecedes[any,any]=
predicates.PeriodImmediatelySucceeds[any,any]=

#
# Expressions.
#

#
# Cast expression.
#
expressions.Cast[time,time_with_time_zone]=
expressions.Cast[time,timestamp]=
expressions.Cast[timestamp,time_with_time_zone]=
expressions.Cast[decimal,integer]=CAST(TRUNC(%1$s) as INTEGER)
expressions.Cast[double,integer]=CAST(TRUNC(%1$s) as INTEGER)
expressions.Cast[float,integer]=CAST(TRUNC(%1$s) as INTEGER)

#
# Extract expression.
#
expressions.Extract.SECOND[any]=CAST(EXTRACT(MILLISECOND FROM %1$s) as DECIMAL)/1000

#
## COSHYP not supported by Netezza.
functions.Coshyp[any]=
## SINHYP not supported by Netezza.
functions.Sinhyp[any]=
## TANHYP not supported by Netezza.
functions.Tanhyp[any]=

#
# Windowed aggregates (SQL/OLAP).
#
olap.RatioToReport[any]=
olap.Tertile[]=
olap.Difference[any]=
olap.PercentileCont[any,any]=
olap.PercentileDisc[any,any]=
olap.Median[any]=
# RESPECT|IGNORE NULLS option is not supported.
olap.Lag[any,any,any,any]=
olap.Lead[any,any,any,any]=
olap.NthValue[any,any]=
olap.NthValue[any,any,any]=
olap.NthValue[any,any,any,any]=
olap.Collect[any]=

#
# Window clause.
#

#
# Aggregates
#
aggregates.PercentileCont[any,any]=
aggregates.PercentileDisc[any,any]=
aggregates.Median[any]=
aggregates.XMLAgg=
aggregates.ArrayAgg[any]=
aggregates.ArrayAgg[any,any]=
aggregates.Collect[any]=
aggregates.ApproxCountDistinct[any]=

#
# Aggregates (distinct)
#

#
# Linear regression aggregates.
#
aggregates.Corr[any,any]=
aggregates.CovarPop[any,any]=
aggregates.CovarSamp[any,any]=
aggregates.RegrAvgX[any,any]=
aggregates.RegrAvgY[any,any]=
aggregates.RegrCount[any,any]=
aggregates.RegrIntercept[any,any]=
aggregates.RegrR2[any,any]=
aggregates.RegrSlope[any,any]=
aggregates.RegrSXX[any,any]=
aggregates.RegrSXY[any,any]=
aggregates.RegrSYY[any,any]=

#
# JSON aggregates.
#
aggregates.JSONArrayAgg=
aggregates.JSONObjectAgg=

#
# Character scalar functions.
#
functions.BitLength[any]=(OCTET_LENGTH(%1$s) * 8)
functions.Index[any,any]=POSITION(%2$s IN %1$s)
functions.Translate[any,any]=
functions.Normalize[any]=
functions.Normalize[any,any]=
functions.Normalize[any,any,any]=

#
# Regular expression functions.
#
functions.SubstringRegex[any,any,any,any,any]=
functions.OccurrencesRegex[any,any,any,any]=
functions.PositionRegex[any,any,any,any,any,any]=

#
# Numeric scalar functions.
#
functions.Abs[interval_day_time]=
functions.Abs[interval_year_month]=
functions.Ceiling[any]=CEIL(%1$s)
functions.Round[any,any,any]=
functions.Power[any,any]=POW(%1$s, %2$s)
functions.Random[]=RANDOM()
functions.Random[any]=
functions.Log10[any]=LOG(%1$s)

#
# Array scalar functions.
#
functions.Cardinality[any]=
functions.TrimArray[any,any]=

#
# Trigonometric functions.
#

#
# Datetime value functions.
#
functions.CurrentDate[]=CURRENT_DATE
functions.CurrentTime[]=
functions.CurrentTimestamp[]=CURRENT_TIMESTAMP
functions.LocalTime[]=CAST(CURRENT_TIME AS TIME)
functions.LocalTimestamp[]=CAST(CURRENT_TIMESTAMP AS TIMESTAMP)
functions.CurrentTime[numeric]=
functions.CurrentTimestamp[numeric]=
functions.LocalTime[numeric]=
functions.LocalTimestamp[numeric]=

#
# XML functions.
#
functions.XMLAttributes=
functions.XMLComment=
functions.XMLConcat=
functions.XMLDocument=
functions.XMLElement=
functions.XMLExists=
functions.XMLForest=
functions.XMLParse=
functions.XMLPI=
functions.XMLNamespaces=
functions.XMLQuery=
functions.XMLSerialize=
functions.XMLTable=
functions.XMLText=
functions.XMLTransform=
functions.XMLValidate=

#
# JSON functions.
#
functions.JSONArray=
functions.JSONExists=
functions.JSONObject=
functions.JSONQuery=
functions.JSONTable=
functions.JSONValue=

#
# Business date functions.
#
functions.AddFractionalSeconds[any,any]=
functions.AddSeconds[interval_day_time,numeric]=(CAST( %1$s AS INTERVAL) + (INTERVAL '1 SECOND' * (%2$s)))
functions.AddSeconds[time,numeric]=(CAST( %1$s AS TIME) + (INTERVAL '1 SECOND' * (%2$s)))
functions.AddSeconds[timestamp,numeric]=(CAST( %1$s AS TIMESTAMP) + (INTERVAL '1 SECOND' * (%2$s)))
functions.AddSeconds[time_with_time_zone,numeric]=(CAST( %1$s AS TIMETZ) + (INTERVAL '1 SECOND' * (%2$s)))
functions.AddSeconds[timestamp_with_time_zone,numeric]=
functions.AddMinutes[interval_day_time,numeric]=(CAST( %1$s AS INTERVAL) + (INTERVAL '1 MINUTE' * (%2$s)))
functions.AddMinutes[time,numeric]=(CAST( %1$s AS TIME) + (INTERVAL '1 MINUTE' * (%2$s)))
functions.AddMinutes[timestamp,numeric]=(CAST( %1$s AS TIMESTAMP) + (INTERVAL '1 MINUTE' * (%2$s)))
functions.AddMinutes[time_with_time_zone,numeric]=(CAST( %1$s AS TIMETZ) + (INTERVAL '1 MINUTE' * (%2$s)))
functions.AddMinutes[timestamp_with_time_zone,numeric]=
functions.AddHours[interval_day_time,numeric]=(CAST( %1$s AS INTERVAL) + (INTERVAL '1 HOUR' * (%2$s)))
functions.AddHours[time,numeric]=(CAST( %1$s AS TIME) + (INTERVAL '1 HOUR' * (%2$s)))
functions.AddHours[timestamp,numeric]=(CAST( %1$s AS TIMESTAMP) + (INTERVAL '1 HOUR' * (%2$s)))
functions.AddHours[time_with_time_zone,numeric]=(CAST( %1$s AS TIMETZ) + (INTERVAL '1 HOUR' * (%2$s)))
functions.AddHours[timestamp_with_time_zone,numeric]=
functions.AddDays[any,any]=((%1$s) + INTERVAL '1 DAY' * (%2$s))
functions.AddDays[interval_day_time,numeric]=
functions.AddDays[date,numeric]=CAST((%1$s) + INTERVAL '1 DAY' * (%2$s) AS DATE)
functions.AddDays[timestamp_with_time_zone,numeric]=
functions.AddWeeks[any,any]=((%1$s) + INTERVAL '7 DAY' * (%2$s))
functions.AddWeeks[interval_day_time,numeric]=
functions.AddWeeks[date,numeric]=CAST((%1$s) + INTERVAL '7 DAY' * (%2$s) AS DATE)
functions.AddWeeks[timestamp_with_time_zone,numeric]=
# Netezza has different implementation for ADD_MONTHS and %1$s + INTERVAL %2$s MONTH
# ADD_MONTHS implements rule: result is last day of the month if months are added to the last day of the month.
# Feb28 + 1 month -> Mar31. This is inconsistent with local processing and other vendors.
#functions.AddMonths[any,any]=ADD_MONTHS(%1$s, %2$s)
functions.AddMonths[any,any]=((%1$s) + (INTERVAL '1 MONTH' * (%2$s)))
functions.AddMonths[interval_year_month,numeric]=
functions.AddMonths[date,numeric]=CAST(((%1$s) + (INTERVAL '1 MONTH' * (%2$s))) AS DATE)
functions.AddMonths[timestamp_with_time_zone,numeric]=
functions.AddQuarters[any,any]=ADD_MONTHS(%1$s, (%2$s * 3))
functions.AddQuarters[interval_year_month,numeric]=
functions.AddQuarters[timestamp_with_time_zone,numeric]=
# ADD_MONTHS implements rule: result is last day of the month if months are added to the last day of the month.
# Feb28, 2011 + 1 year -> Feb29, 2012. This is inconsistent with local processing and other vendors.
#functions.AddYears[any,any]=ADD_MONTHS(%1$s, ( (%2$s) * 12 ))
functions.AddYears[any,any]=(%1$s + (INTERVAL '1 YEAR' * (%2$s)))
functions.AddYears[interval_year_month,numeric]=
functions.AddYears[date,numeric]=CAST(((%1$s) + (INTERVAL '1 YEAR' * (%2$s))) AS DATE)
functions.AddYears[timestamp_with_time_zone,numeric]=
functions.FractionalSecondsBetween[any,any]=
functions.SecondsBetween[any,any]=
functions.MinutesBetween[any,any]=
functions.HoursBetween[any,any]=
functions.DaysBetween[date,date]=((%1$s) - (%2$s))
functions.DaysBetween[date,timestamp]=((%1$s) - CAST(%2$s AS DATE))
functions.DaysBetween[any,any]=(CAST(%1$s AS DATE) - CAST(%2$s AS DATE))
functions.DaysBetween[timestamp,date]=(CAST(%1$s AS DATE) - (%2$s))
functions.WeeksBetween[any,any]=
functions.MonthsBetween[any,any]=CAST(TRUNC(MONTHS_BETWEEN(%1$s, %2$s)) AS INTEGER)
functions.QuartersBetween[any,any]=
functions.YearsBetween[date,date]=EXTRACT(YEAR FROM AGE(%1$s, %2$s))
functions.YearsBetween[date,timestamp]=EXTRACT(YEAR FROM AGE(%1$s, CAST(%2$s as DATE)))
functions.YearsBetween[any,any]=EXTRACT(YEAR FROM AGE(cast(%1$s AS DATE), CAST(%2$s AS DATE)))
functions.YearsBetween[timestamp,date]=EXTRACT(YEAR FROM AGE(CAST(%1$s AS DATE), %2$s))
functions.Age[any]=
functions.DayOfWeek[any,any]=(MOD( CAST( TO_CHAR( %1$s, 'D' ) AS INTEGER ) + 6 - %2$s, 7 ) + 1)
functions.DayOfYear[any]=EXTRACT(DOY FROM %1$s)
functions.DaysToEndOfMonth[any]=(EXTRACT( DAY FROM (ADD_MONTHS(%1$s - ( EXTRACT( DAY FROM %1$s ) * INTERVAL '1 DAY') + INTERVAL '1 DAY', 1) - INTERVAL '1 DAY')) - EXTRACT(DAY FROM %1$s))
functions.FirstOfMonth[any]=((%1$s) - ( EXTRACT( DAY FROM (%1$s) ) * INTERVAL '1 DAY' ) + INTERVAL '1 DAY')
functions.LastOfMonth[any]=(ADD_MONTHS( ( %1$s - ( EXTRACT( DAY FROM %1$s ) * INTERVAL '1 DAY' ) + INTERVAL '1 DAY' ), 1 ) - INTERVAL '1 DAY')
functions.MakeTimestamp[any,any,any]=TO_TIMESTAMP(%1$s || '-' || %2$s || '-' || %3$s, 'YYYY-MM-DD')
functions.WeekOfYear[any]=CAST( TO_CHAR( CAST(%1$s AS TIMESTAMP),'IW') AS INTEGER)
functions.YMDIntBetween[any,any]=

#
# FDS functions.
#
functions.concat[any,any]={fn CONCAT(%1$s, %2$s)}

#
# Literals.
#
literals.binary=false
literals.blob=false
literals.clob=false
literals.boolean=false
literals.date=true
literals.time=true
literals.time_with_time_zone=false
literals.timestamp=true
literals.timestamp_with_time_zone=false
literals.interval_day=false
literals.interval_day_to_hour=false
literals.interval_day_to_minute=false
literals.interval_day_to_second=false
literals.interval_hour=false
literals.interval_hour_to_minute=false
literals.interval_hour_to_second=false
literals.interval_minute=false
literals.interval_minute_to_second=false
literals.interval_second=false
literals.interval_year=false
literals.interval_year_to_month=false
literals.interval_month=false
literals.decimal=true
literals.double=true
literals.float=true
literals.real=true
literals.integer=true
literals.long=true
literals.smallint=true
literals.char=true
literals.nchar=true
literals.nvarchar=true
literals.varchar=true
literals.xml=false

#
# literal formats
#
literals.format.date=DATE '%1$04d-%2$02d-%3$02d'
literals.format.time=TIME '%1$02d:%2$02d:%3$02d%4$.7s'
literals.format.time_with_time_zone={t '%1$02d:%2$02d:%3$02d%4$.4s%7$c%5$02d:%6$02d'}
literals.format.timestamp=TIMESTAMP '%1$04d-%2$02d-%3$02d %4$02d:%5$02d:%6$02d%7$.7s'
literals.format.timestamp_with_time_zone={ts '%1$04d-%2$02d-%3$02d %4$02d:%5$02d:%6$02d%7$.10s%10$c%8$02d:%9$02d'}

#
# Data types.
#
dataType.blob=false
dataType.clob=false
dataType.timestamp_with_time_zone=false
dataType.interval_day=false
dataType.interval_day_to_hour=false
dataType.interval_day_to_minute=false
dataType.interval_day_to_second=false
dataType.interval_hour=false
dataType.interval_hour_to_minute=false
dataType.interval_hour_to_second=false
dataType.interval_minute=false
dataType.interval_minute_to_second=false
dataType.interval_second=false
dataType.interval_year=false
dataType.interval_year_to_month=false
dataType.interval_month=false
dataType.period=false

#
# Data source type overrides
#
datasource.type.NCHAR=nchar(%1$s)
datasource.type.NVARCHAR=nvarchar(%1$s)
datasource.type.TIMETZ=time with time zone
datasource.type.INTERVAL=varchar(%1$s)

#
# Data source type overrides
# driver 4.5
datasource.type.INTERVAL_DAY=varchar(%1$s)

#
# We are unable to retrieve the collation sequence from teradata but we can still check if the comparison is case sensitive or not
#
collation.sequence.sql=SELECT '', CASE WHEN 'A' = 'a' and 'é' = 'e' THEN 'CI_AI' WHEN 'A' = 'a' and 'é' <> 'e' THEN 'CI_AS' WHEN 'A' <> 'a' and 'é' <> 'e' THEN 'CS_AS' ELSE 'CS_AI' END as COLLATOR_STRENGTH