Home » SQL & PL/SQL » SQL & PL/SQL » summation of result of two queries
summation of result of two queries [message #304798] Thu, 06 March 2008 14:40 Go to next message
gauravpuri2002
Messages: 24
Registered: October 2007
Location: Bangalore
Junior Member
hi

I am exeuting the following query( here LKPCA_GEOGRAPHY.LKPN_GEOGRAPHY=2)


SELECT CASE WHEN (L2_COUNTERPARTY_CA.TYP_GEOGRAPHY BETWEEN 1 AND 14) THEN 'TOTAL CANADA'
ELSE LKPCA_GEOGRAPHY.DES_DESCRIPTION END AS GEOGRAPHY,
CASE WHEN LKP_EXPOSURE_CLASS.DES_DESCRIPTION = 'Retail/secured by real estate' THEN 'MORTGAGE'
WHEN LKP_EXPOSURE_CLASS.DES_DESCRIPTION = 'Retail/heloc' THEN 'HELOC'
WHEN LKP_EXPOSURE_CLASS.DES_DESCRIPTION = 'Retail/Credit card' THEN 'CREDIT CARD'
WHEN LKP_EXPOSURE_CLASS.DES_DESCRIPTION = 'Retail/Line of credit' THEN 'LINE OF CREDIT'
WHEN LKP_EXPOSURE_CLASS.DES_DESCRIPTION = 'Retail/Other retail' THEN 'OTHER RETAIL'
ELSE LKP_EXPOSURE_CLASS.DES_DESCRIPTION END AS RETAIL_EXPOSURES,
CASE WHEN L2_NCR_RETAIL_PORTFOLIO.TYP_PD_BAND >= 10 THEN CONCAT ('PD BAND ',L2_NCR_RETAIL_PORTFOLIO.TYP_PD_BAND)
ELSE CONCAT ('PD BAND 0',L2_NCR_RETAIL_PORTFOLIO.TYP_PD_BAND) END AS PD_BAND,
(L2_NCR_RETAIL_PORTFOLIO.PER_WA_PD*100) AS PD_BAND_PERCENT,
SUM(L2_NCR_RETAIL_PORTFOLIO.RCA_AUTHORISED) AS AUTHORISED

FROM FRS_FA.L2_NCR_RETAIL_PORTFOLIO L2_NCR_RETAIL_PORTFOLIO,
FRS_FA.L2_COUNTERPARTY_CA L2_COUNTERPARTY_CA,
FRS_FA.LKPCA_GEOGRAPHY LKPCA_GEOGRAPHY,
FRS_FA.LKP_EXPOSURE_CLASS LKP_EXPOSURE_CLASS

WHERE L2_NCR_RETAIL_PORTFOLIO.LOT_TYPE_FK = 1
AND L2_NCR_RETAIL_PORTFOLIO.LOT_TYPE_FK = L2_COUNTERPARTY_CA.LOT_TYPE_FK
AND L2_NCR_RETAIL_PORTFOLIO.IDE_COUNTERPARTY_REF = L2_COUNTERPARTY_CA.IDE_COUNTERPARTY_REF
AND L2_COUNTERPARTY_CA.TYP_GEOGRAPHY = LKPCA_GEOGRAPHY.LKPN_GEOGRAPHY
AND LKPCA_GEOGRAPHY.LKPN_GEOGRAPHY=2
AND L2_NCR_RETAIL_PORTFOLIO.TYP_EXPOSURE_CLASS = LKP_EXPOSURE_CLASS.LKPN_EXPOSURE_CLASS
AND L2_NCR_RETAIL_PORTFOLIO.TYP_SECURITISATION = 0
AND L2_NCR_RETAIL_PORTFOLIO.VALID_ON=20071031

GROUP BY L2_COUNTERPARTY_CA.TYP_GEOGRAPHY,
LKPCA_GEOGRAPHY.DES_DESCRIPTION,
LKP_EXPOSURE_CLASS.DES_DESCRIPTION,
L2_NCR_RETAIL_PORTFOLIO.TYP_PD_BAND,
L2_NCR_RETAIL_PORTFOLIO.PER_WA_PD;

it gives result as

TOTAL CANADA HELOC PD BAND 03 0.05 23275858310
TOTAL CANADA HELOC PD BAND 08 0.15 567959694.3
TOTAL CANADA HELOC PD BAND 10 0.35 4917072140
TOTAL CANADA HELOC PD BAND 12 0.75 40098608.7
TOTAL CANADA HELOC PD BAND 15 1.7 720106780


now I am execting same query (with LKPCA_GEOGRAPHY.LKPN_GEOGRAPHY=3)
as

SELECT CASE WHEN (L2_COUNTERPARTY_CA.TYP_GEOGRAPHY BETWEEN 1 AND 14) THEN 'TOTAL CANADA'
ELSE LKPCA_GEOGRAPHY.DES_DESCRIPTION END AS GEOGRAPHY,
CASE WHEN LKP_EXPOSURE_CLASS.DES_DESCRIPTION = 'Retail/secured by real estate' THEN 'MORTGAGE'
WHEN LKP_EXPOSURE_CLASS.DES_DESCRIPTION = 'Retail/heloc' THEN 'HELOC'
WHEN LKP_EXPOSURE_CLASS.DES_DESCRIPTION = 'Retail/Credit card' THEN 'CREDIT CARD'
WHEN LKP_EXPOSURE_CLASS.DES_DESCRIPTION = 'Retail/Line of credit' THEN 'LINE OF CREDIT'
WHEN LKP_EXPOSURE_CLASS.DES_DESCRIPTION = 'Retail/Other retail' THEN 'OTHER RETAIL'
ELSE LKP_EXPOSURE_CLASS.DES_DESCRIPTION END AS RETAIL_EXPOSURES,
CASE WHEN L2_NCR_RETAIL_PORTFOLIO.TYP_PD_BAND >= 10 THEN CONCAT ('PD BAND ',L2_NCR_RETAIL_PORTFOLIO.TYP_PD_BAND)
ELSE CONCAT ('PD BAND 0',L2_NCR_RETAIL_PORTFOLIO.TYP_PD_BAND) END AS PD_BAND,
(L2_NCR_RETAIL_PORTFOLIO.PER_WA_PD*100) AS PD_BAND_PERCENT,
SUM(L2_NCR_RETAIL_PORTFOLIO.RCA_AUTHORISED) AS AUTHORISED

FROM FRS_FA.L2_NCR_RETAIL_PORTFOLIO L2_NCR_RETAIL_PORTFOLIO,
FRS_FA.L2_COUNTERPARTY_CA L2_COUNTERPARTY_CA,
FRS_FA.LKPCA_GEOGRAPHY LKPCA_GEOGRAPHY,
FRS_FA.LKP_EXPOSURE_CLASS LKP_EXPOSURE_CLASS

WHERE L2_NCR_RETAIL_PORTFOLIO.LOT_TYPE_FK = 1
AND L2_NCR_RETAIL_PORTFOLIO.LOT_TYPE_FK = L2_COUNTERPARTY_CA.LOT_TYPE_FK
AND L2_NCR_RETAIL_PORTFOLIO.IDE_COUNTERPARTY_REF = L2_COUNTERPARTY_CA.IDE_COUNTERPARTY_REF
AND L2_COUNTERPARTY_CA.TYP_GEOGRAPHY = LKPCA_GEOGRAPHY.LKPN_GEOGRAPHY
AND LKPCA_GEOGRAPHY.LKPN_GEOGRAPHY=3
AND L2_NCR_RETAIL_PORTFOLIO.TYP_EXPOSURE_CLASS = LKP_EXPOSURE_CLASS.LKPN_EXPOSURE_CLASS
AND L2_NCR_RETAIL_PORTFOLIO.TYP_SECURITISATION = 0
AND L2_NCR_RETAIL_PORTFOLIO.VALID_ON=20071031

GROUP BY L2_COUNTERPARTY_CA.TYP_GEOGRAPHY,
LKPCA_GEOGRAPHY.DES_DESCRIPTION,
LKP_EXPOSURE_CLASS.DES_DESCRIPTION,
L2_NCR_RETAIL_PORTFOLIO.TYP_PD_BAND,
L2_NCR_RETAIL_PORTFOLIO.PER_WA_PD;

it gives result like

TOTAL CANADA HELOC PD BAND 03 0.05 29540662820
TOTAL CANADA HELOC PD BAND 08 0.15 240593810.4
TOTAL CANADA HELOC PD BAND 10 0.35 5355086550
TOTAL CANADA HELOC PD BAND 12 0.75 24897665.1
TOTAL CANADA HELOC PD BAND 15 1.7 731847260

now i want sum of these quieries as
TOTAL CANADA HELOC PD BAND 03 0.05 52816521130.00
(52816521130.00=23275858310+29540662820)

regards
gaurav

Re: summation of result of two queries [message #304804 is a reply to message #304798] Thu, 06 March 2008 15:52 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
please by all means do it.

Regards

Raj
Re: summation of result of two queries [message #304811 is a reply to message #304798] Thu, 06 March 2008 16:00 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Youarereallygoingtohavetoformatyourpostbyreadingtherulesoftheforumifyouwantpeopletoprovideyouananswer.

http://www.orafaq.com/forum/t/88153/0/

[Updated on: Thu, 06 March 2008 16:01]

Report message to a moderator

Previous Topic: ORA-22905 Problem
Next Topic: How to use TO_CHAR to get week day and filter by weekend days
Goto Forum:
  


Current Time: Sun Dec 04 16:33:28 CST 2016

Total time taken to generate the page: 0.10906 seconds