CONNX Data Integration Suite 14.8.0 | Reference Guide | SQL Grammar | SQL Literals
 
SQL Literals
Binary Literals
Syntax:
[X|x]'hex byte'
Remarks:
*Binary literals use hexadecimal bytes to represent the binary data.
*Binary literals must be prefixed by a capital or lower case X followed by a single quote character, and suffixed by a single quote character.
If the Use Quoted Delimiters option is not checked in the data dictionary global configuration options, then character literals can also be prefixed and suffixed by a single double quote character.
*The precision of the literal will be the count of all characters inside the quotes divided by two.
Examples of binary literals:
Literal
Data Type
x'AF034879F0CC'
binary(6)
x'' (empty quotes)
binary(0)
Character Literals
Syntax:
'[']character[']'
Remarks:
*Character literals must be prefixed and suffixed by a single quote character.
If the Use Quoted Delimiters option is not checked in the data dictionary global configuration options, then character literals can also be prefixed and suffixed by a single double quote character.
*The data type of a character literal is SQL Char, unless the literal contains a character outside of the Latin character set. If the literal contains a character with a code point greater than 255, its data type is SQL Unicode.
*The precision of the literal will be the count of all characters inside the quotes.
*An empty string will be treated a varchar(1) field, with a space.
Examples of character literals:
Literal
Data Type
'abcdef'
Char(6)
'abcdefg'
Char(7) only if "Use Quoted Delimiters" is disabled; otherwise this is treated as an identifier
'Unicode example.'
Unicode(7)
'' (empty quotes)
Varchar(1)
Date Literals
Syntax:
{d 'YYYY[- | / | . / ]MM[- | / | .]DD' }
where:
*YYYY is the four digit year.
*MM is the one or two digit month of the year (between 1 and 12).
*DD is the one or two digit day of the month (between 1 and 31).
*Each of the / . - symbols is a valid date separator. You must use only one symbol.
Remarks:
*CONNX does not assume the date is in the current century if YYYY has two digits. For example, the literal {d '02-01-14'} refers to the year 2 C.E., not 2002 C.E. or 1902 C.E.
*Date literals have a Date SQL type.
*When a character literal is used in an expression that expects a date, CONNX will assume the supplied character string is a date literal, even without the date literal prefix '{d' and suffix '}'.
Examples of date literals:
Literal
Validity
{d '2014-03-01' }
valid
{d '1920-12-20' }
valid
{d '1920.12.20' }
valid
{d '1950/1/20' }
valid
{d '1920-12-20' }
valid
{d '2014-13-01' }
invalid month
{d '1920-12-99' }
invalid day
Numeric Literals
Syntax:
[+ | -]DIGIT[.]DIGIT[E|e][+|-]DIGIT
Remarks:
*If a numeric literal contains only digits, an optional sign, and an optional decimal point, it will have a data type of SQL Numeric.
*The precision of the literal will be the count of all digits before and after the decimal point.
*The scale of the field will be zero if no decimal point is present.
*If a decimal point is present, the scale will be the total number of digits following the decimal point.
*If the "e" symbol is present in the numeric literal, the number is in scientific notation, and the data type will be Qfloat (see Data Types). At data retrieval time, the value will be downgraded to SQL double.
Examples of numeric literals:
Literal
Data Type
1234
Numeric(4,0)
-123456
Numeric(6,0)
12345.56
Numeric(5,2)
123e0
Double
Time Literal
Syntax:
{t 'HH[: | - | | .]MM[: | - | .]SS' }
where:
*HH is the one or two digit hour, in 24 hour format (0 to 23).
*MM is the one or two digit minute (0-59).
*SS is the one or two digit seconds (0-59).
*Each of the : . - symbols is a valid time separator. You must use only one symbol.
Remarks:
*Time literals cannot specify a seconds value with a fractional component. Only the timestamp literal has a seconds value with a fractional component.
*Time literals have a Time SQL type.
*When a character literal is used in an expression that expects a time, CONNX will assume the supplied character string is a time literal, even without the time literal prefix '{t' and suffix '}'.
Examples of time literals:
Literal
Validity
{t '12:34:56' }
valid
{t '12-34-56' }
valid
{t '12.34.56' }
valid
{t '99:03:33' }
invalid hour
{t '00:99:00' }
invalid minute
Timestamp Literal
Syntax:
{ts 'YYYY[- | / | | .]MM[- | / | .]DD[ | - | . | /]HH[: | - | .]MM[: | - | .]SS[- | / | .FFFFFFFFF]' }
where:
*YYYY is the four digit year.
*MM is the one or two digit month of the year (between 1 and 12)
*DD is the one or two digit day of the month (between 1 and 31).
*HH is the one or two digit hour in 24 hour format (0 to 23)
*MM is the one or two digit minute (0-59).
*SS is the one or two digit seconds (0-59)
*FFFFFFFFF is the zero to 10 digit fractional component of the seconds
*Each of the : . - symbols is a valid time separator. You must use only one symbol.
Remarks:
*Timestamp literals have a Timestamp SQL type.
*When a character literal is used in an expression that expects a timestamp, CONNX will assume the supplied character string is a timestamp literal, even without the timestamp literal prefix '{ts' and suffix '}'.
Examples of timestamp literals:
Literal
Validity
{ts '2014-03-01 12:34:56' }
valid
{ts '2014-03-01 12:34:56.12345678' }
valid
{ts '2014/03/01 12.34.56' }
valid
{ts '2014.03.01-12.34.56.12345678' }
valid
{ts '2014.33.01-12.34.56.12345678' }
invalid month