Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


Grzegorz Grzegorczyk

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...