OraFAQ Forum: SQL & PL/SQL » Group by with nested sql

Home » SQL & PL/SQL » SQL & PL/SQL » Group by with nested sql

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Group by with nested sql [message #321744]

Wed, 21 May 2008 03:55

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

Report message to a moderator

Re: Group by with nested sql [message #321748 is a reply to message #321744]

Wed, 21 May 2008 04:17

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

Report message to a moderator

Re: Group by with nested sql [message #321756 is a reply to message #321748]

Wed, 21 May 2008 04:32

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Group by with nested sql [message #321757 is a reply to message #321744]

Wed, 21 May 2008 04:32

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 00:03:39 CST 2024