Home » SQL & PL/SQL » SQL & PL/SQL » Group by with nested sql
Group by with nested sql [message #321744] Wed, 21 May 2008 03:55 Go to next message
cgk_js
Messages: 48
Registered: December 2005
Member
I could not group by (highlighted in bold) using the nested sql in the select statement.

Please find below and contribute on this.

select 1 ,
a.Counterparty_original, replace( a.Counterparty_original,'-','')) cust,
(select f.country_code from companies f where a.booking_company=f.company_code and f.partition_key ='200802290000' and a.partition_key ='200802290007') ctry,
to_char(to_date(substr(a.partition_key,0,'8'),'YYYYMMDD'),'YYYY-MM-DD'),
a.Contract_Type ,
round(sum(a.PD_original),2) ,
round(sum(a.EAD),2) ,
round(sum(a.EAD*a.lgd),2) ,
round(sum(a.EL),2) ,
round(sum(b.Outstanding ),2) ,
round(sum(b.FAIR_VALUE),2) ,
round(sum(b.Add_On),2) ,
round(sum(a.RWA),2) ,
round(sum(a.RGC),2) ,
NULL,
NULL,
NULL,
NULL,
NULL
from t_cdr a , t_cr b
where a.partition_key = '200802290000'
and b.partition_key = '200802290000'
and a.contract_reference = b.contract_reference
group by
a.Counterparty_original ,
ctry,
a.partition_key,
a.Contract_Type

Thanks
Gopal

Re: Group by with nested sql [message #321748 is a reply to message #321744] Wed, 21 May 2008 04:17 Go to previous messageGo to next message
cgk_js
Messages: 48
Registered: December 2005
Member
i tried it randomly and got the solution,

we should use ctry.country_code in the group by.

we can close this....... Razz

Thanks
Gopal
Re: Group by with nested sql [message #321756 is a reply to message #321748] Wed, 21 May 2008 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, before posting, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Group by with nested sql [message #321757 is a reply to message #321744] Wed, 21 May 2008 04:32 Go to previous message
cgk_js
Messages: 48
Registered: December 2005
Member
i tried it randomly and got the solution,

i used like this,

select 1, a.cust,a.ctry, a.par,
a.Contract_Type ,
sum(a.PD_original),
sum(a.EAD) ,
sum(a.lgd),
sum(a.EL) ,
sum(a.outs),
sum(a.FV),
sum(a.Aon),
sum(a.RWA) ,
sum(a.RGC)
from
(select 1 ,
decode(instr(a.Counterparty_original,'-'),0, a.Counterparty_original, replace( a.Counterparty_original,'-','')) cust,
(select f.country_code from companies f where a.booking_company=f.company_code and f.partition_key ='200802290000' and a.partition_key ='200802290007') ctry,
to_char(to_date(substr(a.partition_key,0,Cool,'YYYYMMDD'),'YYYY-MM-DD') par,
a.Contract_Type Contract_Type ,
a.PD_original PD_original,
a.EAD EAD,
a.EAD*a.lgd lgd,
a.EL EL,
b.Outstanding outs,
b.FAIR_VALUE FV,
b.Add_On Aon,
a.RWA RWA,
a.RGC RGC,
NULL,
NULL,
NULL,
NULL,
NULL
from t_cdr a , t_cr b
where a.partition_key = '200802290000'
and b.partition_key = '200802290000'
and a.contract_reference = b.contract_reference
and rownum<10 ) a
group by
a.cust,a.ctry, a.par, a.Contract_Type


Thanks
Gopal
Previous Topic: how can i update by replacing string
Next Topic: PLS-00201: Compilation Error!
Goto Forum:
  


Current Time: Sat Dec 03 11:47:37 CST 2016

Total time taken to generate the page: 0.08544 seconds