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>,

They're fully documented here: http://infocenter.informationb...om/wf81rel/index.jsp

search for Null or Missing.

ISNULL I believe is EQ MISSING in WebFOCUS.

What I mean is the function used in webfocus pseudo SQL like this:

SQL
SELECT FIELDS
FROM MASTER_FILE;
TABLE SQLOUT
ON TABLE HOLD AS...
END

Not the TABLE FILE SYNTAX.

You CAN'T use webfocus functions in webfocus pseudo SQL.

quote:

Originally posted by BabakNYC:
They're fully documented here: http://infocenter.informationb...om/wf81rel/index.jsp

search for Null or Missing.

ISNULL I believe is EQ MISSING in WebFOCUS.

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

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; 

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.

Which vendor do you belong bug?