db2工作笔记
// 生成32位随机数CREATE OR REPLACE FUNCTION "RSPDB"."F_GETROUNDOF32" ()
specific F_GETROUNDOF32
LANGUAGE SQL
RETURNS varchar(40)
BEGIN atomic
declare v_branch_id_ods varchar(40);
declare v_count int;
declare v_branch_id_rspvarchar(40);
set v_branch_id_ods = (SELECTsubstr(max
(branch_id),1,24) || to_char(floor(RAND() * 89999999 + 10000000))from SYS_branch_info);
set v_count = (selectcount(*) from sys_branch_info where branch_id = v_branch_id_ods);
set v_branch_id_rsp = (selectbranch_idfrom sys_branch_info where branch_id = v_branch_id_ods);
if v_count =0then
return v_branch_id_ods;
end if;
return v_branch_id_rsp;
END
select substr(A.ACCDATE,1,4) || '-' || substr(A.ACCDATE,5,2)|| '-' || substr(A.ACCDATE,7,2)as ACCDATEfrom F_INN_GEN_GL_BRIAC A
db2取月所在的天数
select (current date + 1 month - day(current date + 1 month) days)
fromsysibm.sysdummy1
计算月数
select timestampdiff(64,timestamp(to_date('2013-11-01','YYYY-MM-DD')) - timestamp(to_date('2012-11-01','YYYY-MM-DD')))from sysibm.SYSDUMMY1
timestampdiff(64,timestamp(to_date(enddate,'YYYY-MM-DD')) - timestamp(to_date(startdate,'YYYY-MM-DD')))
求月数
db2函数 定义
CREATE FUNCTION "DB2INST"."F_GETMONTHS" (startdate varchar(40),enddate varchar(40))
RETURNS int
specific F_GETMONTHS
LANGUAGE SQL
BEGIN atomic
declare v_time int;
set v_time = (SELECT (year(to_date(enddate,'YYYY-MM-DD')) - year(to_date(startdate,'YYYY-MM-DD')))* 12 + (month(to_date(enddate,'YYYY-MM-DD')) - month(to_date(startdate,'YYYY-MM-DD'))) FROMsysibm.SYSDUMMY1);
return v_time;
END
验证 values(f_getmonths('2011-01-01','2012-03-03'))
CREATE OR REPLACE FUNCTION "DB2INST"."F_ISNUMBER"
(v_str varchar(100))
specific F_ISNUMBER
returns int
begin atomic
declare v_len int;
set v_len=length(v_str);
if v_len=0 or v_str is null then
return 0;
end if;
while v_len >0 do
if substr(v_str,v_len,1) not in ('0','1','2','3','4','5','6','7','8','9') then
return 0;
end if;
set v_len=v_len-1;
end while;
return 1;
end
values(F_ISNUMBER(123123))return 1;
//去掉回车换行符号和 导入导出 带分隔符
select replace(OBJ_VAL,chr(10),'') from APP_FAST_RPT_DS_INFO where DATASET_ID = '48e4a83134664df10134665e7b25000b'
selectreplace(replace(OBJ_VAL,chr(13),''),chr(10),'') from APP_FAST_RPT_DS_INFO where DATASET_ID = '48e4a83134664df10134665e7b25000b'
db2 "export to f_com_com_sp_brctl.txt of del modified by codepage=1208 COLDEL| select * from f_com_com_sp_brctl"
db2 "import from f_com_com_sp_brctl.txt of del modified by codepage=1208 COLDEL| insert into f_com_com_sp_brctl_t"
//环比
values (to_date(substr('20120531',1,6)||'01','yyyymmdd')-1 day)
2012-4-30 上午12:00:00
//同比
//比年初
values (to_date(substr('20120531',1,4)||'0101','yyyymmdd'))
2012-1-1 上午12:00:00
页:
[1]