This forum is currently locked. You can't register or post questions at this time. (read more)

SQL query comparing databases

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

laurenz140
Member
Posts: 2
Joined: Mon Dec 10, 2012 4:17 pm

SQL query comparing databases

Postby laurenz140 » Mon Dec 10, 2012 4:55 pm

Dear experts

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: SQL query comparing databases

Postby Tim... » Mon Dec 10, 2012 5:26 pm

Hi.

Global temporary tables are possibly not what you think they are.

http://www.oracle-base.com/articles/8i/ ... tables.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

laurenz140
Member
Posts: 2
Joined: Mon Dec 10, 2012 4:17 pm

Re: SQL query comparing databases

Postby laurenz140 » Thu Dec 13, 2012 7:35 am

Dear Tim

Thank you for your quick reply and explanation.

However, could someone provide me with a hint how I could solve this issue? I would just need a catchword.

Thanks in advance,

Laurenz


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests