Data Types
Datatype |
Default value |
bool[ean] |
char[acter][(lenght)] |
A single blank |
date |
NULLDATE
For Oracle and DB2, it is 01/01/0001
For MS SQL Server, it is 1/1/1753 |
double |
0 |
float |
0 |
ID |
'0000000000000000' (sixteen zeros) |
integer |
0 |
smallint
Oracle uses NUMBER(5) |
0 |
string[(lenght)] |
A single blank |
time |
NULLDATE
For Oracle and DB2, it is 01/01/0001
For MS SQL Server, it is 1/1/1753 |
tinyint
not supported on DB2
Oracle uses NUMBER(3) |
0 |
Literals
Integer literals |
[+|-]n
n is any number between -2147483647 and +2147483647 |
Floating point literals |
5.347|21.|0.45|.66|-4.12|10.4e-6|-3.6E7|12e-3 |
ID literals |
16-character string enclosed in single quotes |
Date literals |
| |
DATE('date_value[utc]' [,'pattern']) |
Default formats |
mm/dd/[yy]yy |
DATE(’03/24/1989’) DATE(’4/7/1992’) |
dd-mon-[yy]yy |
DATE(’4-Apr-1975’) |
month dd[,] [yy]yy |
DATE(’January 1, 1993’) |
mon dd [yy]yy |
DATE(’March 23 1990’) |
Date literal keywords |
DATE(TODAY) |
returns the current date in UTC |
DATE(NOW) |
returns the current date and time |
DATE(YESTERDAY) |
returns the current date minus one day in UTC |
DATE(TOMORROW) |
returns the current date plus one day in UTC |
|
|
Special keywords
USER |
identifies the current user
WHERE supervisor_name=USER |
TRUE|FALSE |
represent the Boolean true and false |
DM_SESSION_DD_LOCALE |
represents the data dictionary locale most appropriate for the client’s session locale
WHERE nls_key='fr_cn'
WHERE nls_key=DM_SESSION_DD_LOCALE |
|
|
Functions
Scalar functions |
ASCII(attr) |
returns the ASCII code value of the first character of the argument |
BITAND(arg1, arg2) |
ANDs the bit values of the two input arguments |
BITCLR(arg1, arg2) |
sets the bit in the result to zero (clears it), if the corresponding bit in the second parameter |
BITSET(arg1, arg2) |
ORs the bit values of the two input arguments |
UPPER(arg) |
returns the uppercase of that value |
LOWER(arg) |
returns the lowercase of that value |
SUBSTR(string_value,start[,length]) |
returns some or all of a particular string |
Aggregate functions |
COUNT ([DISTINCT] name|*) |
counts values |
MIN(DISTINCT name|[ALL] value_expresssion) |
returns the minimum value in a given set of values |
MAX(DISTINCT name|[ALL] value_expresssion) |
returns the maximum value in a given set of values |
AVG(DISTINCT name|[ALL] value_expression) |
returns an average |
SUM(DISTINCT name|[ALL] value_expression) |
returns a total |
Date functions |
Valid date_part are year, month, week, and day |
DATEDIFF(date_part, date1, date2) |
returns a number that represents the difference between the two dates |
DATEADD(date_part, number, date) |
adds a number of years, months, weeks, or days to a date and returns the new date |
DATEFLOOR(date_part,date) |
rounds a given date down to the beginning of the year, month, or day in UTC |
DATEFLOOR_LOCAL(date_part,date) |
rounds a date down to the beginning of an indicated granularity (day, month, or year) in the server local time |
DATETOSTRING(date,'format') |
returns a date as a character string in UTC in a particular format |
DATETOSTRING_LOCAL(date,'format') |
formats a date value to a character string in the server local time |
|
