today:
54
yesterday:
419
Total:
1,019,458

SQL VERY IMPORTANT

admin 2018.06.26 05:29 Views : 250

DECLARE
 SSID2 NUMBER;

BEGIN
delete from MISSIONARY_SUB;
delete from MISSIONARY_SUB2;
delete from MISSIONARY_YEAR;

insert into MISSIONARY_SUB
Select 
MISSIONARY_ID,SUPPORTER_ID,AMOUNT,to_number(to_char(CONTRIBUTION_DATE,'MM')) 
from CONTRIBUTION 
where UPPER(:P9_MISSIONARY_ID) = MISSIONARY_ID 
and :P9_YEAR = to_number(to_char(CONTRIBUTION_DATE,'YYYY'));

insert into MISSIONARY_SUB2
Select 
SSID,SUM(SAMOUNT),SDATE 
from MISSIONARY_SUB 
Group by SSID,SDATE;

INSERT INTO MISSIONARY_YEAR (SID)
SELECT DISTINCT SSID FROM MISSIONARY_SUB;

     FOR x IN (Select * from MISSIONARY_YEAR) LOOP

     UPDATE MISSIONARY_YEAR
     SET JAN = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 1)
     WHERE SID = x.SID;
     
     UPDATE MISSIONARY_YEAR
     SET FEB = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 2)
     WHERE SID = x.SID;

     UPDATE MISSIONARY_YEAR
     SET MAR = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 3)
     WHERE SID = x.SID;
     
     UPDATE MISSIONARY_YEAR
     SET APR = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 4)
     WHERE SID = x.SID;

     UPDATE MISSIONARY_YEAR
     SET MAY = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 5)
     WHERE SID = x.SID;
     
     UPDATE MISSIONARY_YEAR
     SET JUN = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 6)
     WHERE SID = x.SID;

     UPDATE MISSIONARY_YEAR
     SET JUL = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 7)
     WHERE SID = x.SID;
     
     UPDATE MISSIONARY_YEAR
     SET AUG = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 8)
     WHERE SID = x.SID;

     UPDATE MISSIONARY_YEAR
     SET SEP = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 9)
     WHERE SID = x.SID;
     
     UPDATE MISSIONARY_YEAR
     SET OCT = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 10)
     WHERE SID = x.SID;

     UPDATE MISSIONARY_YEAR
     SET NOV = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 11)
     WHERE SID = x.SID;
     
     UPDATE MISSIONARY_YEAR
     SET DEC = (SELECT SAMOUNT2 FROM MISSIONARY_SUB2 WHERE SSID2 = x.SID AND SDATE2 = 12)
     WHERE SID = x.SID;

     END LOOP;
COMMIT;
END;
I want to
List
No. Subject Author Date Views
126 Global pages admin 2023.05.09 49
125 YEAR EXPRESSIONS admin 2023.05.25 49
124 FINAL TEST admin 2023.05.08 52
123 TEST admin 2023.05.03 53
122 ORACLE COLLECTION admin 2023.05.08 53
121 GitHub.com/Oracle/APEX. admin 2023.05.04 54
120 DATE -> DAY CONVERT SQL admin 2023.05.25 55
119 GRAPH (BAR) SQL SAMPLE admin 2023.05.25 57
118 INNER JOIN admin 2023.05.19 59
117 TEST admin 2023.04.28 61
116 GP 영수증 (3) -01/04/2023 admin 2023.01.04 65
115 WHDR email address admin 2023.04.13 67
114 Monthly SUM admin 2023.05.22 69
113 GP - 영수증 (2) admin 2022.11.07 71
112 GP 영수증 본문 admin 2022.11.07 72
111 CHANGE TABLE NAME admin 2023.05.31 74
110 Building Mobile Applications with Oracle Application Express 5.0 admin 2017.11.25 75
109 ipconfig admin 2023.06.03 79
108 LOOP TABLE UPDATE admin 2023.05.19 80
107 Manufacturing resource planning (MRP II) admin 2018.02.14 83
Write Tag