I've got an A1 formatted field, ORDER_CLASS_CODE, in a SQL table that numeric fields have also been added to, but when the numbers are used in the WHERE clause to pass in a drill down, we get the following error:

--------
No HTML Output!
--------

(FOC1400) SQLCODE IS 8115 (HEX: 00001FB3) XOPEN: 22003
: Microsoft SQL Native Client: [22003] Arithmetic overflow error convertin
: g expression to data type int.
L (FOC1406) SQL OPEN CURSOR ERROR. : INVOICE_DATA


For example, if we try to pass ORDER_CLASS_CODE 'H' then everything drills down correctly, but when trying to pass ORDER_CLASS_CODE '3' then we get the above error.

Any ideas?

Show us the WHERE statement.

Here's the parent report:

TABLE FILE INVOICE_DATA
SUM
GROSS_EXT/D12.2CM AS 'Gross Sales'
NET_EXT/D12.2CM AS 'Net Sales'
SHIPPING_TOTAL/D12.2CM AS 'Freight'
SALES_TAX/D12.2CM AS 'Sales Tax'
COST_EXT/D12.2CM AS 'COGS'
BY ORDER_CLASS_CODE
BY ORDER_CLASS_DESC AS 'Order Class'
BY CAT4 AS 'Region'
HEADING
"STAPLES SBA REGION REPORT"
"for <+0>&SDATE<+0> to <+0>&EDATE"
" "
FOOTING
""
WHERE INVOICE_DATE GE &SDATE;
WHERE INVOICE_DATE LE &EDATE;
WHERE ORDER_CLASS_CODE EQ '3' OR '4' OR '8' OR '9';
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT &WFFMT.(,,).Select type of display output.
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=DATA,
COLUMN=N1,
FOCEXEC=app/staples_sba_region_reclass_report_for_travis1(SDATE='&SDATE' EDATE='&EDATE' WFFMT='&WFFMT' ORDER_CLASS_CODE=N1),
$
TYPE=TITLE,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=SUBTOTAL,
BY=1,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
SIZE=11,
BACKCOLOR='NONE',
STYLE=BOLD,
$
ENDSTYLE
END

And here's the child report:

TABLE FILE INVOICE_DATA
SUM
INVOICE_NUM AS 'Invoice #'
GROSS_EXT/D12.2CM AS 'Gross Sales'
NET_EXT/D12.2CM AS 'Net Sales'
SHIPPING_TOTAL/D12.2CM AS 'Freight'
SALES_TAX/D12.2CM AS 'Sales Tax'
COST_EXT/D12.2CM AS 'COGS'
BY ORDER_CLASS_CODE
HEADING
"STAPLES SBA REGION REPORT"
"for <+0>&SDATE<+0> to <+0>&EDATE"
" "
FOOTING
""
WHERE INVOICE_DATE GE &SDATE;
WHERE INVOICE_DATE LE &EDATE;
WHERE ORDER_CLASS_CODE EQ '&ORDER_CLASS_CODE';
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL' GROSS_EXT NET_EXT SHIPPING_TOTAL SALES_TAX COST_EXT
ON TABLE PCHOLD FORMAT &WFFMT.(,,).Select type of display output.
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=SUBTOTAL,
BY=1,
STYLE=BOLD,
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
SIZE=11,
BACKCOLOR='NONE',
STYLE=BOLD,
$
ENDSTYLE
END

A quick glance tells me that everything appears to be OK.

Have you tried tracing the SQL generated by WebFOCOUS?

Add the following before the TABLE request:

SET TRACEOFF = ALL
SET TRACEON = SQLTRANS
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON

This will display the generated SQL along with other messages.

Please post the generated SQL here.

I copied your code and pasted it into the parent and child report before the TABLE request and this is all it's generating when I try to run the drill from the parent. The parent report runs fine, it's the child that returns this:

(FOC1400) SQLCODE IS 8115 (HEX: 00001FB3) XOPEN: 22003
: Microsoft SQL Native Client: [22003] Arithmetic overflow error convertin
: g expression to data type int.
L (FOC1406) SQL OPEN CURSOR ERROR. : INVOICE_DATA

Am I doing something wrong with where I'm pasting your code?

Your child report is more or less the same as the parent.

I would expect more details in the child, so p.e. a by field on the "INVOICE_NUMBER"

but that won't solve the problem I think.

I suppose the SQL error you're getting disables the SQL tracing.

This won't solve the problem, but check the WebFOCUS Master. Does it correspond to the database table? Is the field A1 in the Master but not in the table?

Try putting a .EVAL on:
WHERE ORDER_CLASS_CODE EQ '&ORDER_CLASS_CODE.EVAL';

Add SET XRETRIEVAL=OFF after the trace commands in the child program. That will parse the TABLE request but not retrieve any data. Make sure the output type is HTML. Then do a view source to look at the SQL to see if it looks correct and is passing the value as you would expect.

Also, you are summing an alpha field (INVOICE_NUM). Could you make this a BY field instead. The error you are getting indicates an arithmetic overflow which I would assume has something to do with the fields you are summing.

Also could you post your master?

Thank you all so much for coming to the rescue! The .EVAL solution worked (you're a gem, Pat), but I was wondering if you could explain why.

Thank you again, you saved us!

.EVAL should not be necessary. I suspect that this need is caused because the &variable name length is over the maximum 12 characters that is the declared maximum allowed.

You have a variable in quotes. You don't want to pass the variable but rather the value of the variable. .EVAL forces evaluation where it is encountered.