Category: SQL

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

Read More

Db2 Nested Table Expression(NTE)

Nested Table Expression(NTE) is a select statement within a from clause Example : Select * From (Select max(salary) As MaxSalary, Dept From EmpMst E Group by Dept ) As T2

Read More

Db2 – Lpad() function

The LPAD (left pad) function is used to pad an expression on the left with spaces (default) or a string expression. This functions accept three parameters Expression to pad Length Padding expression (optional) Example : Select Lpad(EmpNbr, 10, ‘0’) From EmpMast Result: 0000000260

Read More

Now() function

Now() returns the timestamp   Example :   Select now() from sysibm.sysdummy1

Read More

Use LISTAGG to turn rows of data into a comma separated list

  The LISTAGG function aggregates a set of string elements into one string by concatenating the strings.   Example : Select dept, LISTAGG(Lastname, ‘, ‘)  Within  Group(Order by Lastname) As Employees From Emptable Group By Dept Sample Result :   Dept              Employees Admin            Adam, […]

Read More