DB2常用的函数、常用的SQL

1.多个变量赋值

  SET(WLDMParam,WLMCParam)=(SELECT WLDM,WLMC FROM YC.YC_importplanInfo WHERE PlanCodeInfo=PlanCodeInfoParam);

2.单个变量赋值

 SELECT  name  into NameParam from test 

3.update case 语句

 UPDATE YC.py_dmjhb 
                 SET sdl=CASE IsBaoFeiParam WHEN 0 THEN COALESCE(sdl,0)-1 ELSE COALESCE(sdl,0) END,
                    ydml=COALESCE(ydml,0)+1,
                     bfl=CASE IsBaoFeiParam WHEN 1 THEN COALESCE(bfl,0)+1 ELSE COALESCE(bfl,0) END,
                     syl=CASE IsBaoFeiParam WHEN 1 THEN CASE WHEN COALESCE(jhdml,0)-COALESCE(ydml,0)<0 THEN 0 ELSE COALESCE(jhdml,0)-COALESCE(ydml,0) END
                         ELSE CASE  WHEN COALESCE(jhdml,0)-COALESCE(ydml,0)-1<0 THEN 0 ELSE COALESCE(jhdml,0)-COALESCE(ydml,0)-1  END 
                         END 
               WHERE CAST(jhnf AS INTEGER)=YEAR(current date) AND CAST(jhyf AS INTEGER)=MONTH(current date);

4.MARGE INTO

MERGE INTO YC.YC_exportplan A
           USING(SELECT PlanCode,
                        COALESCE(SUM(realityWeight),0) AS realityWeight,
                        COALESCE(SUM(realityNum),0)    AS realityNum
                 FROM YC.YC_exportHWplanInfo WHERE PlanCode=PlanCodeParam
                 GROUP BY PlanCode)B 
           ON A.PlanCode=B.PlanCode
           WHEN MATCHED THEN UPDATE SET A.realityWeight=B.realityWeight, A.realityNum=B.realityNum;   

 5.获取系统当前日期 

select current date from sysibm.sysdummy1; 
select current time from sysibm.sysdummy1;
select current timestamp from sysibm.sysdummy1;

 6.db2:根据TABLEID找table

SQL0407N  Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=50, COLNO=8" is not allowed.  SQLSTATE=23502 


上面的错误信息没有直接给出table名，可以通过下面的语句得到table和column 


SELECT * FROM SYSCAT.TABLES WHERE TBSPACEID = 2 AND TABLEID = 13


SELECT * FROM SYSCAT.COLUMNS WHERE  TABNAME= '*******' AND COLNO = 8