One of the strengths of WebFOCUS is the ability make direct ...

[David Briars]

One of the strengths of WebFOCUS is the ability make direct SQL function calls.

The SQL adapters support SQL scalar functions in a request.

Here is an example running against the WebFOCUS test MSSQL WF_RETAIL_LITE database:

-*

-* Turn on SQL Tracing.

-*

SET TRACEOFF = ALL

SET TRACEON = SQLTRANS

SET TRACEON = STMTRACE//CLIENT

SET TRACEON = SQLAGGR//CLIENT

SET TRACESTAMP = OFF

SET TRACEWRAP = 78

SET TRACEUSER = ON

-RUN

-*

-* Create Revenue Report

-*

APP PREPENDPATH WFRETAIL

DEFINE FILE WF_RETAIL_LITE

REVENUE_ROUNDED/I12CM = SQL.ROUND(REVENUE_US,0,0);

END

-*

TABLE FILE WF_RETAIL_LITE

"Revenue By Product Category By Date"

PRINT REVENUE_US

REVENUE_ROUNDED AS 'Revenue, Rounded'

BY PRODUCT_CATEGORY

BY TIME_DATE

IF READLIMIT EQ 30

ON TABLE COLUMN-TOTAL

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$

TYPE=TITLE, STYLE=-UNDERLINE, JUSTIFY=CENTER,$

ENDSTYLE

END

 

 

 

image.png534518 10.1 KB

[Peter Vazny]

Thank you! This helps a lot.

Here is the related documentation: Passing Function Calls Directly to a Relational Engine Using SQL.Function Syntax

[David Briars]

Oh, you are welcome. Glad it helped.

Thanks for providing the link to the doc.

[Debra Waybright]

Ive just recently started doing this! You can also create a WebFocus Function that executes the SQL function and define it so your InfoAssist users can access it like a built in function.