DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Denormalize SQL Query ???

  1. #1
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80

    Arrow

    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

  2. #2
    Join Date
    Feb 2001
    Posts
    123
    Take a look at this thread 'can we generate a matrix report in oracle?' - an ongoing discussion on this very topic.

    HTH

    David.

  3. #3
    Join Date
    Feb 2001
    Posts
    180
    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

  4. #4
    Join Date
    Mar 2001
    Posts
    1
    Hi !!

    I feel this will work,

    << SELECT col1||' '||col2||' ' FROM table_name; >>

    If it works in your case then cheers !!!

    Shan

  5. #5
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    [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
  •  


Click Here to Expand Forum to Full Width