-
Hello,
I want result of following query in one row. This Formno 13534 has 3 risks, which I want to denorlmalize into one record.
So result will show like this...
formno risk1 risk2 risk3, desp
13534 1 2 3 SSR
I tried following way. which gives 3 records. Table has Formno,risk,desp fields. Please correct me in this.
select formno,decode(rownum,1,risk) risk1,decode(rownum,2,risk) risk2,decode(rownum,3,risk) risk3,desp from Formrisks where Desp='SSR'and Fromno=13534 and rownum<=3;
Thanks & Regards
Shailesh
-
Take a look at this thread 'can we generate a matrix report in oracle?' - an ongoing discussion on this very topic.
HTH
David.
-
FIRST OPTION IS THE NEXT:
select f1.formno
, f1.risk risk1
, f2.risk risk2
, f3.risk risk3
, f1.desp
from Formrisks f1
, Formrisks f2
, Formrisks f3
where f1.Desp = F2.Desp
and f1.Desp = f3.Desp
and f1.Desp ='SSR'
and f1.Fromno = f2.Fromno
and f1.Fromno = f3.Fromno
and f1.Fromno = 13534
and f1.risk < f2.risk
and f2.risk < f3.risk
;
better maybe to define a function
with a cursor to retrieve all the risk values
and put them in a return field
create or replace function get_risks(p_desp Formrisks.Desp%type
,p_fromno Formrisks.Fromno%type)
return varchar
is
cursor c_risk(p_desp Formrisks.Desp%type
,p_fromno Formrisks.Fromno%type)
is
select risk
from Formrisks
where Desp = p_desp
and Fromno = p_fromno
;
v_Result varchar2(60) ;
begin
for vc_risk in c_risk(p_desp, p_fromno)
loop
if v_Result is Null
then
v_Result := vc_risk.risk;
else
v_Result := v_Result||' - '||vc_risk.risk;
end if;
end loop;
RETURN v_Result;
end get_risks;
Regards
Ben de Boer
-
Hi !!
I feel this will work,
<< SELECT col1||' '||col2||' ' FROM table_name; >>
If it works in your case then cheers !!!
Shan
-
[QUOTE][i]Originally posted by bensr [/i]
[B]FIRST OPTION IS THE NEXT:
select f1.formno
, f1.risk risk1
, f2.risk risk2
, f3.risk risk3
, f1.desp
from Formrisks f1
, Formrisks f2
, Formrisks f3
where f1.Desp = F2.Desp
and f1.Desp = f3.Desp
and f1.Desp ='SSR'
and f1.Fromno = f2.Fromno
and f1.Fromno = f3.Fromno
and f1.Fromno = 13534
and f1.risk < f2.risk
and f2.risk < f3.risk
;
better maybe to define a function
with a cursor to retrieve all the risk values
and put them in a return field
create or replace function get_risks(p_desp Formrisks.Desp%type
,p_fromno Formrisks.Fromno%type)
return varchar
is
cursor c_risk(p_desp Formrisks.Desp%type
,p_fromno Formrisks.Fromno%type)
is
select risk
from Formrisks
where Desp = p_desp
and Fromno = p_fromno
;
v_Result varchar2(60) ;
begin
for vc_risk in c_risk(p_desp, p_fromno)
loop
if v_Result is Null
then
v_Result := vc_risk.risk;
else
v_Result := v_Result||' - '||vc_risk.risk;
end if;
end loop;
RETURN v_Result;
end get_risks; [/B][/QUOTE]
Dear Friends
Thanks all for giving suggestions. I have done following way which gives correct result for any one formno. But when I remove formno in where condition I gives wrong output. Here there can be upto 100 risks for 1 formno, but I want only first 9 of each formno. Please correct my query incase anyone knows about it.
Thanks & Regards
Shailesh
select
max(decode(mod(rownum,100),1,risk)) risk1
,max(decode(mod(rownum,100),2,risk)) risk2
,max(decode(mod(rownum,100),3,risk)) risk3
,max(decode(mod(rownum,100),4,risk)) risk4
,max(decode(mod(rownum,100),5,risk)) risk5
,max(decode(mod(rownum,100),6,risk)) risk6
,max(decode(mod(rownum,100),7,risk)) risk7
,max(decode(mod(rownum,100),8,risk)) risk8
,max(decode(mod(rownum,100),9,risk)) risk9
,formno,desp
from Formrisks where desp='SSR' and formno=13533
group by trunc(mod(rownum,9)/9),formno,desp;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|