We have a focexec using SQL passthru and are printing two numeric columns - one is a count of records (which we want as integer/no decimals) and the other is a $ amount (showing cents). The default for the Count field is giving 2 decimals. If I turn it off with the ENGINE SQLORA SET CONVERSION FLOAT PRECISION 14 0, then I lose the cents on the dollar amount. It seems that it's all or nothing. The actual fex is:

ENGINE SQLORA SET DEFAULT_CONNECTION UCCBTST2
ENGINE SQLORA SET CONVERSION FLOAT PRECISION 14 0
SQL SQLORA
SELECT DISTINCT substr( TO_CHAR(A.CRE_DTTM,'YYYY-MM-DD'),1,7) "Year/Month",
A.TNDR_SOURCE_CD "Source",
COUNT(*) "Count Pay Event ID",
SUM( B.TENDER_AMT) "Sum Tender Amount"
FROM CI_TNDR_CTL A, CI_PAY_TNDR B
WHERE A.TNDR_CTL_ID = B.TNDR_CTL_ID
AND A.CRE_DTTM >= TO_DATE(&FROM_CRE_DT,'YYYYMMDD')
AND A.CRE_DTTM <= TO_DATE[&TO_CRE_DT,'YYYYMMDD')
GROUP BY substr( TO_CHAR(A.CRE_DTTM,'YYYY-MM-DD'),1,7), A.TNDR_SOURCE_CD
ORDER BY 1, 2, 3
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD
END
-*

DEFINE FILE HOLD
COUNTER/I6=1;
END
-*

TABLE FILE HOLD
SUM COUNTER NOPRINT
PRINT *
HEADING
"xxxxxxxxxxxx UTILITIES"
"Query: CU_CASH_PAY_SOURCE_DATE_SORT Count: "Run Date: &DATEMDYY"
" "
ON TABLE PCHOLD FORMAT EXL2K
END

Of course, the other option is to rewrite it to native WF, but the users are trying to stay consistent with using SQL's.

Bill S.
Webfocus 7.6.7
Oracle 11g

This message has been edited. Last edited by: <Kathryn Henning>,

Webfocus 8.0.0.8
AIX 7.x and Windows 2012
EXL2K/07, PDF, HTML

Instead of using the decimal restriction at the ENGINE SQL level use the formatting at the final output of your report like

TABLE FILE HOLD
SUM
COUNT_PAY_EVENT_ID/D20
TENDER_AMOUNT/D20.2MBC
....
END
D20.2MBC - M prints $ symbol, B adds parenthesis for negative values, C -comma

How about casting the count as a number with zero precision:

ENGINE SQLORA SET DEFAULT_CONNECTION myschema
-RUN

SQL SQLORA
select cast(count(*) as number(9)) as count from sys.all_tables;

TABLE ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

?FF H001
-RUN

Thanks for both for the quick response. We went with Francis' solution of the using the CAST function - works perfectly.