Hi, I have been blocked by Webfocus strange behaviour with ...

[Grzegorz Grzegorczyk]

Hi,

I have been blocked by Webfocus strange behaviour with WITH clause when I tried to use it to report from SQL Server database.

To replicate error you can use following code:

 

ENGINE SQLMSS SET DEFAULT_CONNECTION InformationVault

SQL SQLMSS PREPARE SQLOUT FOR

WITH TST (TEST)

AS(

SELECT 'TEST' AS TEST

),

SELECT TEST FROM TST

;

END

TABLE FILE SQLOUT

TEST

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,$

ENDSTYLE

END

 

 

Webfocus returns following error:

 

(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN:

(FOC1500) : (0) [ ] A processing error Incorrect syntax near the keyword

(FOC1500) : with. If this statement is a common table expression, an

(FOC1500) : xmlnamespaces clause or a change tracking context clause, the previous

(FOC1500) : statement must be terminated with a semicolon. has occurred.

 

Putting semicolon ; just before with results in nothing returned:

 

L (FOC1487) SQL SYNTAX ERROR

0 ERROR AT OR NEAR LINE 11 IN PROCEDURE ADHOCRQ FOCEXEC *

(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT

(FOC009) Request failed validation, not executed.

 

Any ideas how to resolve it Similar statement executed on ORACLE SQL returns data without issue.

[Martin Yergeau]

Cannot confirm 100%, but I faced the same issue.

I think that you cannot use the WITH syntax because you are creating a tempory table and the connector cannot manage that.

ORACLE connector may have a different way to manage that since the WITH feature has been introduced by Oracle.

I understand that using that feature allows you to break down complex queries into smaller ones and helps for debuggging.

Try using normal sub-query or just as simple as

ENGINE SQLMSS SET DEFAULT_CONNECTION InformationVault

SQL SQLMSS PREPARE SQLOUT FOR

SELECT <something>

FROM <someTable>

END

TABLE FILE SQLOUT

PRINT *

ON TABLE SET PAGE-NUM NOLEAD

ON TABLE SET ASNAMES ON

ON TABLE NOTOTAL

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,$

ENDSTYLE

END

[Charles Morris 2]

yes, youre also missing a verb in your TABLE FILE SQLOUT, but it doesnt surprise me that WITH in SQLMSS doesnt behave as expected

[Sarah Buccellato]

Hi Greg, were Martin and Charless posts helpful If youre still facing the same issue, you may need to open a support case.

[Debra Waybright]

When I use a CTE (WITH) I have to put ENGINE SQLMSS SET CURSORS CLIENT before the SQL SQLMSS PREPARE statement.

[Grzegorz Grzegorczyk]

Hi Debra,

I believe your tip is solution to issue that I have described.

Unfortunately our Webfocus server works on Linux machine and uses JDBC adapter. This means that statement SET CURSORS CLIENT will not work.

This is what I have found in Reporting Server help:

 

image.png1859225 54.5 KB

 

Anyway thank you for clear answear.