I have a sql table with 5 fileds:

EmpId nvarchar50, null
Dept int,null
Act float,null
Budg float,null
class navarchar20,null
Period int,null

The table has been around for quite sometime. A stored proc performs a truncate and new data aappended each month.

I can access the table via sql server or Msaccess without issue.

Webforcus (v8008 - Dev Studio) is causing problems. It was working fine until this month. It now errors out. I reloaded last months data...works fine. I figure there is some sort of garbage in the data but I'll be damned if I can find it.

The code does not run at all, it just yields this error:



Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.


--------------------------------------------------------------------------------


(FOC1400) SQLCODE IS 8115 (HEX: 00001FB3) XOPEN: 22003
: Microsoft OLE DB Provider for SQL Server: [22003] Arithmetic overflow er
: ror converting expression to data type int.
L (FOC1406) SQL OPEN CURSOR ERROR. : TBLMSTR_YTDEMPLOYEEFTES


I tried creating the simplest fex against the table and it yields the same error.


SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON=MNTSTMT

TABLE FILE TBLMSTR_YTDEMPLOYEEFTES
BY LOWEST TBLMSTR_YTDEMPLOYEEFTES.TBLMSTR_YTDEMPLOYEEFTES.PERIOD AS 'PD'
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLDFTEPD
END

Any ideas? As I said..MAsAccess and SQl server has no issues with the table.

This message has been edited. Last edited by: FP Mod Chuck,

btw....was tinkering with the data in the two integer fileds...pretty much to no avail however, if I manually update the value of 'period' on my sql server from: 201905 to 2019...suddenly all is forgiven and everything works. If I change it back...blows up. If I change it to 201903 or 201904 (values from pervious months...blows up...2019 alone works. This is nuts! This was never an issue before.



The table is small...less than 11K rows. There are no server space issues.

1. Can you share the SQL Trace from this request?
2. Can you run with WHERE READLIMIT EQ 10?
3. Does the request work without ON TABLE HOLD?
4. Can you create a test master file for the same table using the MS SQL Server ODBC instead of OLE DB and test the same TABLE request using this master?
5. Do you change any of the formats (especially a P20.0) in the master file manually?
6. Are all these real columns or is one of them a DEFINE?
7. Could you show us the definition of the fields you use in the request as they appear in the master file? Also if you're editing the format of these fields, show us what you change them to.
8. Has there been an upgrade or update of the SQL Server since last time this worked? Something has to have change to cause this.

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

I am back.

1) The WHERE READLIMIT EQ 10 still blew up
2) Adding this: ON TABLE HOLD AS HOLDTEMP FORMAT ALPHA still yields the same error.
3) So far as I know nothing was changed on the sql server.
4) All are real columns
5) No Master file changes that I am aware of
6) No edits on them either.
7) Not sure how to do your #4. I only know I create a new synonym, click my sql adapter and db where the table lives and select the table.

I did make a new masterfle for testing. It appears to have the same properties and my other one. It blows up in the same manner. However, there is a new development..I notice the error goes away if I change the BY LOWEST to a PRINT.


SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON=MNTSTMT
TABLE FILE TBLTEMP_YTDFTETEST
BY LOWEST TBLTEMP_YTDFTETEST.TBLTEMP_YTDFTETEST.ASOFPERIOD
ON TABLE HOLD AS HOLDTEMP FORMAT ALPHA


(FOC1400) SQLCODE IS 8115 (HEX: 00001FB3) XOPEN: 22003
: Microsoft OLE DB Provider for SQL Server: [22003] Arithmetic overflow er
: ror converting expression to data type int.
L (FOC1406) SQL OPEN CURSOR ERROR. : TBLTEMP_YTDFTETEST



SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON=MNTSTMT
TABLE FILE TBLTEMP_YTDFTETEST
PRINT TBLTEMP_YTDFTETEST.TBLTEMP_YTDFTETEST.ASOFPERIOD
ON TABLE HOLD AS HOLDTEMP FORMAT ALPHA


1
0 NUMBER OF RECORDS IN TABLE= 10928 LINES= 10928



If you show me how properly turn trace on, I will.

Add this to the beginning of your request.

FYI, SET XRETRIEVAL=OFF will stop WebFOCUS from running the request. So, all you'll see is the SQL Trace. For proper testing, you should change that line to say SET XRETRIEVAL=ON after you get the trace.

  
-SET &ECHO=ALL;
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF

-*** Show SQL statements
SET TRACEON    = STMTRACE//CLIENT

-*** Show SQL generated sub-statement trace
SET TRACEON    = STMTRACE/2/CLIENT

-*** Disable the trace stamp (Date/Time etc)
SET TRACESTAMP = OFF

-*** Set trace line wrapping - # of characters
SET TRACEWRAP  = 132

-*** Activate SQL tracing
SET TRACEUSER  = ON

Also, try changing the request that causes the error, to say ON TABLE PCHOLD FORMAT HTML so you can test the request without an attempt to create a hold.

Please show us the part of the master file that shows PERIOD. It should look something like this: FIELDNAME=PERIOD, USAGE=blah blah, ACTUAL=blah blah,$

Could you also test BY PERIOD instead of BY LOWEST PERIOD?

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

quote:

-SET &ECHO=ALL;
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF

Hold file or not makes no difference.

-SET &ECHO=ALL;
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF
TABLE FILE TBLTEMP_YTDFTETEST
BY LOWEST TBLTEMP_YTDFTETEST.TBLTEMP_YTDFTETEST.ASOFPERIOD
ON TABLE PCHOLD FORMAT HTML
END

Yields:

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.


--------------------------------------------------------------------------------


SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF
TABLE FILE TBLTEMP_YTDFTETEST
BY LOWEST TBLTEMP_YTDFTETEST.TBLTEMP_YTDFTETEST.ASOFPERIOD
ON TABLE PCHOLD FORMAT HTML
END
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0



I am not sure how to get a dump of the mastefile, when I double Click it and look at properties I see for AsOfPeriod:

ACTUAL I4
Length 4
USUAGE I11
Length 11

You need to add all the lines in the trace code I shared to see the SQL trace.

Duh!

-SET &ECHO=ALL;
SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF

-*** Show SQL statements
SET TRACEON = STMTRACE//CLIENT

-*** Show SQL generated sub-statement trace
SET TRACEON = STMTRACE/2/CLIENT

-*** Disable the trace stamp (Date/Time etc)
SET TRACESTAMP = OFF

-*** Set trace line wrapping - # of characters
SET TRACEWRAP = 132

-*** Activate SQL tracing

SET TRACEUSER = ON
TABLE FILE TBLTEMP_YTDFTETEST
BY LOWEST TBLTEMP_YTDFTETEST.TBLTEMP_YTDFTETEST.ASOFPERIOD
ON TABLE PCHOLD FORMAT HTML
END


SET XRETRIEVAL=OFF
SET EMPTYREPORT=OFF
-*** Show SQL statements
SET TRACEON = STMTRACE//CLIENT
-*** Show SQL generated sub-statement trace
SET TRACEON = STMTRACE/2/CLIENT
-*** Disable the trace stamp (Date/Time etc)
SET TRACESTAMP = OFF
-*** Set trace line wrapping - # of characters
SET TRACEWRAP = 132
-*** Activate SQL tracing
SET TRACEUSER = ON
TABLE FILE TBLTEMP_YTDFTETEST
BY LOWEST TBLTEMP_YTDFTETEST.TBLTEMP_YTDFTETEST.ASOFPERIOD
ON TABLE PCHOLD FORMAT HTML
END
AGGREGATION DONE ...
SELECT
T1."AsOfPeriod",
SUM(T1."AsOfPeriod")
FROM
LVPG_FinancialPlanning.dbo.tblTemp_YtdFteTest T1
GROUP BY
T1."AsOfPeriod"
ORDER BY
T1."AsOfPeriod";
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0


...and the Select it puts together fails on my sql server..I guess the question is...why / where did it get the SUM from?

SUM(T1."AsOfPeriod")

Could you test:

quote:

TABLE FILE TBLTEMP_YTDFTETEST
BY TBLTEMP_YTDFTETEST.TBLTEMP_YTDFTETEST.ASOFPERIOD
ON TABLE PCHOLD FORMAT HTML
END

THAT WORKS!

But the code was never like that and worked until this month. Last month the value 201904 loaded fine. In fact if I reload last months data it is fine.

This month's data has a few hundred more rows..and the value: 201905. The process Truncates the table and appends the new month.

What are your thoughts as to what is happening?

...in fact there are several fexes that grab the data in this manner, all with BY LOWEST...

I am guessing I am hitting some sort of sql threshold...but I do not understand why webfocus thru the SUM in to begin with?

What version of WebFOCUS are you running? In my case BY LOWEST integer field does not generate a SUM integer field in 8204. I have a feeling you have a version that used to generate the SUM in the SQL and that was fine until the value in the field caused problems. This is a total SWAG on my part based on the SQL Trace you showed. The best way to address this is to open a case with IB tech support to see what they say. Now that you have all the details, it should be easy for them to tell you what to do next.

we are on lowly 8008...hoping to upgrade!

Well, if you open a case with IB and tell them you're having this issue with 4 year old software, their first suggestion will be to test this in the current version. Word around the campfire is 8.206 is on it's way out the door. You might be able to justify the upgrade even sooner, if you download the latest on a test machine and show it's working properly there.

oh we understand...it's political...we are hoping to get to the latest version soon.

Your TABLE request does not contain a VERB,
so WebFOCUS adds one for you i.e. a SUM
So you need to add a VERB to your TABLE request to control what it is doing.

e.g. try adding:
SUM MAX.TBLTEMP_YTDFTETEST.TBLTEMP_YTDFTETEST.ASOFPERIOD NOPRINT

i.e.

TABLE FILE TBLTEMP_YTDFTETEST
SUM MAX.TBLTEMP_YTDFTETEST.TBLTEMP_YTDFTETEST.ASOFPERIOD NOPRINT
BY LOWEST TBLTEMP_YTDFTETEST.TBLTEMP_YTDFTETEST.ASOFPERIOD
ON TABLE HOLD AS HOLDTEMP FORMAT ALPHA
END

Sorry Martin, but the verbs in WebFOCUS are SUM, PRINT, LIST, WRITE, ADD, COUNT etc.

They do not include the sorts BY or ACROSS.

So Twanette's statement about it not containing a verb is correct. Presenting Data Values: Verbs


T