Focal Point
[SOLVED] Modify

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7211006903

December 05, 2008, 03:44 PM
Donald
[SOLVED] Modify
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
December 05, 2008, 05:06 PM
Dave Ayers
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
December 05, 2008, 07:27 PM
Francis Mariani
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
December 06, 2008, 10:44 AM
Donald
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
December 06, 2008, 10:48 AM
<JG>
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
December 06, 2008, 11:19 AM
FrankDutch
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

December 07, 2008, 04:50 PM
Waz
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!

December 08, 2008, 02:29 PM
Donald
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
December 08, 2008, 03:52 PM
Waz
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!