I tried to use SQL UPDATE but kepp getting error message as below:

1
0 NUMBER OF RECORDS IN TABLE= 201 LINES= 4
(FOC14069) SQL SYNTAX ERROR ON LINE 99 AT 'K'



I created a hold file as 2012CK then created another small (4 records) hold file 2012FIX.

Then trid to use UPDATE to update 2012CK.

-***********************************************

TABLE FILE 2012CK
SUM
COMPUTE AMT/D15.2CBM = AMOUNT;AS 'AMT'
BUSINESS_UNIT
JOURNAL_DATE
MONEY_AMOUNT
AMOUNT
VOUCHER_ID
VOUCHER_LINE_NUM
DISTRIB_LINE_NUM
SRC_SYS_ID
POSTING_DATE
INVOICE_DT
INVOICE_ID
VENDOR_ID
NAME1
DESCR
VNDR_LOC
BUSINESS_UNIT_PO
PO_ID
ACCOUNTING_DT
DUE_DT
ENTERED_DT
BY FISCAL_YEAR
BY MON_PERIOD
BY DEPTID
BY ACCOUNT
BY PROGRAM_CODE
BY JOURNAL_DATE
BY JOURNAL_ID
BY JOURNAL_LINE
BY VENDOR_ID
BY NAME1
WHERE RANK EQ 88888
WHERE TOTAL AMT NE 0
ON TABLE HOLD AS 2012FIX
END
-RUN
-RUN


SQL
UPDATE 2012CK K INNER JOIN 2012FIX F ON
(K.FISCAL_YEAR = F.FISCAL_YEAR)
AND (K.MON_PERIOD = F.MON_PERIOD)
AND (K.DEPTID = F.DEPTID)
AND (K.ACCOUNT = F.ACCOUNT)
AND (K.PROGRAM_CODE = F.PROGRAM_CODE)
AND (K.JOURNAL_DATE = F.JOURNAL_DATE)
AND (K.JOURNAL_ID = F.JOURNAL_ID)
AND (K.JOURNAL_LINE = F.JOURNAL_LINE)
AND (K.FUND_CODE = "8080")
AND (K.RNAK = 0)
AND (K.JOURNAL_ID LIKE "AP*")

SET K.VOUCHER_ID = F.VOUCHER_ID
, K.VOUCHER_LINE_NUM = F.VOUCHER_LINE_NUM
, K.DISTRIB_LINE_NUM = F.DISTRIB_LINE_NUM
, K.SRC_SYS_ID = F.SRC_SYS_ID
, K.POSTING_DATE = F.POSTING_DATE
, K.INVOICE_DT = F.INVOICE_DT
, K.INVOICE_ID = F.INVOICE_ID
, K.NAME1 = F.NAME1
, K.DESCR = F.DESCR
, K.VNDR_LOC = F.VNDR_LOC
, K.BUSINESS_UNIT_GL = F.BUSINESS_UNIT_GL
, K.BUSINESS_UNIT_PO = F.BUSINESS_UNIT_PO
, K.PO_ID = F.PO_ID
, K.ACCOUNTING_DT = F.ACCOUNTING_DT
, K.DUE_DT = F.DUE_DT
, K.ENTERED_DT = F.ENTERED_DT
;
END
-***********************************************
We are using WebFUCUS7.7.3

Please help.

This message has been edited. Last edited by: Kerry,

Don't know if this is the problem, but RANK is mis-spelled in your SQL UPDATE statement:

AND (K.RNAK = 0)

It looks like 2012FIX is a binary WebFOCUS HOLD file, which you cannot use in the SQL UPDATE statement - you can join to a table that is of the same dbms type and the one you're updating by the UPDATE statement. Also, are you sure of the UPDATE statement using INNER JOIN?

If this is SQL Server, you may be able to create a temporary table and use that in the inner join...

Thank you for pointing out my misspelling.

I corrected it but still got the same error message:

1
0 NUMBER OF RECORDS IN TABLE= 201 LINES= 4
(FOC14069) SQL SYNTAX ERROR ON LINE 99 AT 'K'


Is there any syntax error at the very beginning :

SQL
UPDATE 2012CK K
????????????????????????????????????

As Francis said -- there is a problem with the whole approach.
You said you created two HOLD files (2012CK and 2012FIX) and since you are not HOLDing them as native SQL tables you cannot use the UPDATE syntax on them.
If they are Binary/FOCUS HOLD files you can use MODIFY or best still apply your manipulations to 2012CK in DEFINE's and HOLD it as a new file.
If you want to UPDATE the source SQL tables then you need to create temp SQL table or update using amper variables as values.

thanks
Sashanka

I recreate 2012FIX to become 2012FIX.mas/2012FIX.foc.

Can I use it for UPDATE?

It is still not working.

Jean - Wu, as Sashanka mentioned, you cannot use SQL UPDATE with a FOCUS database. Use MODIFY FILE instead. There are many examples of this in the forum.

Ok, I will give it a try using MODIFY. Thanks.