IBM AS400 DB2 SQL Tricks
Posted 02-08-2013 at 10:20 AM by NullTerminator
set schema mySchema
Select x,y,z from w
fetch first 20 rows only
reset an auto counter that got hosed
alter table T alter TheColumn restart with 123
grab the first non null value
select a,b,c, coalesce(z,y,z) as FirstGoodValue from T
Case
select A, case when x < 0 then 'Negative' else 'Positive' end from T
TIMESTAMP format is 'YYYY-MM-DD-hh.mm.ss.uuuuuu'
get month from a date MONTH(datefield), YEAR(datefield)
Date spans
select a,b,c from T
where SomeDate > CURRENT DATE - 2 MONTH
SELECT CURRENT DATE - 1 MONTH + 2 DAYS FROM MyTable
Select x,y,z from w
fetch first 20 rows only
reset an auto counter that got hosed
alter table T alter TheColumn restart with 123
grab the first non null value
select a,b,c, coalesce(z,y,z) as FirstGoodValue from T
Case
select A, case when x < 0 then 'Negative' else 'Positive' end from T
TIMESTAMP format is 'YYYY-MM-DD-hh.mm.ss.uuuuuu'
get month from a date MONTH(datefield), YEAR(datefield)
Date spans
select a,b,c from T
where SomeDate > CURRENT DATE - 2 MONTH
SELECT CURRENT DATE - 1 MONTH + 2 DAYS FROM MyTable
Total Comments 0