過濾儲存過程結果 (Informix)

IBM Informix 動態伺服器版本 11.50.UC5XA。

我有一個 Informix 數據庫。此數據庫包含一個名為“sp_agent_details”的儲存過程，該過程獲取兩個 DateTime 參數。這個儲存過程產生了大約 27 列，我只想要其中的幾列用於報告。我嘗試這種語法：

select AGENT_NAME, AGENT_LOGIN_ID from 
      TABLE(sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00'))

這個結果這樣的錯誤：

java.sql.SQLException: Illegal SQL statement in SPL routine.

接下來我試試這個語法：

select AGENT_NAME, AGENT_LOGIN_ID from 
      TABLE(MULTISET{sp_agent_call_summary 
   ('2014-02-04 04:00:00', '2014-02-04 23:00:00')})

結果：

java.sql.SQLException: Function (informix.sp_agent_call_summary) 
returns too many values.

然後我嘗試了這個：

select AGENT_NAME, AGENT_LOGIN_ID from 
  TABLE(sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00'))
  (AGENT_NAME, AGENT_LOGIN_ID, other columns that are returned by SP)

結果：

java.sql.SQLException: Illegal SQL statement in SPL routine.

這是如何工作的說明。

這是在 Informix 11.50 FC9 中使用 dbaccess 執行的。您可以命名您的返回參數，但它們僅在通過 self 將過程作為過程執行時才有效……而不是在 select 語句中。在那裡您需要手動命名每一列…檢查下面。

IBM Informix 11.50 手冊中的參考資料：

• 從語法中選擇…
• 過程命名返回參數

---

create procedure dummy ( param integer )
returning int as id , char(10) as name, date as created
define vid int;;
define vname char(10);;
define vcreate date;;

foreach c1 for 
 select first 10 tabid, tabname, created 
    into vid, vname, vcreate 
    from systables where tabid >= param

 return vid , vname, vcreate with resume ;;
end foreach ;;

end procedure
;
Routine created.

;

execute procedure dummy(10);

        id name       created

        10 syssyntabl 20/02/2014
        11 sysconstra 20/02/2014
        12 sysreferen 20/02/2014
        13 syschecks  20/02/2014
        14 sysdefault 20/02/2014
        15 syscoldepe 20/02/2014
        16 sysprocedu 20/02/2014
        17 sysprocbod 20/02/2014
        18 sysprocpla 20/02/2014
        19 sysprocaut 20/02/2014

10 row(s) retrieved.


select  * from table(dummy(10));

unnamed_col_1 unnamed_col_2 unnamed_col_3

          10 syssyntabl    20/02/2014
          11 sysconstra    20/02/2014
          12 sysreferen    20/02/2014
          13 syschecks     20/02/2014
          14 sysdefault    20/02/2014
          15 syscoldepe    20/02/2014
          16 sysprocedu    20/02/2014
          17 sysprocbod    20/02/2014
          18 sysprocpla    20/02/2014
          19 sysprocaut    20/02/2014

10 row(s) retrieved.


select  * from table(dummy(10)) as x(id,name,created);

        id name       created

        10 syssyntabl 20/02/2014
        11 sysconstra 20/02/2014
        12 sysreferen 20/02/2014
        13 syschecks  20/02/2014
        14 sysdefault 20/02/2014
        15 syscoldepe 20/02/2014
        16 sysprocedu 20/02/2014
        17 sysprocbod 20/02/2014
        18 sysprocpla 20/02/2014
        19 sysprocaut 20/02/2014

10 row(s) retrieved.


select  x.id,name from table(dummy(10)) x(id,name,created);

        id name

        10 syssyntabl
        11 sysconstra
        12 sysreferen
        13 syschecks
        14 sysdefault
        15 syscoldepe
        16 sysprocedu
        17 sysprocbod
        18 sysprocpla
        19 sysprocaut

10 row(s) retrieved.



Database closed.

引用自：https://dba.stackexchange.com/questions/60471