无法执行sql查询 [英] 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  

涉及的rdms是oracle，错误是

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. 阈值表包含一个列阈值类型，其中包含现金交易表的列名
2. 对于阈值表中的每条记录，我们需要根据现金交易表中的阈值类型比较 sum(amount) 组.
3. 并将获取的数量与阈值表的阈值数量进行比较
4. 我需要选择 threshold_id

阈值表:

Threshold_id        Threshold_type          Threshold_amount
============================================================
threshold_1         p_id                    450
threshold_2         p_id,to_acc_main_num    100 

现金交易表:

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

期望的输出:

让我们先取:阈值表中的第一条记录

Lets take the first fetch :the first record from the threshold table

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

1.现在阈值类型是p_id ok2.所以我需要从 cash_transactions 表中按 pid 分组.3.所以期望的结果是(但我必须仅根据 p_id 求和)在这种情况下不是 tran_inst_id

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.现在将上面的每条记录数量与 threshold_1 条记录的数量进行比较.2.so 450 threshold_amount for threshold_1 与以上所有记录进行比较3.所以所需的输出将是

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.         

假设如果阈值类型为空，那么我们不需要在查询中包含partition by part，那么如何获取?

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?

推荐答案

它只适用于动态 sql，因为 group by 子句中的列数是可变的.例如一个函数:

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

然后

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

由于新要求而进行

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

这篇关于无法执行sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！