Hello IBM i 7.4


Db2 Sql – Left Function

Left function returns a substring consisting of the leftmost ‘n’ characters from a string. syntax : left  (source string, position) example : Values left(‘EASTER’,4); Result : EAST  

Db2 Sql to get the first and last day of the month

LAST_DAY function returns the last day of a month. Example : To get the last day of the current month, Values last_day(current date) Result : 04/30/2019 To get the first day of the current month, Values last_day(current date) + 1 day – 1 month Result : 04/01/2019

Db2 Upsert using Merge into

Using Merge into statement, you can either insert or update a row. Merge into Syntax Merge into upsert_table as Temp using (values (1, 2, 3), (4, 5, 6), (7, 8, 9) —- more rows ) As Merge (C1, C2, C3) On Temp.Key_to_Match = Merge.Key_to_Match When Matched Then Update set temp.c1 = merge.c1, temp.c2 = merge.c2, […]

SQL Db2 VALUES Statement

Values statement derives a result from an expression Examples : VALUES LOWER(‘ABCD’); Result : abcd VALUES (LOWER(‘ABCD’), UPPER(‘abcd’)); Result : abcd ABCD (Since we have used parentheses, results are in the same row) VALUES LOWER(‘ABCD’), UPPER(‘abcd’); Result : abcd ABCD