当前位置: 代码迷 >> Oracle开发 >> 请Proc 下SQL 话语能用 oracle 函数ROW_NUMBER()和 rank()吗
  详细解决方案

请Proc 下SQL 话语能用 oracle 函数ROW_NUMBER()和 rank()吗

热度:520   发布时间:2016-04-24 06:33:50.0
请Proc 下SQL 语句能用 oracle 函数ROW_NUMBER()和 rank()吗
高分求解???急、急、急!!!
请Proc 下SQL 语句能用 oracle 函数ROW_NUMBER()和 rank()吗?
在oralce 查询是没有问题的,在ProC程序编译时,报错
Syntax error at line 1091, column 33, file src/Database.pc:
Error at line 1091, column 33 in file src/Database.pc
               ACCT_ITEM_TYPE_ID,
................................1
PCC-S-02201, Encountered the symbol "(" when expecting one of the following:

   , into, from,

Error at line 0, column 0 in file src/Database.pc

SELECT t.ACCT_ID,
      t.SERV_ID,
      t.ACC_NBR,
      t.ACCT_ITEM_TYPE_ID,
      nvl(t.PRODUCT_ID, 0),
      nvl(t.CHARGE, 0)
  FROM (SELECT ACCT_ID,
               SERV_ID,
               ACC_NBR,
               ACCT_ITEM_TYPE_ID,
               PRODUCT_ID,
               CHARGE,
               DEAL_TIME,
               DURATION,
               FLOWS,
               ROW_NUMBER() OVER(PARTITION BY ACCT_ID, SERV_ID, ACC_NBR, ACCT_ITEM_TYPE_ID ORDER BY DEAL_TIME desc, DURATION desc, FLOWS desc) NUM
          FROM TICKET_MM_01) t
 WHERE t.NUM = 1;
------解决思路----------------------
在procedure里面,你select出来的东西要放到变量里面去。

如:
decalre
v_name varchar2(20);
begin
select name into v_name from tesb_table;
end;
------解决思路----------------------
当然可以用了,给你个示例:
SELECT B.BOND_INNER_ID,
                           B.BOND_ID,
                           B.BOND_CODE,
                           B.BOND_SHORT_NAME,
                           B.SEC_MAR_PAR,
                           A.DMI_BSNS_DATE as trade_date,
                           A.DMI_PRVS_CLSNG_RATE AS NET_PRE_CLOS_PRI,
                           A.DMI_OPNG_RATE AS NET_OPEN_PRI,
                           A.DMI_HIGHST_RATE AS HIGH_NET,
                           A.DMI_LWST_RATE AS LOW_NET, ---DMI_LWST_RATE
                           A.DMI_CLSNG_RATE AS CLOS_NET,
                           NULL AS AVE_NET,
                           A.DMI_NET_RATE_CHGE_RATE AS RISE_DROP_NET,
                           NULL AS VIBR_NET,
                           NULL AS FULL_PRE_CLOS,
                           NULL AS FULL_OPEN_CLOS,
                           NULL AS FULL_HIGH_CLOS,
                           NULL AS FULL_LOW_CLOS,
                           NULL AS FULL_CLOS_CLOS,
                           NULL AS FULL_AVE_CLOS,
                           NULL AS FULL_RISE_DROP,
                           NULL AS FULL_VIBR,
                           A.TURNOVER_RATE AS TURNOVER_RATE,
                           NULL AS DEAL_COUNT,
                           NULL AS DEAL_VOL,
                           A.DMI_TTL_TRADED_AMNT AS DEAL_AMUT,
                           A.DMI_OPNG_CONTRA_RATE AS OPEN_LIMIT_YIELD,
                           A.DMI_HIGHST_CONTRA_RATE AS HIGH_LIMIT_YIELD,
                           A.DMI_LWST_CONTRA_RATE AS LOW_LIMIT_YIELD,
                           A.DMI_CLSNG_CONTRA_RATE AS CLOS_LIMIT_YIELD,
                           NULL AS AVE_LIMIT_YIELD,
                           DENSE_RANK() OVER(PARTITION BY A.DMI_MKT_INFRMN_CRTR_DESC ORDER BY A.DMI_BSNS_DATE DESC) RANKNUM
                      FROM V_T_CBT_MARKET_LAST A
                      LEFT JOIN APPDATA.TI_BND_CODE_CONT B
                        ON A.DMI_MKT_INFRMN_CRTR_DESC = B.BOND_CODE
                     WHERE B.BOND_ID = V_BOND_ID
                       AND A.DMI_BSNS_DATE < V_TRADE_DATE
                     ORDER BY DMI_BSNS_DATE DESC
  相关解决方案