Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] MUST OPEN A CASE...completely befuddled
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] MUST OPEN A CASE...completely befuddled
 Login/Join
 
Master
posted
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,


WebFOCUS 8.
Windows, All Outputs
 
Posts: 539 | Registered: June 28, 2013Reply With QuoteReport This Post
Master
posted Hide Post
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.


WebFOCUS 8.
Windows, All Outputs
 
Posts: 539 | Registered: June 28, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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,


WebFOCUS 8206, Unix, Windows
 
Posts: 1673 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Master
posted Hide Post
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.


WebFOCUS 8.
Windows, All Outputs
 
Posts: 539 | Registered: June 28, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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,


WebFOCUS 8206, Unix, Windows
 
Posts: 1673 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Master
posted Hide Post
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


WebFOCUS 8.
Windows, All Outputs
 
Posts: 539 | Registered: June 28, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
You need to add all the lines in the trace code I shared to see the SQL trace.


WebFOCUS 8206, Unix, Windows
 
Posts: 1673 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Master
posted Hide Post
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")


WebFOCUS 8.
Windows, All Outputs
 
Posts: 539 | Registered: June 28, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Could you test:
quote:
TABLE FILE TBLTEMP_YTDFTETEST
BY TBLTEMP_YTDFTETEST.TBLTEMP_YTDFTETEST.ASOFPERIOD
ON TABLE PCHOLD FORMAT HTML
END


WebFOCUS 8206, Unix, Windows
 
Posts: 1673 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Master
posted Hide Post
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?


WebFOCUS 8.
Windows, All Outputs
 
Posts: 539 | Registered: June 28, 2013Reply With QuoteReport This Post
Master
posted Hide Post
...in fact there are several fexes that grab the data in this manner, all with BY LOWEST...


WebFOCUS 8.
Windows, All Outputs
 
Posts: 539 | Registered: June 28, 2013Reply With QuoteReport This Post
Master
posted Hide Post
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?


WebFOCUS 8.
Windows, All Outputs
 
Posts: 539 | Registered: June 28, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8206, Unix, Windows
 
Posts: 1673 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Master
posted Hide Post
we are on lowly 8008...hoping to upgrade!


WebFOCUS 8.
Windows, All Outputs
 
Posts: 539 | Registered: June 28, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8206, Unix, Windows
 
Posts: 1673 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Master
posted Hide Post
oh we understand...it's political...we are hoping to get to the latest version soon.


WebFOCUS 8.
Windows, All Outputs
 
Posts: 539 | Registered: June 28, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 201 | Location: Johannesburg, South Africa | Registered: September 13, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5604 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] MUST OPEN A CASE...completely befuddled

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.