oracle - Not able to execute sql query -

select tt.threshold_id   (select sum(amount) on (partition tt.threshold_type                                       order tt.threshold_type ) amt           cash_transactions) cash,        thresholds tt  tt.threshold_amount < cash.amt   

the rdms involved oracle error

" ora-00904: "tt"."threshold_type": invalid identifier" 

what want query :

1. threshold table contains column threshold type contain column name of cash transactions table
2. and each record threshold table need compare sum(amount) group threshold type cash transactions table .
3. and amount fetched compared threshold_amount of the threshold table
4. and need select threshold_id

thresholds table:

threshold_id        threshold_type          threshold_amount ============================================================ threshold_1         p_id                    450 threshold_2         p_id,to_acc_main_num    100  

cash transactions table:

tran_inst_id    p_id    amount    to_acc_main_num ================================================= 1               e1      100       123 2               e2      200       5765   3               e1      200       687 4               e2      300       890 5               e1      100       462 

desired output :

lets take first fetch :the first record threshold table

threshold_id        threshold_type          threshold_amount ============================================================ threshold_1         p_id                    100000 

1.now threshold_type p_id ok 2.so need group pid cash_transactions table. 3.so desired result (but have take sum on basis of p_id only) not tran_inst_id in case

tran_inst_id  p_id    sum(amount) ====================================== 1             e1        400 2             e2        500 3             e1        400 4             e2        500 5             e1        400 

1.now each records amount above compared amount of threshold_1 record. 2.so 450 threshold_amount threshold_1 compared above record 3.so required output be

theshold_id   tran_inst_id ================================== thresold_1      2 threshold_1     4  - above result first record of threshold table ,now same continues second record.          

edit:suppose if threshold_type null ,then not need include partition part in query ,then how can obtain?

it possible dynamic sql, since number of columns in group clause variable. example function:

    create or replace     function sum_cash_transactions ( p_threshold_type varchar2) return number           v_result number;     begin       execute immediate ' select max( sum_amount)                            from( select sum(amount) sum_amount                                   cash_transactions                                 group ' || p_threshold_type || ' )'      v_result;      return v_result;      end; / 

and then

select threshold_id  thresholds  threshold_amount < sum_cash_transactions(threshold_type); 

edit due new requirements:

create or replace package pkg   type res_rec_type record (     threshold_id  varchar2(200)   , tran_inst_id  number(10,0)   , sum_amount    number(22)   );   type res_tab_type table of res_rec_type;   function f1 return  res_tab_type pipelined; end; /  create or replace package body pkg    function f1 return  res_tab_type pipelined       cur    sys_refcursor;     v_rec  res_rec_type;   begin     treshold in ( select threshold_id,  threshold_type,   threshold_amount thresholds)     loop       open cur 'select ' || threshold.threshold_id || ', ttran_inst_id,  s (select  ttran_inst_id, sum(amount) on (partition ' || p_threshold_type || ') s cash_transactions ) s > ' || treshold.threshold_amount ;       loop         fetch cur v_rec;         exit when cur%notfound;         pipe row(v_rec);       end loop;     end loop;     close cur;     return;   end; end; /  select * form table(pkg.f1);