DB2一些SQL的用法

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 * 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''')    

 

13.row_number()
select test ,ROW_NUMBER() over(partition by test1 order by (case test2 when 0 then 999999 else test2 end) desc ) as rownum from tlib/file WHERE rownum = 1

14.裝換類型
SELECT int(1.01),int('123'), int('123a'),cast(1  as decimal(5,2) ) ,coalesce(1.1,0), replace(1.1,'NULL','0'),replace('aa','a'  ,'0') from tlib/file

 15.isnull函數
   coalesce ,   replace      

16.upper,lower函數  

17.利用translate函數判斷字符串不含有字母數字
SELECT test1,                                           
   translate(test1,'?????????????????????????????????????',       
'01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ' )                           
FROM test                                                      
where trim(test1)<>'' and   trim(                                 
   translate(test1,'                                     ',       
   '01234567890ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
  ))<>''    

18.批量更新DB2 mass update 

UPDATE tlib/test2 SET txt= (SELECT txtFROM             

 (select DISTINCT TXT1 from tlib/test1 GROUP BY   txt1,txt2) AS NT WHERE txt=  NT.txt)       

19.union里面的子查询order by 不起作用：使用union all

20.

merge into tlib/test b                               
using (                                                        
SELECT *    FROM tlib/test t1,tlib/test t2        
) as d                                                         
on  txt=d.txt
 when matched then update set b.txt1='234'