Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Modify
 Login/Join
 
Platinum Member
posted
I am trying to use Modify to update a sql table by first holding a file and doing the modify. I am getting this error:
 (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 42000
 : Microsoft OLE DB Provider for SQL Server: [42000] Syntax error or access
 :  violation
 (FOC1740) EXECUTE ERROR  : ABS_FORECAST_ADJUSTMENT
 0 TRANSACTIONS:         TOTAL =     1  ACCEPTED=     1  REJECTED=     0
 SEGMENTS:             INPUT =     0  UPDATED =     0  DELETED =     0

 


 ON TABLE HOLD AS HFRCST FORMAT ALPHA
END
-RUN

MODIFY FILE ABS-FORECAST_ADJUSTMENT
FIXFORM FROM HFRCST
MATCH COMPANY CUSTOMER
ON MATCH UPDATE SALES_FORECAST_MO1 SALES_FORECAST_MO2 SALES_FORECAST_MO3 SALES_FORECAST_MO4 SALES_FORECAST_MO5 SALES_FORECAST_MO6 SALES_FORECAST_MO7 SALES_FORECAST_MO8 SALES_FORECAST_MO9 SALES_FORECAST_MO10 SALES_FORECAST_MO11 SALES_FORECAST_MO12 LAST_CHANGE_DATE LAST_CHANGE_USER
DATA ON HFRCST
END
-RUN 

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


Prod: WebFocus 7.7.3 Win 2003
Dev: WebFocus 7.7.3 Win 2003
 
Posts: 116 | Registered: April 23, 2007Report This Post
Platinum Member
posted Hide Post
Don,

What is the correct file name ?

ABS_FORECAST_ADJUSTMENT, or

ABS-FORECAST_ADJUSTMENT


Regards,
Dave

http://www.daveayers.com

WebFocus/Maintain 7.6.4-8
on Win2000 and 2003 Server
 
Posts: 165 | Location: Detroit Metro | Registered: September 17, 2003Report This Post
Expert
posted Hide Post
quote:
Syntax error or access violation

Do you have update access to the table?


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
according to the dba I do have update ability, but I will verify that again with him.


Prod: WebFocus 7.7.3 Win 2003
Dev: WebFocus 7.7.3 Win 2003
 
Posts: 116 | Registered: April 23, 2007Report This Post
<JG>
posted
Donald, a possible cause is that the update is on a single line,
I do not know if Modify supports long lines as well as WebFOCUS now does.

Try recoding it to go over multiple lines which is what we did in the old days.

ON MATCH UPDATE SALES_FORECAST_MO1 SALES_FORECAST_MO2 SALES_FORECAST_MO3
ON MATCH UPDATE SALES_FORECAST_MO4 SALES_FORECAST_MO5 SALES_FORECAST_MO6
ON MATCH UPDATE SALES_FORECAST_MO10 SALES_FORECAST_MO11 SALES_FORECAST_MO12
ON MATCH UPDATE LAST_CHANGE_DATE LAST_CHANGE_USER
 
Report This Post
Virtuoso
posted Hide Post
Donald

You should not ask the web master if YOU have access to the database but if the application (IBI) has access.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
Am I missing the point, but don't you have to add WRITE=YES to the access file to allow update to the DB.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
I tried updating just three fields to prove that its not a length issue.

 SET TRACEOFF = ALL
 SET TRACEON=STMTRACE
 SET TRACEON = SQLAGGR//CLIENT
 SET TRACEON = STMTRACE//CLIENT
 SET TRACEON = STMTRACE/2/CLIENT
 SET TRACEUSER = ON
 -RUN
 DEFINE FILE MISTRAL-FS03_V01
 MONTH1/A20 = '1';
 MONTH2/A20 = ' ';
 MONTH3/A20 = ' ';
 MONTH4/A20 = ' ';
 MONTH5/A20 = ' ';
 MONTH6/A20 = ' ';
 MONTH7/A20 = ' ';
 MONTH8/A20 = ' ';
 MONTH9/A20 = ' ';
 MONTH10/A20 = ' ';
 MONTH11/A20 = ' ';
 MONTH12/A20 = ' ';
 COMPANY/A2 = 'H' || 'H';
 CUSTOMER/A6 = 'BA06';
 END
 -RUN
 TABLE FILE MISTRAL-FS03_V01
 PRINT DST.MONTH1 AS SALES_FORECAST_MO1
 DST.MONTH2 AS SALES_FORECAST_MO2
 DST.MONTH3 AS SALES_FORECAST_MO3
 DST.MONTH4 AS SALES_FORECAST_MO4
 DST.MONTH5 AS SALES_FORECAST_MO5
 DST.MONTH6 AS SALES_FORECAST_MO6
 DST.MONTH7 AS SALES_FORECAST_MO7
 DST.MONTH8 AS SALES_FORECAST_MO8
 DST.MONTH9 AS SALES_FORECAST_MO9
 DST.MONTH10 AS SALES_FORECAST_MO10
 DST.MONTH11 AS SALES_FORECAST_MO11
 DST.MONTH12 AS SALES_FORECAST_MO12
 DST.COMPANY AS COMPANY
 DST.CUSTOMER AS CUSTOMER
 COMPUTE LAST_CHANGE_DATE/HYYMDs = HGETC(10, 'HYYMDm');
 COMPUTE LAST_CHANGE_USER/A10 = 'N642553';
 WHERE ( CO EQ 'H' )AND ( DIV1NM EQ 'IBU' ) AND (CUST =  'BA06')  ;
 ON TABLE HOLD AS HFRCST FORMAT ALPHA
 END
 -RUN
 14.28.22 BR  (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
 14.28.22 BR  (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
 14.28.22 AE    SELECT T1."CO",T1."DIV1NM",T1."CUST" FROM
 14.28.22 AE   AMISTRAL_PROD.FS03_V01 T1 WHERE (T1."CUST" = 'BA06') AND
 14.28.22 AE   (T1."DIV1NM" = 'IBU') AND (T1."CO" = 'H');
 0 NUMBER OF RECORDS IN TABLE=       61  LINES=     61
 MODIFY FILE ABS-FORECAST_ADJUSTMENT
 FIXFORM FROM HFRCST
 MATCH COMPANY CUSTOMER
 ON MATCH UPDATE SALES_FORECAST_MO1 LAST_CHANGE_DATE LAST_CHANGE_USER
 DATA ON HFRCST
 END
 -RUN
 14.28.22 AE   SELECT T1."Company",T1."Customer",T1."YearMonth",
 14.28.22 AE   T1."PartNumber",T1."Orig_Forecast_M1",T1."Orig_Forecast_M2",
 14.28.22 AE   T1."Orig_Forecast_M3",T1."Orig_Forecast_M4",
 14.28.22 AE   T1."Orig_Forecast_M5",T1."Orig_Forecast_M6",
 14.28.22 AE   T1."Orig_Forecast_M7",T1."Orig_Forecast_M8",
 14.28.22 AE   T1."Orig_Forecast_M9",T1."Orig_Forecast_M10",
 14.28.22 AE   T1."Orig_Forecast_M11",T1."Orig_Forecast_M12",
 14.28.22 AE   T1."Sales_Forecast_Mo1",T1."Sales_Forecast_Mo2",
 14.28.22 AE   T1."Sales_Forecast_Mo3",T1."Sales_Forecast_Mo4",
 14.28.22 AE   T1."Sales_Forecast_Mo5",T1."Sales_Forecast_Mo6",
 14.28.22 AE   T1."Sales_Forecast_Mo7",T1."Sales_Forecast_Mo8",
 14.28.22 AE   T1."Sales_Forecast_Mo9",T1."Sales_Forecast_Mo10",
 14.28.22 AE   T1."Sales_Forecast_Mo11",T1."Sales_Forecast_Mo12",
 14.28.22 AE   T1."CSR_Forecast_Mo1",T1."CSR_Forecast_Mo2",
 14.28.22 AE   T1."CSR_Forecast_Mo3",T1."CSR_Forecast_Mo4",
 14.28.22 AE   T1."CSR_Forecast_Mo5",T1."CSR_Forecast_Mo6",
 14.28.22 AE   T1."CSR_Forecast_Mo7",T1."CSR_Forecast_Mo8",
 14.28.22 AE   T1."CSR_Forecast_Mo9",T1."CSR_Forecast_Mo10",
 14.28.22 AE   T1."CSR_Forecast_Mo11",T1."CSR_Forecast_Mo12",T1."CSR_Comments",
 14.28.22 AE   T1."Sales_Comments",T1."Status",T1."Status_Error_Message",
 14.28.22 AE   T1."Registration_date",T1."Registration_user",
 14.28.22 AE   T1."Last_Change_Date",T1."Last_Change_User",T1."Submit_Date",
 14.28.22 AE   T1."Submit_User" FROM absdb_prod.dbo.Forecast_Adjustment T1
 14.28.22 AE   WHERE (T1."Company" = ?) AND (T1."Customer" = ?) ORDER BY
 14.28.22 AE   T1."Company"  ASC,T1."Customer"  ASC,T1."YearMonth"  ASC,
 14.28.22 AE   T1."PartNumber"  ASC;
 14.28.22 AE   UPDATE absdb_prod.dbo.Forecast_Adjustment SET  WHERE ("Company"
 14.28.22 AE   = ?) AND ("Customer" = ?) AND ("YearMonth" = ?) AND
 14.28.22 AE   ("PartNumber" = ?);
 (FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 42000
 : Microsoft OLE DB Provider for SQL Server: [42000] Syntax error or access
 :  violation
 (FOC1740) EXECUTE ERROR  : ABS_FORECAST_ADJUSTMENT
 0 TRANSACTIONS:         TOTAL =     1  ACCEPTED=     1  REJECTED=     0
 SEGMENTS:             INPUT =     0  UPDATED =     0  DELETED =     0



 


Prod: WebFocus 7.7.3 Win 2003
Dev: WebFocus 7.7.3 Win 2003
 
Posts: 116 | Registered: April 23, 2007Report This Post
Expert
posted Hide Post
Donald, two things

1. What User is being used to access SQL Server from WebFOCUS?

2. Are you able to copy the UPDATE SQL, change the ? to values and run it against SQL Server with the User above?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders