As a part-time student I've got once more a task containing sql
. I have to extract significant monthly deviations of two financial values (RWA and EAD) per client id (CIFNR) out of an oracle database. A client might have multiple entries, therefore it is necessary to summarize first the two financial values. Due to the fact that I'm more familiar with sql in access than in oracle I've written an access sql query to get a first impression of possible pitfalls.
- Code: Select all
SELECT PMONTH.CIFNR, sum(PMONTH.CREDIT_RISK_RC)/2 AS CREDIT_RISK_RC_O, sum(PMONTH.EAD)/2 AS EAD_O, CMONTH.CIFNR, sum(CMONTH.CREDIT_RISK_RC)/2 AS CREDIT_RISK_RC_N, sum(CMONTH.EAD)/2 AS EAD_N
FROM CMONTH INNER JOIN PMONTH ON CMONTH.CIFNR = PMONTH.CIFNR
GROUP BY PMONTH.CIFNR, CMONTH.CIFNR
HAVING (((SUM(CMONTH.CREDIT_RISK_RC) - SUM(PMONTH.CREDIT_RISK_RC) > 2*10) OR (SUM(PMONTH.CREDIT_RISK_RC) - SUM(CMONTH.CREDIT_RISK_RC)) > 2*10 )) OR (((SUM(CMONTH.EAD) - SUM(PMONTH.EAD) > 2*10) OR (SUM(PMONTH.EAD) - SUM(CMONTH.EAD)) > 2*10 ))
UNION ALL
SELECT PMONTH.CIFNR, sum(PMONTH.CREDIT_RISK_RC) as CREDIT_RISK_RC_O, sum(PMONTH.EAD) as EAD_O, CMONTH.CIFNR, sum(CMONTH.CREDIT_RISK_RC) as CREDIT_RISK_RC_N, sum(CMONTH.EAD) as EAD_N
FROM CMONTH LEFT JOIN PMONTH on CMONTH.CIFNR = PMONTH.CIFNR
GROUP BY PMONTH.CIFNR, CMONTH.CIFNR
HAVING ((PMONTH.CIFNR is null) AND ((sum(CMONTH.EAD) > 10 OR sum(CMONTH.EAD) < -10) or (sum(CMONTH.CREDIT_RISK_RC) > 10 or sum(CMONTH.CREDIT_RISK_RC) < -10)))
UNION ALL
SELECT PMONTH.CIFNR, sum(PMONTH.CREDIT_RISK_RC) as CREDIT_RISK_RC_O, sum(PMONTH.EAD) as EAD_O, CMONTH.CIFNR, sum(CMONTH.CREDIT_RISK_RC) as CREDIT_RISK_RC_N, sum(CMONTH.EAD) as EAD_N
FROM CMONTH RIGHT JOIN PMONTH on CMONTH.CIFNR = PMONTH.CIFNR
GROUP BY PMONTH.CIFNR, CMONTH.CIFNR
HAVING ((CMONTH.CIFNR is null) AND ((sum(PMONTH.EAD) > 10 OR sum(PMONTH.EAD) < -10) or (sum(PMONTH.CREDIT_RISK_RC) > 10 or sum(PMONTH.CREDIT_RISK_RC) < -10)))
This has worked pretty fine with my test tables and I've got all siginficant deviations which were higher than 10.
Then the time had come to implement my access sql into oracle. I enjoy only reading rights on the oracle database which is somehow limiting. The oracle db is "stored" on the following path: "inp.inp_st_risk_weighted_asset" and all data is in one table (i.e. all months). This has made it somehow difficult to use my previous query. I've tried to create global temporary tables but they were somehow always empty.
- Code: Select all
CREATE GLOBAL TEMPORARY TABLE PMONTH
As (Select CIFNR, CREDIT_RISK_RC, EXPOSURE_DEFAULT_RC from inp.inp_st_risk_weighted_asset where dat_per = '30.Sep.2012');
CREATE GLOBAL TEMPORARY TABLE CMONTH
As (Select CIFNR, CREDIT_RISK_RC, EXPOSURE_DEFAULT_RC from inp.inp_st_risk_weighted_asset where dat_per = '31.Oct.2012');
SELECT PMONTH.CIFNR, sum(PMONTH.CREDIT_RISK_RC)/2 AS CREDIT_RISK_RC_O, sum(PMONTH.EXPOSURE_DEFAULT_RC)/2 AS EXPOSURE_DEFAULT_RC_O, CMONTH.CIFNR, sum(CMONTH.CREDIT_RISK_RC)/2 AS CREDIT_RISK_RC_N, sum(CMONTH.EXPOSURE_DEFAULT_RC)/2 AS EXPOSURE_DEFAULT_RC_N
FROM CMONTH INNER JOIN PMONTH ON CMONTH.CIFNR = PMONTH.CIFNR
GROUP BY PMONTH.CIFNR, CMONTH.CIFNR
HAVING (((SUM(CMONTH.CREDIT_RISK_RC) - SUM(PMONTH.CREDIT_RISK_RC) > 2*10) OR (SUM(PMONTH.CREDIT_RISK_RC) - SUM(CMONTH.CREDIT_RISK_RC)) > 2*10 )) OR (((SUM(CMONTH.EXPOSURE_DEFAULT_RC) - SUM(PMONTH.EXPOSURE_DEFAULT_RC) > 2*10) OR (SUM(PMONTH.EXPOSURE_DEFAULT_RC) - SUM(CMONTH.EXPOSURE_DEFAULT_RC)) > 2*10 ))
UNION ALL
SELECT PMONTH.CIFNR, sum(PMONTH.CREDIT_RISK_RC) as CREDIT_RISK_RC_O, sum(PMONTH.EXPOSURE_DEFAULT_RC) as EXPOSURE_DEFAULT_RC_O, CMONTH.CIFNR, sum(CMONTH.CREDIT_RISK_RC) as CREDIT_RISK_RC_N, sum(CMONTH.EXPOSURE_DEFAULT_RC) as EXPOSURE_DEFAULT_RC_N
FROM CMONTH LEFT JOIN PMONTH on CMONTH.CIFNR = PMONTH.CIFNR
GROUP BY PMONTH.CIFNR, CMONTH.CIFNR
HAVING ((PMONTH.CIFNR is null) AND ((sum(CMONTH.EXPOSURE_DEFAULT_RC) > 10 OR sum(CMONTH.EXPOSURE_DEFAULT_RC) < -10) or (sum(CMONTH.CREDIT_RISK_RC) > 10 or sum(CMONTH.CREDIT_RISK_RC) < -10)))
UNION ALL SELECT PMONTH.CIFNR, sum(PMONTH.CREDIT_RISK_RC) as CREDIT_RISK_RC_O, sum(PMONTH.EXPOSURE_DEFAULT_RC) as EXPOSURE_DEFAULT_RC_O, CMONTH.CIFNR, sum(CMONTH.CREDIT_RISK_RC) as CREDIT_RISK_RC_N, sum(CMONTH.EXPOSURE_DEFAULT_RC) as EXPOSURE_DEFAULT_RC_N
FROM CMONTH RIGHT JOIN PMONTH on CMONTH.CIFNR = PMONTH.CIFNR
GROUP BY PMONTH.CIFNR, CMONTH.CIFNR
HAVING ((CMONTH.CIFNR is null) AND ((sum(PMONTH.EXPOSURE_DEFAULT_RC) > 10 OR sum(PMONTH.EXPOSURE_DEFAULT_RC) < -10) or (sum(PMONTH.CREDIT_RISK_RC) > 10 or sum(PMONTH.CREDIT_RISK_RC) < -10)))
I would extremly appreciate if someone of you expercts could give me an input and how to proceed.
Thanks you and best regards,
Laurenz