Code:
TABLE FILE TBLMSTR_UNITSWRVUS
BY LOWEST TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
COLUMN=N1,
SQUEEZE=2.763889,
$
ENDSTYLE
END

Yeilds:
(FOC1400) SQLCODE IS 8115 (HEX: 00001FB3) XOPEN: 22003
: Microsoft SQL Server Native Client 10.0: [22003] Arithmetic overflow err
: or converting expression to data type int.
L (FOC1406) SQL OPEN CURSOR ERROR. : TBLMSTR_UNITSWRVUS


The field in the master file is: Real I11. There are 6M+ rows in the table. I see no Null values. I have no problem using MsAcces querying the actual table.

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

I suggest you trace your code and look at the SQL Code generated by your fex and see if you can get your DBA's to figure out why that query errors out.

Here's code you can add to the top of that fex that will display the sql code with that error message (I swiped this long ago from some post here in focal point. I think this was Francis Mariani's code - I swipe a lot of code from him ).

-*-- Set up SQL tracing --------------------------------------------------------

-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL

-*-- Show Commands and data exchange between the -----------
-*-- physical and the logical layers of the data adapter
-*SET TRACEON = SQLCALL

-*-- Enable Trace for the SQL Translator -------------------
SET TRACEON = SQLTRANS

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

-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT

-*-- Show SQL generated statement trace --------------------
-*SET TRACEON = STMTRACE/1/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 = 78

-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON
-RUN

WF 8.1.05 Windows

Anatess beat me to it exactly what I would suggest as a starting point.

From that we should be able to see if you have content in your synonym that is being interpreted by the adapter into the target DBMS SQL - particularly defines.

Can you post your synonym as well? I understand if it is proprietary or something else that prevents you from sharing, but it might help us identify what might be causing your issue.

quote:

I swipe a lot of code from him

If it's shared on the forum then is it really "swiping"?

T

quote:

I swipe a lot of code from him

Anatess is one smart cookie!

Is it possible that the DB integer column has a value that is too large for WebFOCUS metadata integer column? The largest integer column in WF is I11, but perhaps the DB column is larger...

I'll try the code...looking at the actual table on our sql server I think its I4.

@anatess, don't we all
@robertf, as frankie suggests,
out of curiousity
try putting in a RECORDLIMIT
to see if you get an answer back when you're deliberatly not risking overflow.
might be just easier to redefine a field with D20 format.

---

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Hmmmm..this column: RPTBANBR appears in a detail table (the one I am having issues with) as well a 'dictionary' or 'lookup' table...used if you want to get the RPTBANBR's name for example.

If I run the query off the lookup table...(below)...it runs fine.
TABLE FILE TBLMSTR_BANBRBANAMEXREF
BY LOWEST TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.RPTBANBR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
END


However If I pull the field in off the detail table it blows up...original post. Interestingly, if I add a WHERE condition it works until I get to some magic 6 digit number it does not like....have not determine the exact number yet..

Ex: this works:
TABLE FILE TBLMSTR_UNITSWRVUS
BY LOWEST TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR
WHERE TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR LE 100000;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
END

**This does not:
WHERE TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR LE 200000;


The master file properties look the same on the item in each table as do the properties on the sql server.

I am still trying to get the TRACE code to work...

We found a RPTBANBR that does not work:
TABLE FILE TBLMSTR_UNITSWRVUS
BY LOWEST TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR
WHERE TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR EQ 150020;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

...but repalce the BY LOWEST with a PRINT and it does!

TABLE FILE TBLMSTR_UNITSWRVUS
BY LOWEST TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR

From looking at your code, it appears there are no columns specified, which I never do, but I assume that all columns in the table are retrieved. This means RPTBANBR is being summarized as well as being a BY column.

Since you're getting the value from a "detail" table and since it's an integer, each value for the dimension (BY) gets added up.
Normally, the request should look something like this, where measures are the amount columns, attributes are non-amount columns (alpha, date, numeric columns that are not amounts, etc) and dimensions are the SUM BY columns

TABLE FILE table-name
SUM
measure1
measure2
measure3
MAX.attribute1
MAX.attribute2
MAX.attribute3
BY dimension1
BY dimension2
BY dimension3
END

The best thing to do is trace your SQL, copy/paste it here.

I tried copy/pasting the TRACE code at the top of the first post to no avail...What Eaxctly should I put at the top to activate a TRACE?

I admit I am a little confused. I want to see a unique list of only the RPTBANBRs found in the detail table.....when I does this for other fields therein in this manner...it works fine. If I am doing something incorrect though, I want to understand...

You paste the SQL trace code at the top of the fex. Then you run the fex - html format. The view source. You will find the SQL SELECT statement.

Could you please explain why there is no SUM statement in your WebFOCUS TABLE FILE, and why no fields (columns) are mentioned?

This illustrates what happens when summing a numeric dimension or attribute column (one that is not a measure column). Guess which one is correct?

One:

TABLE FILE CAR
SUM 
SEATS
BY SEATS
END

Two:

TABLE FILE CAR
SUM 
MAX.SEATS
BY SEATS
END

quote:

Guess which one is correct?

Depends upon what the specification called for and whether the BA specified it correctly

T

@RobertF

we have this problem too since migration to Teradata. ( I suspect the adapter... )

Just adding a SUM field will do the trick.

Tracing will show the adapter add a dummy SUM field.
Like COUNT * AS 'INTERNAL DUMMY'.

Problem is, you have more rows that what fits in the field.

SUM
COMPUTE DUMMY/I11 = 1;
BY ....

usually fixes the problem.

You could also do something like

TABLE FILE TBLMSTR_UNITSWRVUS
SUM FST.TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR NOPRINT
BY LOWEST TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR
....

G'luck

quote:

Originally posted by Francis Mariani:
Could you please explain why there is no SUM statement in your WebFOCUS TABLE FILE, and why no fields (columns) are mentioned?

We do that all the time. Typical usage is when you need a unique list of values from a single field. Using SUM or PRINT doesn't add anything useful to the results.

A comparable query in SQL would read:

SELECT fieldA FROM table GROUP BY fieldA;
or
SELECT DISTINCT fieldA FROM table;

An example of where this is useful is fetching data for dropdown lists in launch pages (where the option's values are also their descriptions).

Yes, WEP.

So did we...
...until we moved to a different RDMS ( and adapter ).

Then we got this error too...

Wep5652....T H A N K S!
I was spinning my head trying to understand why folks would want the sum or print...you said it best, I want a unique list of RPTBANBRs found in my table.


DAVE...your code works!...
TABLE FILE TBLMSTR_UNITSWRVUS
SUM FST.TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR NOPRINT
BY LOWEST TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR

To be honest I have to digest it. Why I would want to SUM a field when I am trying to get unique values escapes me for now and does not seem logical....but it works!

All,
I want this unique list to appear in a multiselect OR drop down list for the user to select from when running the report. Anyway, we typically we use the GUI to create a multiselect as follows, for Example:

-SET &SPECIALTY = &IDXSPECIALTY.(OR(FIND TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY,TBLMSTR_BANBRBANAMEXREF.TBLMSTR_BANBRBANAMEXREF.IDXSPECIALTY IN TBLMSTR_BANBRBANAMEXREF)).Select the Desired Specialty/s.;


When we tried this methodology on the afore mentioned **RPTBANBR**...thats when we started getting errors that lead to this thread.


So, is there a way to incorporate what you all have showed me and use it to create a unique list of RPTBANBRs as a multiselect OR dropdown?

Robert,

This should work fine for you:

Make a .fex
( we call them 'helpers' )

TABLE FILE TBLMSTR_UNITSWRVUS
SUM COMPUTE DISP/A80 = FST.TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR;
BY LOWEST TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR
ON TABLE PCHOLD FORMAT XML
END

In the GUI choose dynamic, select this fex. And you're done.

G'luck

quote:

Originally posted by Dave:
Yes, WEP.

So did we...
...until we moved to a different RDMS ( and adapter ).

Then we got this error too...

Yeah, I think I've seen this happen too in certain cases.

I'm inclined to think this is a bug in the adapter(s). Otherwise, it's a bug in the FOCUS parser, as it should not accept invalid code. In either case the right thing to do is open a case with IBI so that they are at least aware of the issue and can start thinking about fixing it.

RobertF has not yet posted the SQL generated from his original request. We need to see the SQL before deciding if it's a bug or not.

In my 20 years of working with FOCUS/WebFOCUS, FOCUS DB/VSAM/Oracle/MS SQL Server/DB2/MySQL I have never written a TABLE FILE without a verb and list of columns. Am I missing something?

If you want a distinct set of values you ask for a distinct set of values, why write ambiguous code?

Reading a MS SQL Server table:

TABLE FILE MY_ACCOUNT
PRINT
DST.ACCOUNT
END

generates

SELECT T1."ACCOUNT", MAX(T1."ACCOUNT") FROM idr.vwaccount T1 GROUP BY T1."ACCOUNT" ORDER BY T1."ACCOUNT";

which looks correct.

TABLE FILE MY_ACCOUNT
SUM
ACCOUNT
BY ACCOUNT
END

generates

SELECT T1."ACCOUNT", SUM(T1."ACCOUNT") FROM idr.vwaccount T1 GROUP BY T1."ACCOUNT" ORDER BY T1."ACCOUNT";

which looks correct, based on the request but is not what you expect.

TABLE FILE MY_ACCOUNT
SUM
MAX.ACCOUNT
BY ACCOUNT
END

is how i would code it and it generates

SELECT T1."ACCOUNT", MAX(T1."ACCOUNT") FROM idr.vwaccount T1 GROUP BY T1."ACCOUNT" ORDER BY T1."ACCOUNT";

which is correct.

TABLE FILE MY_ACCOUNT
BY LOWEST ACCOUNT
END

generates

SELECT T1."ACCOUNT", SUM(T1."ACCOUNT") FROM idr.vwaccount T1 GROUP BY T1."ACCOUNT" ORDER BY T1."ACCOUNT";

which is not what you expected.

I don't understand how you can expect the data adapter to behave differently than this. This is a numeric column, so it sums the values. Ask for distinct values if you want distinct values.

They never metioned or stressed the DST or FST in class. In all the tools I have used I never had to worry about this. To accomplish what I want on our sql server for example:

select rptbanbr
from tblmstr_unitswrvus
group by rptbanbr


that yeilds a distinct list. Works in MsAccess as well. I have a bit to learn about the BI tool's behavior I guess.


When I run this...nothing happens...

-*-- Set up SQL tracing --------------------------------------------------------
-*-- Deactivate SQL tracing --------------------------------
SET TRACEOFF = ALL

-*-- Show Commands and data exchange between the -----------
-*-- physical and the logical layers of the data adapter
-*SET TRACEON = SQLCALL
-*-- Enable Trace for the SQL Translator -------------------
SET TRACEON = SQLTRANS

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

-*-- Show Optimization information -------------------------
SET TRACEON = SQLAGGR//CLIENT

-*-- Show SQL generated statement trace --------------------
-*SET TRACEON = STMTRACE/1/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 = 78

-*-- Activate SQL tracing ----------------------------------
SET TRACEUSER = ON
-RUN

TABLE FILE TBLMSTR_UNITSWRVUS
BY LOWEST TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END

Here is perhaps some useful trace:

 FOC2589 - AGGREGATION DONE BUT MAY PRODUCE INCONSISTENT RESULTS
 FOC2612 - OVERFLOW MAY OCCUR WHEN SUMMING AN I2 FIELD WITH USAGE I, D OR P
 SELECT
 T54."DAT_JAAR_COM_NR",
 SUM(T54."DAT_JAAR_COM_NR")(INTEGER)
 FROM
 PRD_DW2_DM.DIM_DATUM_A T54
 GROUP BY
 T54."DAT_JAAR_COM_NR"
 ORDER BY
 T54."DAT_JAAR_COM_NR";
 0 NUMBER OF RECORDS IN TABLE=        0  LINES=      0

quote:

Originally posted by Francis Mariani:
RobertF has not yet posted the SQL generated from his original request. We need to see the SQL before deciding if it's a bug or not.

In my 20 years of working with FOCUS/WebFOCUS, FOCUS DB/VSAM/Oracle/MS SQL Server/DB2/MySQL I have never written a TABLE FILE without a verb and list of columns. Am I missing something?

If you want a distinct set of values you ask for a distinct set of values, why write ambiguous code?

Actually, one of the purposes is to write unambiguous code. The other is to not repeat unnecessarily.

quote:

Reading a MS SQL Server table:

TABLE FILE MY_ACCOUNT
PRINT
DST.ACCOUNT
END

generates

SELECT T1."ACCOUNT", MAX(T1."ACCOUNT") FROM idr.vwaccount T1 GROUP BY T1."ACCOUNT" ORDER BY T1."ACCOUNT";

which looks correct.

It does, but the SQL is in fact not exactly what the TABLE request specified; it adds an unnecessary aggregation: MAX(T1."ACCOUNT") and it adds an ORDER BY that wasn't asked for.

The minimum required SQL to generate what RobertF needs is:

SELECT T1."ACCOUNT" FROM idr.vwaccount T1 GROUP BY T1."ACCOUNT" ORDER BY T1."ACCOUNT";

The reason to specify below is to get an ordered list of distinct values. The aggregation DST. does not explicitly specify an order, that is just an artefact of the implementation (that I wasn't aware of, so thanks for pointing that out).

To explicitly specify an ordered list of values, you have to add a BY-statement to the request. That BY-statement on itself should also be enough to result in a distinct list, as it acts both as an ORDER BY and a GROUP BY.

The minimal TABLE request that translates to is:

TABLE FILE MY ACCOUNT
BY ACCOUNT
END

Except that it doesn't translate to what we thought it would, as you point out.

quote:

TABLE FILE MY_ACCOUNT
SUM
MAX.ACCOUNT
BY ACCOUNT
END

is how i would code it and it generates

SELECT T1."ACCOUNT", MAX(T1."ACCOUNT") FROM idr.vwaccount T1 GROUP BY T1."ACCOUNT" ORDER BY T1."ACCOUNT";

which is correct.

It is, but it requires unnecessarily repeating statements and (as you found out) isn't as straightforward as you'd think; you have to specify MAX. or FST. or LST. to get correct results - especially since this is a numeric column; an alpha-numeric field would have yielded correct results without the aggregation.

That extra aggregation adds an extra, unneeded, computation to the generated SQL statement; after all, we don't care about the MAX value at all, but CPU cycles are spent on calculating it: that's not ideal.

quote:

TABLE FILE MY_ACCOUNT
BY LOWEST ACCOUNT
END

generates

SELECT T1."ACCOUNT", SUM(T1."ACCOUNT") FROM idr.vwaccount T1 GROUP BY T1."ACCOUNT" ORDER BY T1."ACCOUNT";

which is not what you expected.

Indeed, not what we expected.
I'm not sure why that SUM statement gets generated, it's totally unnecessary for the TABLE request. I already explained why I'm inclined to think there's a bug in here somewhere.

quote:

I don't understand how you can expect the data adapter to behave differently than this. This is a numeric column, so it sums the values. Ask for distinct values if you want distinct values.

And I (and apparently others) don't understand why the data adapter insists on adding a verb (and an aggregation) when we didn't ask for one.

If you are all still game.....

This all seems overly complicated. Lets take a simple example. We want to to create a hold file from our large detail table. This hold file will act as a Paramter DropDown data source for the user to select out what they want to report on. We plan to try 'chaining' in the HTML set up as well. The fields to be prompted to the user are:

SPECIALTY
ROLLUP
RPTBANBR
PROVIDER

Ideally, after the user picks a SPECIALTY, the ROLLUP will only show values in that specialty. (chaining) After selecting the Rollup, the RPTBANBRs will only be those found under the rollup etc etc.

Once the hold table is built, we can create paramters off it using the GUI. However, we *can not* even get the hold file built.

This Sql Code generate the values we need to attain:


SELECT
idxSpecialty,
idxRollup,
RptBanbr,
Billprovnm
FROM tblMSTR_unitswrvus
GROUP BY
idxSpecialty,
idxRollup,
RptBanbr,
Billprovnm


All fields are character except the afore mentioned RPTBANBR (I11).

I'll just ask, rather than posting more code...What would my Webfocus code look like to build the hold file? Sounds so simple...but we have been unable to get it to work...no matter what I try it yeilds:

Unknown error occurred. Agent on reporting server EDASERVE may have crashed or request was halted by the operator. Please investigate reporting server log.

SET HOLDLIST=PRINTONLY
-RUN

TABLE FILE tblMSTR_unitswrvus
SUM
idxSpecialty NOPRINT

BY idxSpecialty
BY idxRollup
BY RptBanbr
BY Billprovnm

ON TABLE HOLD AS H001
END
-RUN

And I don't understand why you cannot get a SQL trace to work.

THANKS!

works..takes 7 minutes as opposed to 15 seconds on the sql server but it works. I'll have the dbas looking onto index perhaps...or perhaps make a master table out of this and have sql build the table each time the large 'detail' table is built....as opposed to creating a hold file each time the report is run.

I really do not understand what is going on though...now we are summing a character field??

As far as the trace...what exactly should I see happen? Eseentially I click the blue triangle to run the fex...and nothing happens...

You need to look at the source of the generated HTML in your browser.

The trace output goes to the comments block at the end of the page that also holds the report source code when you set -SET &ECHO = ON or ALL.

SQL tracing is very frequently discussed on FocalPoint.

quote:

takes 7 minutes as opposed to 15 seconds on the sql server

- shouldn't happen. Check the SQL trace. Run the generated SQL using your SQL client. Compare the generated SQL with the SQL that takes 15 seconds. Do they result in the same number of rows. All part of normal report development.

quote:

now we are summing a character field

You need rows in a certain sequence? That what the BY statements are for. You need unique values? That's what the SUM is for. The column NOPRINT is part of the SUM. But this is where I drop out and get back to work. Good luck with the rest.

Fianally back on this and got the trace to work for this:

TABLE FILE TBLMSTR_UNITSWRVUS
BY LOWEST TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END



AGGREGATION DONE ...
SELECT
T1."RptBaNbr",
SUM(T1."RptBaNbr")
FROM
LVPG_FinancialPlanning.dbo.tblMSTR_UnitsWrvus T1
GROUP BY
T1."RptBaNbr"
ORDER BY
T1."RptBaNbr";
(FOC1400) SQLCODE IS 8115 (HEX: 00001FB3) XOPEN: 22003
: Microsoft SQL Server Native Client 10.0: [22003] Arithmetic overflow err
: or converting expression to data type int.
L (FOC1406) SQL OPEN CURSOR ERROR. : TBLMSTR_UNITSWRVUS

So it is putting a SUM in there...don't really understand why...but it is....Yes this code works:

TABLE FILE TBLMSTR_UNITSWRVUS
SUM FST.TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR NOPRINT
BY LOWEST TBLMSTR_UNITSWRVUS.TBLMSTR_UNITSWRVUS.RPTBANBR
ON TABLE NOTOTAL
END