Not able to execute sql query

Question

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 :

threshold table contains a column threshold type which contain column name of the cash transactions table
And for each record from the threshold table we need to compare the sum(amount) group by the threshold type from cash transactions table .
and the amount fetched is compared with the threshold_amount of the the threshold table
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 would be easier to read if you put it in code and formatted the lines so you can see what is missing. My guess is you are missing a from statement for your inner select, but I don't have Oracle, so I can't test it... — Paul W
– Paul W, Commented May 11, 2011 at 11:49
@marc_s :yes ,i have not mentioned the details...sorry for that — gaurav
– gaurav, Commented May 11, 2011 at 11:55
Please provide the output of desc thresholds and desc cash_transactions and a few sample rows of both tables that belong together. With the current info, it is hard to understand what you are trying to achieve. — Daniel Hilgarth
– Daniel Hilgarth, Commented May 11, 2011 at 11:57

schurik · Accepted Answer · 2011-05-12 09:42:09Z

2

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);

edited May 12, 2011 at 9:42

answered May 11, 2011 at 12:40

schurik

7,9582 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Dave Costa Over a year ago

The question is unclear about the output desired, but I suspect he doesn't just want the IDs of the thresholds that are exceeded by some set of transactions -- he probably wants each set of transactions that exceeds a threshold. But your answer gives me an interesting idea -- if that function were converted to a pipeline that can return multiple rows, then it could be called from a simple query as you suggest and return all the information that I think would be desired.

schurik Over a year ago

@dave costa the OP has clearly written: "select id threshold" :), but yes you are right, one is much more flexible with a pipline function.

gaurav Over a year ago

@schurik- lets take the 1st fetch ....suppose the inner query returns 10 result ,so the inner query has ten sum_amount ,which is to be compared with the 1st record of the threshold table,and the corresponding threshold_id is to be fetched

gaurav Over a year ago

@schurik- above solution will not give the required answer

schurik Over a year ago

@gaurav the inner query return the maximum of Sums pro group from threshold_type. For you testdata the function will return 500 for threshold_1 and 300 for threshold_2. Than you compare this values with threshold_amount in the query. "threshold_2" will be return from the query, while 100 < 300. "threshold_1" will be not return from the query while 1000 > 500. What is the require answer?

|

Dave Costa · Accepted Answer · 2011-05-11 12:46:17Z

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.

Collectives™ on Stack Overflow

Not able to execute sql query

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related