iT邦幫忙

0

Tiptop自製功能報錯 SQLCA.sqlcode = -400

TIM 2024-05-13 17:37:22708 瀏覽
  • 分享至 

  • xImage

在Tiptop中的新增一action,只要點擊過後,就無法再執行其他任何按鍵,請問該如何修改,謝謝各位大神
程式如下:

FUNCTION t360_out_history_price()
  DEFINE l_sql    STRING,
         l_ac     INTEGER,
         l_ac_2   INTEGER,
         l_flag   INTEGER,
         l_table  STRING
        ,l_sql_t  STRING   

  DEFINE sr RECORD
           oqu03 LIKE oqu_file.oqu03,
           oqu04 LIKE oqu_file.oqu04,
           oqt01 LIKE oqt_file.oqt01,
           oqt02 LIKE oqt_file.oqt02,
           oqu02 LIKE oqu_file.oqu02,
           oqt09 LIKE oqt_file.oqt09,
           oqu07 LIKE oqu_file.oqu07
         END RECORD

  LET l_sql_t = "oqu03.oqu_file.oqu03,",
                "oqu04.oqu_file.oqu04,",
                "oqt01.oqt_file.oqt01,",
                "oqt02.oqt_file.oqt02,",
                "oqu02.oqu_file.oqu02,",
                "oqt09.oqt_file.oqt09,",
                "oqu07.oqu_file.oqu07"

  LET l_table = cl_prt_temptable('cxmt360',l_sql_t) CLIPPED
  IF l_table = -1 THEN RETURN END IF
  LET l_sql_t = "INSERT INTO ",g_cr_db_str CLIPPED,l_table CLIPPED,
                " VALUES(?,?,?,?,? ,?,?)"

  PREPARE insert_prep_out_3 FROM l_sql_t
  IF SQLCA.SQLCODE OR STATUS THEN
    CALL cl_err('inser_prep_out_3:',status,1)
    RETURN
  END IF

  FOR l_ac = 1 TO g_oqu.getLength()
    LET l_flag = 0
    FOR l_ac_2 = 1 TO l_ac-1
      IF g_oqu[l_ac_2].oqu03 = g_oqu[l_ac].oqu03 AND g_oqu[l_ac_2].oqu04 = g_oqu[l_ac].oqu04 THEN
        LET l_flag = 1
        EXIT FOR
      END IF
    END FOR
    IF l_flag = 1 THEN
      CONTINUE FOR
    END IF

    LET l_sql = " SELECT oqu03,oqu04,oqt01,oqt02,oqu02,oqt09,NVL(oqu07,oqv05) oqu07",
                "   FROM oqt_file LEFT JOIN oqu_file ON oqt01 = oqu01 ",
                "                 LEFT JOIN (SELECT oqv01,oqv02,MIN(oqv05) oqv05 FROM oqv_file GROUP BY oqv01,oqv02) ON oqu01 = oqv01 AND oqu02 = oqv02",
                "  WHERE oqt04 = '",g_oqt.oqt04,"' AND oqu03 = '",g_oqu[l_ac].oqu03,"' ",
                "    AND oqu04 = '",g_oqu[l_ac].oqu04,"'",                   
                "    AND NOT oqt01 = '",g_oqt.oqt01,"'  AND NOT oqtconf = 'X'",
                " ORDER BY oqt02 DESC,oqt01, oqu02 " 

    PREPARE cxmt360_prepare_out_3 FROM l_sql
    DECLARE cxmt360_curs_out_3 CURSOR FOR cxmt360_prepare_out_3
    IF SQLCA.sqlcode OR STATUS THEN
      CALL cl_err('prepare:',SQLCA.sqlcode,1)
      CALL cl_used(g_prog,g_time,2) RETURNING g_time
      RETURN
    END IF

    FOREACH cxmt360_curs_out_3 INTO sr.*
      EXECUTE insert_prep_out_3 USING sr.*
    END FOREACH

    CLOSE cxmt360_curs_out_3
    FREE cxmt360_prepare_out_3

  END FOR

  LET l_sql = "SELECT * FROM ",g_cr_db_str CLIPPED,l_table CLIPPED

  CALL cl_prt_cs3('cxmt360','cxmt360_history_price',l_sql,"")
END FUNCTION
看更多先前的討論...收起先前的討論...
by2048 iT邦高手 1 級 ‧ 2024-05-13 21:23:35 檢舉
sql code < 0 建議先檢查sql 語法是否正確
無法按任何鍵,程式是否還在迴圈中執行尚未結束
min_di iT邦新手 3 級 ‧ 2024-05-14 08:28:55 檢舉
指令 : finderr -400 0
-400 嘗試在未開啟的游標上取得.
FETCH 敘述指名從未開啟或是已經關閉的游標. 請檢查程式邏輯並確定它
會在此之前開啟游標, 而不會意外地將它關閉. 要注意的是除非游標宣告
WITH HOLD, 否則 COMMIT WORK 或 ROLLBACK WORK 會自動地將
它加以關閉.

有CLOSE 但沒有OPEN嗎?
TIM iT邦新手 5 級 ‧ 2024-05-14 09:08:57 檢舉
請問如果要加OPEN需要加在哪裡呢,謝謝
min_di iT邦新手 3 級 ‧ 2024-05-14 09:18:19 檢舉
FETCH指令之前必須先執行OPEN CURSOR的指令,結束時必須由CLOSE CURSOR 來釋放CURSOR,但FOREACH 指令有自動開啟與關閉CURSOR的功能。

我不知道你是否有debug看哪個段落出現-400
但我是認為不用CLOSE?
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

0
做工仔人!
iT邦大師 1 級 ‧ 2024-05-14 08:51:34

應該是變數問題。
在FUNCTION 中不要用 l_ac 及 l_table
因為l_ac 是tiptop單身控制游標的變數。
建議:
1.l_ac改為 l_ac_1 (因為另一個變數為 l_ac_2)
2.這支是報價單的列印程式: 可以考慮新增一支r的程式,在報價單中傳值呼叫即可。TIPTOP系統中這種寫法很多:訂單/出通單/出貨單/採購單/請購單/傳票...可供參考。

看更多先前的回應...收起先前的回應...
TIM iT邦新手 5 級 ‧ 2024-05-14 09:11:15 檢舉

我將 l_ac 及 l_table變數修改成其他名稱,一樣會發生問題,請問我是哪裡做錯了,謝謝

FUNCTION t360_out_history_price()
  DEFINE l_sql    STRING,
         l_a     INTEGER,
         l_a_2   INTEGER,
         l_flag   INTEGER,
         l_table2  STRING
        ,l_sql_t  STRING   

  DEFINE sr RECORD
           oqu03 LIKE oqu_file.oqu03,
           oqu04 LIKE oqu_file.oqu04,
           oqt01 LIKE oqt_file.oqt01,
           oqt02 LIKE oqt_file.oqt02,
           oqu02 LIKE oqu_file.oqu02,
           oqt09 LIKE oqt_file.oqt09,
           oqu07 LIKE oqu_file.oqu07
         END RECORD

  LET l_sql_t = "oqu03.oqu_file.oqu03,",
                "oqu04.oqu_file.oqu04,",
                "oqt01.oqt_file.oqt01,",
                "oqt02.oqt_file.oqt02,",
                "oqu02.oqu_file.oqu02,",
                "oqt09.oqt_file.oqt09,",
                "oqu07.oqu_file.oqu07"

  LET l_table2 = cl_prt_temptable('cxmt360',l_sql_t) CLIPPED
  IF l_table2 = -1 THEN RETURN END IF
  LET l_sql_t = "INSERT INTO ",g_cr_db_str CLIPPED,l_table2 CLIPPED,
                " VALUES(?,?,?,?,? ,?,?)"

  PREPARE insert_prep_out_3 FROM l_sql_t
  IF SQLCA.SQLCODE OR STATUS THEN
    CALL cl_err('inser_prep_out_3:',status,1)
    RETURN
  END IF

  FOR l_a = 1 TO g_oqu.getLength()
    LET l_flag = 0
    FOR l_a_2 = 1 TO l_a-1
      IF g_oqu[l_a_2].oqu03 = g_oqu[l_a].oqu03 AND g_oqu[l_a_2].oqu04 = g_oqu[l_a].oqu04 THEN
        LET l_flag = 1
        EXIT FOR
      END IF
    END FOR
    IF l_flag = 1 THEN
      CONTINUE FOR
    END IF

    LET l_sql = " SELECT oqu03,oqu04,oqt01,oqt02,oqu02,oqt09,NVL(oqu07,oqv05) oqu07",
                "   FROM oqt_file LEFT JOIN oqu_file ON oqt01 = oqu01 ",
                "                 LEFT JOIN (SELECT oqv01,oqv02,MIN(oqv05) oqv05 FROM oqv_file GROUP BY oqv01,oqv02) ON oqu01 = oqv01 AND oqu02 = oqv02",
                "  WHERE oqt04 = '",g_oqt.oqt04,"' AND oqu03 = '",g_oqu[l_a].oqu03,"' ",
                "    AND oqu04 = '",g_oqu[l_a].oqu04,"'", 
                "    AND NOT oqt01 = '",g_oqt.oqt01,"'  AND NOT oqtconf = 'X'",
                " ORDER BY oqt02 DESC,oqt01, oqu02 " 

    PREPARE cxmt360_prepare_out_3 FROM l_sql
    DECLARE cxmt360_curs_out_3 CURSOR FOR cxmt360_prepare_out_3
    IF SQLCA.sqlcode OR STATUS THEN
      CALL cl_err('prepare:',SQLCA.sqlcode,1)
      CALL cl_used(g_prog,g_time,2) RETURNING g_time
      RETURN
    END IF

    FOREACH cxmt360_curs_out_3 INTO sr.*
      EXECUTE insert_prep_out_3 USING sr.*
    END FOREACH

    CLOSE cxmt360_curs_out_3
    FREE cxmt360_prepare_out_3

  END FOR

  LET l_sql = "SELECT * FROM ",g_cr_db_str CLIPPED,l_table2 CLIPPED

  CALL cl_prt_cs3('cxmt360','cxmt360_history_price',l_sql,"")
END FUNCTION
TIM iT邦新手 5 級 ‧ 2024-05-14 09:44:02 檢舉

您好想再問一下
建議1:我已經有修改過但還是不行
建議2:我不太懂新增一隻r的程式是指甚麼,剛剛去翻了一下還是不明白,所以想再請教一下,謝謝

1.r的程式=>指的是獨立的報表程式.(TIPTOP程式編號第四碼='r':代表為報表程式.=>請參考TIPTOP的技轉手冊前幾頁就有說明)
2.l_ac :沒改到,只改到l_table , 如果l_ac 改為l_ac_1 也無效, 就要用debug mode 檢查

debug mode操作:問一下鼎新客服或資深同事。(因為操作比較複雜)
技轉手冊是在"附錄"中,要有"慧根"才看的懂。

尼克 iT邦大師 1 級 ‧ 2024-05-14 15:06:58 檢舉

debug mode 要有"慧根"。/images/emoticon/emoticon20.gif

尼克指的是:在沒人點撥的情況下,單"看技轉手冊"看到懂 debug mode 的使用:需要"慧根"(我的手冊是 Version 2.20)

我要發表回答

立即登入回答