SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token PC_CURSOR was not valid. Valid tokens: GLOBAL. Cause . . . . . : A syntax error was detected at token PC_CURSOR. Token PC_CURSOR is not a valid token. A partial list of valid tokens is GLOBAL. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token PC_CURSOR. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
代码:
CREATE PROCEDURE EODSHADOW.PopulateProdChanges()
LANGUAGE SQL
BEGIN
Declare sqlcode integer default 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_itemnumber CHAR(12) DEFAULT '';
DECLARE v_skucode CHAR(14) DEFAULT '';
DECLARE GLOBAL TEMPORARY TABLE session.tmp_POTable(
Order int ,
EXPECT_ON decimal(7, 0),
QUANTITY decimal(7, 0)
)
NOT LOGGED WITH REPLACE;
DECLARE GLOBAL TEMPORARY TABLE session.tmp_UPCTable(
Order int,
UPC char(14)
)
NOT LOGGED WITH REPLACE;
--1.Change the processingStatus of all records in ProdChangesXref table from ‘ERPPND’ to ‘ERPQUE’.
UPDATE ProdChangesXRef
SET processingStatus = 'ERPQUE',modifyDt=current timestamp
WHERE processingStatus = 'ERPPND';
--insert deleted prod, sku into ProdChanges
INSERT INTO ProdChanges
(ITMNUMBER
,SKUCode
,Deleted
,processingStatus
,createDt
,modifyDt
,operationType)
SELECT
ITMNUMBER,
SKUCODE,
DELETED,
'ERPPND',
CURRENT TIMESTAMP,
CURRENT TIMESTAMP,
'UPDALL'
FROM ProdChangesXRef
Where DELETED = '1';
--delete records that are inserted into ProdChanges
DELETE FROM ProdChangesXRef x
Where exists (select ITMNUMBER,SKUCODE from ProdChanges
where ProdChanges.ITMNUMBER=x.ITMNUMBER AND ProdChanges.SKUCODE=x.SKUCODE