有用的SQL

--建序列
DROP SEQUENCE CMCS3_PROCESS_INSTANCE_INFO;

create sequence TransferBill_SEQ
minvalue 9999999999999999
maxvalue 999999999999999999999999999
start with 9999999999999999
increment by 1
cache 20;

create sequence TransferBillList_SEQ
minvalue 9999999999999999
maxvalue 999999999999999999999999999
start with 9999999999999999
increment by 1
cache 20;

--where ,group by ,having ,order by使用顺序
Select/update/delete…… from …… where …… group by …… having …… order by …… asc/desc

--删除和增加一列
alter table CMCS3_TRANSFER_BILL_LIST drop column PAYMENT_LIST_DATE

alter table CMCS3_TRANSFER_BILL_LIST add column PAYMENT_LIST_DATE
TIMESTAMP

--DB2查询序列方式
select NEXTVAL for MENU_SEQ from sysibm.sysdummy1;

--去掉char转化后的左边补充的00000000
SELECT TRIM(L ’0’ FROM '00123.45') FROM SYSIBM.SYSDUMMY1
将其他类型转换成char型直接用char()

--DB2的 DATE  和  TIMESTAMP 的区别
DATE 只能记录日期 年月日
TIMESTAMP 能记录年月日 时分秒

--外连接的问题
select count(*) from cmcs3_bank_account a left join cmcs3_transfer_bill b
on a.accountid = b.accountid
left join cmcs3_transfer_bill_list l
on b.transferbillid = l.transferbillid
where a.accountno = '1502206029300372918' and l.payeeaccountno = '138029283000004733' and l.payamount = '286900';

--oracle和DB2转换double型
to_number(tbl.payAmount) oracle
double(tbl.payAmount) DB2


--获取前20位数据库数据
FETCH FIRST 20 ROWS ONLY


--月末额度视图
DROP VIEW "CMBBCD_AH"."CMCS3_BANK_ACCOUNT_BALANCE_VIEW";

CREATE VIEW "CMBBCD_AH"."CMCS3_BANK_ACCOUNT_BALANCE_VIEW"
    ("ACCOUNTBALANCEID_", "ACCOUNTID_", "YEAR_", "MONTH_", "BALANCE_", "USEABLEBALANCE_",
    "OVERDRAFT_" )
AS
select  b.ACCOUNTBALANCEID as ACCOUNTBALANCEID_, b.ACCOUNTID as ACCOUNTID_,year(b.BALANCEDATE) as YEAR_,month(b.BALANCEDATE) as MONTH_,  b.BALANCE as BALANCE_,b.USEABLEBALANCE as USEABLEBALANCE_,b.OVERDRAFT as OVERDRAFT_ 
  from CMCS3_BANK_ACCOUNT_BALANCE b, ( 
  select b1.ACCOUNTID as ACCOUNTID, max(b1.BALANCEDATE) as BALANCEDATE 
  from CMCS3_BANK_ACCOUNT_BALANCE b1 group by b1.ACCOUNTID,year(b1.BALANCEDATE),month(b1.BALANCEDATE) )  as b2 
  where b.ACCOUNTID = b2.ACCOUNTID and b.BALANCEDATE = b2.BALANCEDATE
WITH NO ROW MOVEMENT;