1.RRN(Table) 会显示该record在这个表中唯一的行号,此行号自动增长,不会被重用
select RRN(Table) as RowID from Table
2.Update *HIVAL/*LOCAL by SQL
Update Table Set Value=X'FFFF' // *HIVAL
Update Table Set Value=X'0000' // *LOVAL
3.连接字符串
Update Table Set Column1='R'||Column1
Update Table Set Column1='ABC'||SubString(Column1,4)
Update Table Set Column1='ABC'||Right(Column1,2)
4.查找Column1中的空格位置
SELECT Locate(' ',trim(Column1)) from Table
5.截取字符串
SELECT Substr(Column1,3),Substr(Column1,3,2),Left(Column,3),Right(Column,3) from Table
6.當前日期時間
SELECT curdate(),curtime() from Table
7.日期比較
SELECT days (curdate()) - days(date('2014-02-01')) FROM Table
AS400 计算日期时间
http://www.aixchina.net/club/thread-27763-1-1.html
8.前面幾條
select top ?
select RRN(Table) as RowID from Table
2.Update *HIVAL/*LOCAL by SQL
Update Table Set Value=X'FFFF' // *HIVAL
Update Table Set Value=X'0000' // *LOVAL
3.连接字符串
Update Table Set Column1='R'||Column1
Update Table Set Column1='ABC'||SubString(Column1,4)
Update Table Set Column1='ABC'||Right(Column1,2)
4.查找Column1中的空格位置
SELECT Locate(' ',trim(Column1)) from Table
5.截取字符串
SELECT Substr(Column1,3),Substr(Column1,3,2),Left(Column,3),Right(Column,3) from Table
6.當前日期時間
SELECT curdate(),curtime() from Table
7.日期比較
SELECT days (curdate()) - days(date('2014-02-01')) FROM Table
AS400 计算日期时间
http://www.aixchina.net/club/thread-27763-1-1.html
8.前面幾條
select top ?
SELECT * FROM orders FETCH FIRST 100 ROWS ONLY
9.字符串長度,去除空格
length(...)
trim(...) rtrim(...) ltrim(...)
SELECT icincd,length(name),length(trim(name)) FROM orders
10.替換字符串
select replace('aaabb','ab','vvv') from orders
11.Case when
SELECT * from orders
ORDER BY CASE no WHEN 0 THEN 900000 ELSE no END
12.轉義字符
2個引號
INSERT INTO QTEMP/TEST VALUES(1,'abc''')