Not able to execute sql query

select tt.threshold_id
from   (select sum(amount) over (partition by tt.threshold_type 
                                     order by tt.threshold_type ) amt
        from   cash_transactions) cash,
       thresholds tt
where  tt.threshold_amount < cash.amt  

the rdms involved is oracle the error is

" ORA-00904: "TT"."THRESHOLD_TYPE": invalid identifier"

what i want to do with this query is :

1. threshold table contains a column threshold type which contain column name of the c开发者_JAVA百科ash transactions table
2. And for each record from the threshold table we need to compare the sum(amount) group by the threshold type from cash transactions table .
3. and the amount fetched is compared with the threshold_amount of the the threshold table
4. and i need to select the 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 the first fetch :the first record from the threshold table

Threshold_id        Threshold_type          Threshold_amount
============================================================
threshold_1         p_id                    100000

1.now the threshold_type is p_id ok 2.So i need to group by pid from cash_transactions table. 3.so the desired result from this is (but i have to take sum on basis of p_id only) not tran_inst_id in this 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 is compared with the amount of threshold_1 record. 2.so 450 threshold_amount for threshold_1 is compared with all the above record 3.so the required output will be

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

EDIT:Suppose if the threshold_type is null ,then we not need to include partition by part in the query ,then how it can be obtain?

---

It is only possible with dynamic sql, since the number of columns in the group by clause is variable. For example with a function:

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

and then

select threshold_id
 from thresholds
 where threshold_amount < sum_cash_transactions(threshold_type);

EDIT due to new requirements:

CREATE OR REPLACE package pkg AS
  TYPE res_rec_type IS RECORD (
    threshold_id  VARCHAR2(200)
  , Tran_inst_id  NUMBER(10,0)
  , sum_amount    NUMBER(22)
  );
  TYPE res_tab_type IS TABLE of res_rec_type;
  FUNCTION f1 RETURN  res_tab_type PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg AS

  FUNCTION f1 RETURN  res_tab_type PIPELINED
  IS
    CUR    SYS_REFCURSOR;
    v_rec  res_rec_type;
  BEGIN
    FOR treshold in ( SELECT Threshold_id,  Threshold_type,   Threshold_amount FROM thresholds)
    LOOP
      OPEN CUR FOR 'SELECT ' || threshold.Threshold_id || ', tTran_inst_id,  s FROM (SELECT  tTran_inst_id, SUM(AMOUNT) OVER (PARTITION BY ' || p_Threshold_type || ') as s from cash_transactions ) WHERE s > ' || treshold.Threshold_amount ;
      LOOP
        FETCH cur INTO v_rec;
        EXIT WHEN cur%NOTFOUND;
        pipe row(v_rec);
      END LOOP;
    END LOOP;
    CLOSE cur;
    RETURN;
  END;
END;
/

SELECT * form table(pkg.f1);

---

Your immediate issue is that tt is not in the scope of the subquery on cash_transactions, so you can't reference threshold_type in that subquery. The syntactical fix for this would be to convert to a join, with a normal group by.

However, this still won't accomplish what you want, because it will group by the values contained in threshold_type -- it won't be interpreted as a list of columns to actually group by. The query parser has to know the columns being referenced at parse time, and what you are trying to do is determine them at execution time.

The standard way to deal with this is dynamic SQL, where you construct the query as a string in procedural code, then explicitly parse and execute it. A simple implementation in PL/SQL would look like:

FOR tt in (SELECT * from thresholds) LOOP
  OPEN a_refcursor FOR 'SELECT SUM(amount) FROM cash_transactions GROUP BY '
                       ||tt.threshold_type||
                       ' HAVING SUM(amount) > '||TO_CHAR(tt.threshold_amount);
  LOOP
    FETCH a_refcursor INTO local_var;
    EXIT WHEN a_refcursor%NOTFOUND;
    -- do whatever it is you want to do with the information here
  END LOOP;
END LOOP;

However, I'm guessing that you would like to know the values of the dynamic columns for which the threshold has been exceeded. That can easily be added to the query:

  OPEN a_refcursor FOR 'SELECT '|| tt.threshold_type ||
                       ',SUM(amount) FROM cash_transactions GROUP BY '
                       ||tt.threshold_type||
                       ' HAVING SUM(amount) > '||TO_CHAR(tt.threshold_amount);

But the difficulty is that the number of columns returned by the dynamic query is now variable, so the FETCH statement will not work. In this case you would have to use calls to DBMS_SQL to handle the dynamic SQL, which allows you to fetch column values by position.