Go语言视频零基础入门到精通

时间旅人

BEGIN
DECLARE DATE_CUR DATE;-----当前日期
DECLARE DATE_PRE VARCHAR(10);-----营業日期
DECLARE DATE_NF VARCHAR(4);--本年年份
DECLARE DATE_YF VARCHAR(2);--本月月份
DECLARE DATE_RF VARCHAR(2);--本月日份

DECLARE ORGNO VARCHAR(20);--机构码
DECLARE ORGNAME VARCHAR(40);--機构名稱
DECLARE ORGNUM2 INTEGER DEFAULT 0;---市聯社或办事處個数
DECLARE ORGNUM3 INTEGER DEFAULT 0;---信用社个shu
DECLARE ORGNUM1 INTEGER DEFAULT 0;---省联she個数
DECLARE V_COUNTER INTEGER DEFAULT 0;---ji數器

--DECLARE C1 CURSOR FOR SELECT ORGANNO,ORGANNAME FROM SDW_S_ORG WHERE ORGANLEVEL=1;
DECLARE C2 CURSOR FOR SELECT ORGANNO,ORGANNAME FROM SDW_S_ORG WHERE ORGANLEVEL=2;
DECLARE C3 CURSOR FOR SELECT ORGANNO,ORGANNAME FROM SDW_S_ORG WHERE ORGANLEVEL=3;

--SELECT COUNT(DISTINCT ORGANNO) INTO ORGNUM1 FROM SDW_S_ORG WHERE ORGANLEVEL=1;  
SELECT COUNT(DISTINCT ORGANNO) INTO ORGNUM2 FROM SDW_S_ORG WHERE ORGANLEVEL=2;  
SELECT COUNT(DISTINCT ORGANNO) INTO ORGNUM3 FROM SDW_S_ORG WHERE ORGANLEVEL=3;  

--SELECT ORGANNO INTO ORGNO FROM SDW_S_ORG WHERE ORGANLEVEL='2';

select A.OPENDAY INTO DATE_CUR FROM BDW_PUB_SYS_INFO A;  
SET DATE_PRE=SUBSTR(CHAR(DATE_CUR-1 DAY),1,10);
SET DATE_NF=SUBSTR(CHAR(DATE_PRE),1,4);
SET DATE_YF=SUBSTR(CHAR(DATE_PRE),6,2);
SET DATE_RF=SUBSTR(CHAR(DATE_PRE),9,2);

OPEN C2; ----对省聯she，对客户zai不同shi联社下做的贷款不做累计操zuo
WHILE(ORGNUM2>V_COUNTER)  
DO  
  FETCH C2 INTO ORGNO,ORGNAME;
SET V_COUNTER=V_COUNTER+1;
  INSERT INTO MDW_BIG_AMOUNT_LOAN
  (
  JGM,
  JGMC,
  JKRMC,
  JKRXZ,
  HYLB,
  DKYE,2
  NCDKYE,
  QXJE,
  XZYE,
  LZYE,
  XYYE,  
  BZYE,
  DZYYE,
  ZCYE,
  GZYE,
  CJYE,
  KYYE,
  SSYE,
  QTJGDK,
  BZ,
  NF,
  YF   
  )
  SELECT
D.JGM,
D.JGMC,
D.JKRMC,
D.JKRXZ,
D.HYLB,
D.YE,
E.DKYE,
D.QXJE,
D.JZYE,
D.XZYE,
D.XYDKYE,
D.BZDKYE,
D.ZDYDKYE,
D.ZCDKYE,
D.GZDKYE,
D.CJDKYE,
D.KYDKYE,
D.SSDKYE,
  0,
'',
  ''||DATE_NF||'',
  ''||DATE_YF||''   
FROM
(
(
SELECT
  ''||ORGNO||'' AS JGM,
  ''||ORGNAME||'' AS JGMC,
A.CUS_ID AS CUS_ID,
  A.CUS_NAME AS JKRMC,
  (CASE WHEN A.PRD_TYPE='01' THEN A.COM_HOLD_TYPE ELSE A.CUS_TYPE END) AS JKRXZ,
  (CASE WHEN A.PRD_TYPE='01' THEN (SELECT CNNAME FROM SDW_S_DIC WHERE ENNAME=A.COM_CLL_TYPE AND OPTTYPE='STD_GB_4754-2002' )
  WHEN A.PRD_TYPE='02' AND A.BIZ_TYPE IN ('021191','021311','022183','022309','022310','022312','022352','022313', '022314', '022315', '022316', '022366', '022351') THEN (SELECT CNNAME FROM SDW_S_TREEDIC WHERE ENNAME=A.LOAN_DIRECTION) ELSE '个人xiao費' END) AS HYLB,
  SUM(A.LOAN_BALANCE) AS YE,
  SUM(A.DELAY_INT_CUMU) AS QXJE,
  SUM(CASE WHEN A.FIRST_DISB_DATE<'2003-01-01' THEN A.LOAN_BALANCE ELSE 0 END) AS JZYE,
  SUM(CASE WHEN A.FIRST_DISB_DATE>'2003-01-01' AND A.LOAN_FORM<>'4' THEN A.LOAN_BALANCE ELSE 0 END) AS XZYE,
  SUM(CASE WHEN A.ASSURE_MEANS_MAIN='00' THEN A.LOAN_BALANCE ELSE 0 END ) AS XYDKYE,
  SUM(CASE WHEN A.ASSURE_MEANS_MAIN='30' THEN A.LOAN_BALANCE ELSE 0 END ) AS BZDKYE,
  SUM(CASE WHEN A.ASSURE_MEANS_MAIN='20' OR A.ASSURE_MEANS_MAIN='10' THEN A.LOAN_BALANCE ELSE 0 END ) AS ZDYDKYE,

  SUM(CASE WHEN A.CLA='10' THEN A.LOAN_BALANCE ELSE 0 END ) AS ZCDKYE,   
  SUM(CASE WHEN A.CLA='20' THEN A.LOAN_BALANCE ELSE 0 END ) AS GZDKYE,
  SUM(CASE WHEN A.CLA='30' THEN A.LOAN_BALANCE ELSE 0 END ) AS CJDKYE,   
  SUM(CASE WHEN A.CLA='40' THEN A.LOAN_BALANCE ELSE 0 END ) AS KYDKYE,
  SUM(CASE WHEN A.CLA='50' THEN A.LOAN_BALANCE ELSE 0 END ) AS SSDKYE   
  FROM MDW_ACC_GATHER A  
WHERE A.ACCOUNT_STATUS='1' AND A.LOAN_BALANCE>0 GROUP BY A.CUS_ID,A.LOAN_DIRECTION,A.PRD_TYPE,A.COM_CLL_TYPE,A.BIZ_TYPE,A.COM_HOLD_TYPE,A.CUS_TYPE,A.CUS_NAME
) C
  
RIGHT OUTER JOIN
   
(
  SELECT
  CUS_ID,
  CUS_NAME,
  FINA_BR_ID,
  YE
FROM
  (
  SELECT A1.CUS_ID AS CUS_ID,A1.CUS_NAME AS CUS_NAME,A1.FINA_BR_ID AS FINA_BR_ID,SUM(A1.LOAN_BALANCE) AS YE
  FROM MDW_ACC_GATHER A1   
WHERE A1.ACCOUNT_STATUS='1' AND SUBSTR(A1.FIRST_DISB_DATE,1,4)= ''||DATE_NF||'' AND (SUBSTR(A1.FIRST_DISB_DATE,6,2)= ''||DATE_YF||'' OR SUBSTR(A1.FIRST_DISB_DATE,6,2)= ''||DATE_YF-1||'' OR SUBSTR(A1.FIRST_DISB_DATE,6,2)= ''||DATE_YF-2||'')  
AND A1.FINA_BR_ID IN (SELECT ORGANNO FROM SDW_S_ORG WHERE LOCATE LIKE '%'||''||ORGNO||''||'%') GROUP BY A1.FINA_BR_ID, A1.CUS_ID,A1.CUS_NAME
  )ORDER BY YE DESC FETCH FIRST 10 ROWS ONLY
) B
  
  ON C.CUS_ID=B.CUS_ID --GROUP BY A.FINA_BR_ID,A.CUS_ID,A.LOAN_DIRECTION,A.PRD_TYPE,A.COM_CLL_TYPE,A.BIZ_TYPE,A.COM_HOLD_TYPE,A.CUS_TYPE,A.CUS_NAME

) D  
LEFT OUTER JOIN
(SELECT JGM,DKYE,JGMC FROM MDW_BIG_AMOUNT_LOAN WHERE NF=''||DATE_NF-1||'' AND YF='12' GROUP BY JGM,DKYE,JGMC) E ON E.JGM=D.JGM;
  
END WHILE;
CLOSE C2;

COMMIT;
END;
欢迎來到Java学习者論坛,转载请注ming地址:http://www.javaxxz.com.

jspfan

代码太长，你分段运行SQL語句，看看问题出在什么地方

mone12

不符合业务规则，还是报错了？

彩虹糖梦

我提问的主要目的是要大家帮我看下right outer jion的用法是否对？

运行时报错：SQL0104N An unexpected token "JOIN" was found following "". Expected tokens may include: "FROM". LINE NUMBER=45. SQLSTATE=42601

okwoie

好像少了半个括号

彩虹糖梦

是不是少了半个括号？

wujia

没有少括号！

水中月

沒看出來

bykuten7

好帖要顶,楼主的头像还是不错滴