AS400 and SQL Tricks

SQL RAISE_ERROR scalar function in DB2 for i SQL RAISE_ERROR The RAISE_ERROR function return an error with the specified SQLSTATE along with SQL…

SQL QUARTER scalar function in DB2 for i SQL QUARTER he QUARTER function returns the quarter of the year in which the date resides i.e than int…

SQL POSSTR scalar function in DB2 for i SQL POSSTR The POSITION function returns the position of the first occurrence of the search string in th…

SQL POSITION scalar function in DB2 for i SQL POSITION The POSITION function returns the position of the first occurrence of the search string in…

SQL NULLIF scalar function in DB2 for i SQL NULLIF The NULLIF function returns the null value if the two parameters are equal, otherwise it retur…

SQL MONTH scalar function in DB2 for i SQL MONTH The MONTH function returns the month part of a value. Syntax MONTH(expression) The argum…

SQL MOD scalar function in DB2 for i SQL MOD The MOD function returns the remainder by dividing the first numeric expression by the second numeri…

SQL MINUTE scalar function in DB2 for i SQL MINUTE The MINUTE function returns the minute part of a value. Syntax MICROSECOND(expression) …

SQL MIN scalar function in DB2 for i SQL MIN The MIN function returns the minimum value in a set of values. We should pass at least 2 arguments.…

SQL MICROSECOND scalar function in DB2 for i SQL MICROSECOND The MICROSECOND function returns the microsecond part of a timestamp value. Syntax…

SQL MAX scalar function in DB2 for i SQL MAX The MAX function returns the maximum value in a set of values. We should pass at least 2 arguments.…

SQL LTRIM scalar function in DB2 for i SQL LTRIM The LTRIM function removes all of the characters that are passed in the trim-expression from the…

SQL LCASE_LOWER scalar function in DB2 for i SQL LOWER or LCASE The LOWER/LCASE function returns string where all the characters get converted in…

SQL LOCATE scalar function in DB2 for i SQL LOCATE The LOCATE function returns the position at which the first occurrence of search string starts…

SQL LENGTH scalar function in DB2 for i SQL LENGTH The LENGTH function returns the length of a value. Syntax LENGTH(expression) The expressi…

SQL LAST_DAY scalar function in DB2 for i SQL LAST_DAY The LAST_DAY scalar function returns the date which is the last day of the month of the pa…

SQL INTERGER or INT scalar function in DB2 for i SQL INTEGER or INT The INTEGER function returns an integer value of either a number or a characte…

SQL INSERT scalar function in DB2 for i SQL INSERT The INSERT function returns a string where beginning at start in source-string, length charact…

SQL IFNULL scalar function in DB2 for i SQL IFNULL The IFNULL function returns the first nonnull expression. Syntax IFNULL(expression1, expre…

SQL IDENTITY_VAL_LOCAL scalar function in DB2 for i SQL IDENTITY_VAL_LOCAL The IDENTITY_VAL_LOCAL function returns the most recently assigned val…

SQL HOUR scalar function in DB2 for i SQL HOUR The HOUR function returns the hour part of a value. Syntax HOUR(expression) The argument sho…

SQL HEX scalar function in DB2 for i SQL HEX The HEX function returns a hexadecimal value as a character string. Syntax HEX(expression) wh…

SQL GREATEST scalar function in DB2 for i SQL GREATEST The GREATEST function returns the maximum value in a set of values. Syntax GREATEST(a…

SQL GRAPHIC scalar function in DB2 for i SQL GRAPHIC The GRAPHIC function returns a fixed-length graphic string of a value of different data type…

SQL FLOOR scalar function in DB2 for i SQL FLOOR FLOOR function is also called as greatest integer function and returns the largest integer less …

SQL FIRST_DAY scalar function in DB2 for i SQL FIRST_DAY The FIRST_DAY function returns the 1st day of the month of the passed argument. Synt…

SQL EXTRACT scalar function in DB2 for i SQL EXTRACT The EXTRACT function returns a portion of a date or timestamp based on its arguments. The…

SQL DIGITS scalar function in DB2 for i SQL DIGITS The DIGITS function returns a absolute value character string of a number. Syntax DIGITS(n…

SQL DECIMAL or DEC scalar function in DB2 for i SQL DECIMAL or DEC The DECIMAL function returns a decimal value of either a number, or character…

SQL DAYS scalar function in DB2 for i SQL DAYS The DAYS function returns an integer representation of a date. The result is 1 more than the num…

SQL DAYOFYEAR scalar function in SQL DB2 for i DAYOFYEAR The DAYOFYEAR function returns an integer from 1 to 366 i.e. day of the year where 1 i…

SQL DAYOFWEEK scalar function in SQL DB2 for i DAYOFWEEK The DAYOFWEEK function returns an integer from 1 to 7 i.e. day of the week where 1 is Su…

SQL DAY scalar function in DB2 for i SQL DAY The DAY function returns the day part of a value. Syntax DAY(expression) The argument should…

SQL DATE scalar function in DB2 for i SQL DATE The DATE function returns a date that is derived from a value. Syntax DATE(expression) The a…