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*")
Please help.This message has been edited. Last edited by: Kerry,
WebFOCUS 7.6.4 Windows XP Excel, PDF, HTML, txt,,,,,etc
November 27, 2012, 04:32 PM
Dan Satchell
Don't know if this is the problem, but RANK is mis-spelled in your SQL UPDATE statement:
AND (K.RNAK = 0)
WebFOCUS 7.7.05
November 27, 2012, 04:33 PM
Francis Mariani
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...
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 27, 2012, 04:40 PM
Jean - Wu
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 ????????????????????????????????????
WebFOCUS 7.6.4 Windows XP Excel, PDF, HTML, txt,,,,,etc
November 27, 2012, 04:47 PM
Severus.snape
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
WF 7.7.03/Windows/HTML,PDF,EXL POC/local Dev Studio 7.7.03 & 7.6.11
November 27, 2012, 04:50 PM
Jean - Wu
I recreate 2012FIX to become 2012FIX.mas/2012FIX.foc.
Can I use it for UPDATE?
It is still not working.
WebFOCUS 7.6.4 Windows XP Excel, PDF, HTML, txt,,,,,etc
November 27, 2012, 05:10 PM
Francis Mariani
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.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
November 27, 2012, 05:13 PM
Jean - Wu
Ok, I will give it a try using MODIFY. Thanks.
WebFOCUS 7.6.4 Windows XP Excel, PDF, HTML, txt,,,,,etc