Denodo SQL Type Mapping

denodo 8.0 saves some manual coding when building the ‘Base Views’ by performing some initial data type conversions from ANSI SQL type to denodo Virtual DataPort data types. So, here is a quick reference mapping to show what the denodo Virtual DataPort Data Type mappings are:

ANSI SQL types To Virtual DataPort Data types Mapping

ANSI SQL Type	Virtual DataPort Type
BIT (n)	blob
BIT VARYING (n)	blob
BOOL	boolean
BYTEA	blob
CHAR (n)	text
CHARACTER (n)	text
CHARACTER VARYING (n)	text
DATE	localdate
DECIMAL	double
DECIMAL (n)	double
DECIMAL (n, m)	double
DOUBLE PRECISION	double
FLOAT	float
FLOAT4	float
FLOAT8	double
INT2	int
INT4	int
INT8	long
INTEGER	int
NCHAR (n)	text
NUMERIC	double
NUMERIC (n)	double
NUMERIC (n, m)	double
NVARCHAR (n)	text
REAL	float
SMALLINT	int
TEXT	text
TIMESTAMP	timestamp
TIMESTAMP WITH TIME ZONE	timestamptz
TIMESTAMPTZ	timestamptz
TIME	time
TIMETZ	time
VARBIT	blob
VARCHAR	text
VARCHAR ( MAX )	text
VARCHAR (n)	text

ANSI SQL Type Conversion Notes

• The function CAST truncates the output when converting a value to a text, when these two conditions are met:

1. You specify a SQL type with a length for the target data type. E.g. VARCHAR(20).
2. And, this length is lower than the length of the input value.

• When casting a boolean to an integer, true is mapped to 1 and false to 0.

Related References

denodo 8.0 / User Manuals / Virtual DataPort VQL Guide / Functions / Conversion Functions

• https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/conversion_functions/conversion_functions