Oracle Analytics Function Usage

Question

I am new to Oracle Analytics . Can any one help me to resolve the following.

SELECT year,
           month,
           week,
           C.cpg_pk                                                        CPG,
           C.dep_pk                                                        DEPT,
           T.cust_id                                                       CUST_ID,
           D1.r_id                                                         R_ID,
           Decode(d2.at_code, '3', func1.Get_att(d2.at_code, D2.val_code)) AS P1,
           Decode(d2.at_code, '2', func1.Get_att(d2.at_code, D2.val_code)) AS IC,
           Decode(d2.at_code, '1', func1.Get_att(d2.at_code, D2.val_code)) AS B1,
           Decode(func1.Get_att(d2.at_code, D2.val_code), 2, d2.at_code)   AS P2,
           Decode(func1.Get_att(d2.at_code, D2.val_code), 5, d2.at_code)   AS B2,
           Count(DISTINCT A.cust_id)                                       TOTAL_ACC
           ,
           Count(DISTINCT T.txn_pk)
           TOTAL_TXN,
           SUM(am_amount)                                                  TOTAL_AMT
    FROM   t_header T,
           cust_master A,
           tx_details1 D1,
           tx_details2 D2,
           cpg_master C
    WHERE  A.TYPE = 0
           AND T.cust_id = A.cust_id
           AND T.txn_pk = 5001
           AND T.txn_pk = D1.txn_pk
           AND T.txn_pk = D2.txn_pk
           AND D1.cpg_pk = C.cpg_pk
           AND D1.op = 1
    GROUP  BY year,
              month,
              week,
              C.cpg_pk,
              C.dep_pk,
              t.cust_id,
              D1.r_id,
              Decode(d2.at_code, '3', func1.Get_att(d2.at_code, D2.val_code)),
              Decode(d2.at_code, '2', func1.Get_att(d2.at_code, D2.val_code)),
              Decode(d2.at_code, '1', func1.Get_att(d2.at_code, D2.val_code)),
              Decode(func1.Get_att(d2.at_code, D2.val_code), 2, d2.at_code),
              Decode(func1.Get_att(d2.at_code, D2.val_code), 5, d2.at_code)

Its generated output is as follows:

YEAR    MONTH   WEEK    CPG DEPT    CUST_ID R_ID    P1  IC  B1  P2  B2   TOTAL
2012    08      32     127 -1      10019   3665                     134   23100.09   
2012    08      32     127 -1      10019   3665                     135   23100.09  
2012    08      32     127 -1      10019   3665             723           23100.09  
2012    08      32     127 -1      10019   3665        714                23100.09
2012    08      32     127 -1      10019   3665                  41       23100.09
2012    08      32     127 -1      10019   3665                  42       23100.09
2012    08      32     127 -1      10019   3665     21                    23100.09
2012    08      32     128 -1      10019   3665                      134  23100.09 
2012    08      32     128 -1      10019   3665                      135  23100.09 
2012    08      32     128 -1      10019   3665             723           23100.09 
2012    08      32     128 -1      10019   3665        714                23100.09 
2012    08      32     128 -1      10019   3665                  41       23100.09
2012    08      32     128 -1      10019   3665                  42       23100.09
2012    08      32     128 -1      10019   3665     21                    23100.09

Here the values are repeating. I tried to eliminate the repeating with a group by, but didn't succeed. Could you help me?

The required output is this:

YEAR    MONTH   WEEK    CPG DEPT    CUST_ID R_ID    P1  IC  B1  P2  B2  TOTAL_AMT
---------------------------------------------------------------------------------
2012    08      32      127 -1      10019   3665    21  714 723  41   134 23100.09
2012    08      32      127 -1      10019   3665    21  714 723  41   135 23100.09
2012    08      32      127 -1      10019   3665    21  714 723  42   134 23100.09
2012    08      32      127 -1      10019   3665    21  714 723  42   135 23100.09
2012    08      32      128 -1      10019   3665    21  714 723  41   134 23100.09
2012    08      32      128 -1      10019   3665    21  714 723  41   135 23100.09
2012    08      32      128 -1      10019   3665    21  714 723  42   134 23100.09
2012    08      32      128 -1      10019   3665    21  714 723  42   135 23100.09

The main thing is year, month, week, cpg, dept, cust_id, r_id, p1, ic, b1, p2, b2 it should be unique row. Is it achievable using analytical functions or do I need to write PL/SQL? is my question is sensable ?

possible duplicate of [Can I use Oracle analytical function here?](http://stackoverflow.com/questions/12614209/can-i-use-oracle-analytical-function-here) — Alex Poole, Oct 20 '12 at 07:39
So respond to Tony's answer saying why, and improve the original question - don't ask an identical question. Please read the [FAQ](http://stackoverflow.com/faq). — Alex Poole, Oct 20 '12 at 09:54

score 1 · Accepted Answer · answered Oct 27 '12 at 04:08

with t1 as( select 2012 YEAR1, 08 MONTH1, 32 WEEK, 127 CPG, -1 DEPT, 10019 CUST_ID, 3665 R_ID, null P1, null IC, 723 B1,null P2, null B2, 23100.09 TOTAL from dual union all -- select 2012, 08, 32, 127, -1, 10019, 3665, null, null, null, null, 135 , 23100.09 from dual union all -- select 2012, 08, 32, 127, -1, 10019, 3665, null, null, null , null, 134, 23100.09 from dual union all select 2012, 08, 32, 127, -1, 10019, 3665, null, 714 , null, null, null, 23100.09 from dual union all -- select 2012, 08, 32, 127, -1, 10019, 3665, null, null ,null, 41, null, 23100.09 from dual union all -- select 2012, 08, 32, 127, -1, 10019, 3665, null, null, null, 42, null, 23100.09 from dual union all -- select 2012, 08, 32, 127, -1, 10019, 3665, 21 , null, null, null, null, 23100.09 from dual union all -- select 2012, 08, 32, 128, -1, 10019, 3665, null, null, null, null, 134 , 23100.09 from dual union all -- select 2012, 08, 32, 128, -1, 10019, 3665, null, null, null, null, 135 , 23100.09 from dual union all select 2012, 08, 32, 128, -1, 10019, 3665, null, null, 723 , null, null, 23100.09 from dual union all select 2012, 08, 32, 128, -1, 10019, 3665, null, 714 , null, null, null, 23100.09 from dual union all -- select 2012, 08, 32, 128, -1, 10019, 3665, null, null ,null, 41, null, 23100.09 from dual union all -- select 2012, 08, 32, 128, -1, 10019, 3665, null, null, null, 42, null, 23100.09 from dual union all -- select 2012, 08, 32, 128, -1, 10019, 3665, 21 , null, null, null, null, 23100.09 from dual ) select t1p1.YEAR1, t1p1.month1, t1p1.week, t1p1.cpg, t1p1.dept, t1p1.cust_id, t1p1.r_id, t1p1.p1,t1ic.ic, t1b1.b1, t1p2.p2, t1b2.b2, t1p1.total from (select YEAR1, month1, week, cpg, dept, cust_id, r_id, p1 , total from t1 where p1 is not null) t1p1, (select YEAR1, month1, week, cpg, dept, cust_id, r_id, ic from t1 where ic is not null) t1ic, (select YEAR1, month1, week, cpg, dept, cust_id, r_id, b1 from t1 where b1 is not null) t1b1, (select YEAR1, month1, week, cpg, dept, cust_id, r_id, p2 from t1 where p2 is not null) t1p2, (select YEAR1, month1, week, cpg, dept, cust_id, r_id, b2 from t1 where b2 is not null) t1b2 where t1p1.year1 = t1ic.year1(+) and t1p1.year1 = t1b1.year1(+) and t1p1.year1 = t1p2.year1(+) and t1p1.year1 = t1b2.year1(+) and t1p1.month1 = t1ic.month1(+) and t1p1.month1 = t1b1.month1(+) and t1p1.month1 = t1p2.month1(+) and t1p1.month1 = t1b2.month1(+) and t1p1.week = t1ic.week(+) and t1p1.week = t1b1.week(+) and t1p1.week = t1p2.week(+) and t1p1.week = t1b2.week(+) and t1p1.cpg = t1ic.cpg(+) and t1p1.cpg = t1b1.cpg(+) and t1p1.cpg = t1p2.cpg(+) and t1p1.cpg = t1b2.cpg(+) and t1p1.dept = t1ic.dept(+) and t1p1.dept = t1b1.dept(+) and t1p1.dept = t1p2.dept(+) and t1p1.dept = t1b2.dept(+) and t1p1.cust_id = t1ic.cust_id(+) and t1p1.cust_id = t1b1.cust_id(+) and t1p1.cust_id = t1p2.cust_id (+)and t1p1.cust_id = t1b2.cust_id(+) and t1p1.r_id = t1ic.r_id(+) and t1p1.r_id = t1b1.r_id(+) and t1p1.r_id = t1p2.r_id (+) and t1p1.r_id = t1b2.r_id(+) order by t1p1.YEAR1, t1p1.month1, t1p1.week, t1p1.cpg, t1p1.dept, t1p1.cust_id, t1p1.r_id, t1p1.p1,t1ic.ic, t1b1.b1, t1p2.p2, t1b2.b2 /

this works fine after adding outerjoin and splits all attribute as individual query — user1760970, Oct 27 '12 at 04:09

Oracle Analytics Function Usage

1 Answers1