[CLOSED]webfocus SQL function and syntax reference
Is there a document for Webfocus SQL functions? I can use MAX/MIN/SUM and CASE. But not sure what other function and syntax are supported. For example I need to use ISNULL() or NULLIF() but they are not supported.
Thanks.This message has been edited. Last edited by: <Emily McAllister>,
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)
February 19, 2016, 12:03 PM
BabakNYC
The SQL syntax you're talking about should really be used in pass thru mode which means, you send the native SQL of the RDBMS you're using, and the answer set returned is in memory and can be used by TABLE syntax using a master file called SQLOUT. In the SQL portion you can use whatever syntax your native RDBMS supports. Here's an example for SQL Server:
ENGINE SQLMSS SET DEFAULT_CONNECTION CON01
SQL SQLMSS PREPARE SQLOUT FOR
SELECT * FROM ORDERS
END
TABLE FILE SQLOUT
SUM
CNT.CustomerID AS 'COUNT,CustomerID'
BY LOWEST ShipName
BY LOWEST OrderID
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
WebFOCUS 8206, Unix, Windows
February 21, 2016, 12:51 PM
David Briars
quote:
...For example I need to use ISNULL()...
Example of using SQL scalar functions in a TABLE FILE command:
-* File ExSQLFunction.fex
-*
-* Set display of null data.
-*
SET NODATA = NULL
-*
-* 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
-*
-* Display Special Offers.
-*
DEFINE FILE SPECIALOFFER
DISPLAY_MAXQTY/I11 = SQL.ISNULL(MAXQTY, 0);
END
TABLE FILE SPECIALOFFER
PRINT DESCRIPTION
DISCOUNTPCT
MINQTY
MAXQTY
DISPLAY_MAXQTY
ON TABLE SET STYLE *
INCLUDE=eninformationbuilders_light1.sty,$
ENDSTYLE
END
This example focexec is running against the AdventureWorks2014 MS SQL Server sample database.
Generated SQL:
SELECT
ISNULL(T1."MaxQty", 0),
T1."Description",
T1."DiscountPct",
T1."MinQty",
T1."MaxQty"
FROM
AdventureWorks2014.Sales.SpecialOffer T1;
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
February 29, 2016, 12:22 PM
bug
quote:
Originally posted by David Briars:
quote:
...For example I need to use ISNULL()...
Example of using SQL scalar functions in a TABLE FILE command:
-* File ExSQLFunction.fex
-*
-* Set display of null data.
-*
SET NODATA = NULL
-*
-* 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
-*
-* Display Special Offers.
-*
DEFINE FILE SPECIALOFFER
DISPLAY_MAXQTY/I11 = SQL.ISNULL(MAXQTY, 0);
END
TABLE FILE SPECIALOFFER
PRINT DESCRIPTION
DISCOUNTPCT
MINQTY
MAXQTY
DISPLAY_MAXQTY
ON TABLE SET STYLE *
INCLUDE=eninformationbuilders_light1.sty,$
ENDSTYLE
END
This example focexec is running against the AdventureWorks2014 MS SQL Server sample database.
Generated SQL:
SELECT
ISNULL(T1."MaxQty", 0),
T1."Description",
T1."DiscountPct",
T1."MinQty",
T1."MaxQty"
FROM
AdventureWorks2014.Sales.SpecialOffer T1;
It's good to know there is this "SQL.ISNULL()" thing. Is it new from ver 8?
But what I am looking for is the standard functions available in the pseudo SQL code, not the real SQL passthru or pure webfocus syntax.
Pseudo Webfocus SQL is quick and easy when you need to join 2 different data sources. It supports the following syntax as I tested:
FULL JOIN, LEFT JOIN, INNER JOIN CASE WHEN..ELSE..END SUM(), MAX(), MIN() GROUP BY
with the limitation of:
1. Can only join on one field, if neither source is a relational database (SQL, DB2, Oracle...);
2. Does not support sub query, like
..WHERE A IN (SELECT B FROM TABLE_B).
I also have trouble comparing dates in pseudo SQL.
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)