Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Possible reasoning for getting a FOC1406) SQL OPEN CURSOR ERROR

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Possible reasoning for getting a FOC1406) SQL OPEN CURSOR ERROR
 Login/Join
 
Gold member
posted
Environment: WebFocus 7.6.4
Table Name: ACT
Field Name Size
Number A14
site I4
SDATE date (MDYY)
EDATE date (MDYY)

SAMPLING OF DATA RECORDS

NUMBER SITE SDATE EDATE
CCB 2 01/08/2008 01/17/2008
XP 4 08/04/2008 08/08/2008
XPP-80 4 06/10/2008 06/20/2008
XTP-80 4 07/08/2008 07/18/2008
GFIT-83 4 08/18/2008 08/22/2008
DLE-84 4 06/17/2008 06/19/2008
AIA-814 4 06/12/2008 06/12/2008

PROBLEM: FOC1406 error when executing the following:
DEFINE FILE ACT
SITENAME/A10=DECODE SITE( 1 GEORGIA 2 TENNESSEE 3 NEW_MEXICO 4 MARYLAND 6 DC 5 OTHER ELSE GEORGIA );
END
TABLE FILE ACT
PRINT ‘NUMBER'
‘SITE'
‘SDATE'
‘EDATE'
‘SITENAME'
WHERE SITENAME EQ 'TENNESSEE';
END
(FOC1400) SQLCODE IS -201 (HEX: FFFFFF37) XOPEN: 42000
: A syntax error has occurred.
L (FOC1406) SQL OPEN CURSOR ERROR. : ACT

NOTE: If I run the same report using the WHERE clause:

WHERE ACT.ACT.SITENAME LIKE ‘T%’;

I get correct information:

CCB 2 01/08/2008 01/17/2008 TENNESSEE

I also used the Command Console to execute the same queries and get the same results. Everything work correctly when using LIKE VS EQ in the where clause.

Any thoughts on what's causing this?

Jeff

This message has been edited. Last edited by: Kerry,


7.7.02 Windows7
HTML/Excel/PDF
 
Posts: 66 | Registered: April 16, 2008Report This Post
Virtuoso
posted Hide Post
What happens if you use:
  
IF SITE EQ 2


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Gold member
posted Hide Post
This statement does work also.

Jeff


7.7.02 Windows7
HTML/Excel/PDF
 
Posts: 66 | Registered: April 16, 2008Report This Post
Virtuoso
posted Hide Post
I'm not sure this is causing your problem, but the fact that your only selection criteria is on a defined field means that you are actually doing a table scan because that selection criteria is not passed to the DBMS. It can only eliminate the record AFTER is has been read and the DEFINEd field is calculated, so it has to read EVERY record. I would recommend using the SITE field in your WHERE instead of a defined field.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
I take it that SITENAME is not in the original datasource? It is just a define and no real field?

If that is the case, then it does seem that the decode along with the selection is passed to the dbms. Which subsequently leads to the error. To check if the request is passed or not, you can activate the stmtrace and see what sql is being generated.
SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEUSER = ON


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
GamP,
Thanks for the suggestion. I added the code and here is what its showing now. Not sure what the message is telling me.

-* File JUNK.fex
SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEUSER = ON
DEFINE FILE ACT
SITESTUFF/A10=DECODE SITE( 1 GEORGIA 2 TENNESSEE 3 NEW_MEXICO 4 MARYLAND 5 OTHER 6 DC ELSE GEORGIA );
END
TABLE FILE ACT
PRINT
'ACT.ACT.NUMBER'
'ACT.ACT.SNAME'
'ACT.ACT.CNAME'
WHERE SITESTUFF EQ 'GLYNCO'
END
09.46.55 BR (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
09.46.55 BR (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
09.46.55 AE SELECT T1.number,T1.site,T1.category FROM act T1 WHERE
09.46.55 AE ((CASE (T1.site) WHEN 1 THEN 'GEORGIA' WHEN 2 THEN 'TENNESSEE'
09.46.55 AE WHEN 3 THEN 'NEW_MEXICO' WHEN 4 THEN 'MARYLAND' WHEN 5 THEN
09.46.55 AE 'OTHER' WHEN 6 THEN 'DC' ELSE 'GEORGIA' END) = 'GLYNCO');
(FOC1400) SQLCODE IS -201 (HEX: FFFFFF37) XOPEN: 42000
: A syntax error has occurred.
L (FOC1406) SQL OPEN CURSOR ERROR. : ACT


7.7.02 Windows7
HTML/Excel/PDF
 
Posts: 66 | Registered: April 16, 2008Report This Post
Virtuoso
posted Hide Post
I would suggest now that you run the same with the condition
WHERE SITESTUFF LIKE ‘T%’;
and see what gets passed, because it seems that the CASE command is not recognized.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
Jeff--

What's the brand and release of the data server?

"GLYNCO?" Why didn't you stick with Tennessee? And you also removed SITESTUFF as a printed verb object.

quote:
Not sure what the message is telling me.


The AE lines contain the generated SQL code. Reformatted, it's readily seen as a straight-forward translaton of your TABLE request:
 SELECT T1.number
 ,T1.site
 ,T1.category
 FROM act T1
 WHERE
 (
  (CASE (T1.site)
        WHEN 1 THEN 'GEORGIA'
        WHEN 2 THEN 'TENNESSEE'
        WHEN 3 THEN 'NEW_MEXICO'
        WHEN 4 THEN 'MARYLAND'
        WHEN 5 THEN 'OTHER'
        WHEN 6 THEN 'DC'
        ELSE 'GEORGIA'
   END)
   = 'GLYNCO'
 )


The CASE ... END part is equivalent to the Decode that underlies the defned SITESTUFF field. So WF is passng your selection criterion through to SQL (or a least attempting to). Maybe the server is choking on CASE, or the extra parentheses (unlikely), or punctuation (trailing ";", no trailing END). -- Best way to tell (as Dan suggested) is to see the generated SQL for a similar TABLE tha works -- when the WHERE is omitted entirely, or using LIKE.

What happens when you submit that SELECT code directly?


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Gold member
posted Hide Post
I get the following message but the "LIKE" command does work as appropriate records are displayed??

....
....
....

WHERE SITESTUFF LIKE 'T%'
end


12.47.21 BR (FOC2598) FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : SITESTUFF
12.47.21 BR (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
12.47.21 BR (FOC2594) AGGREGATION IS NOT APPLICABLE TO THE VERB USED
12.47.21 AE SELECT T1.number,T1.site,T1.category FROM act T1;

I did come up with a work around but would like to figure out why using SITESTUFF EQ 'TENNESSEE' doesn't want to work correctly. Seems to me to be straight forward

I did a simple report just to print out the NUMBER SITE and SITESTUFF fields (Without any where clause) and the DECODE statements looks to work correctly as the output shows

NUMBER SITE SITESTUFF
T2802A 2 TENNESSEE
TSI-804 4 MARYLAND
EIISTP-80 1 GEORGIA
E_FPS 6 DC

This message has been edited. Last edited by: Jeff_Rowland,


7.7.02 Windows7
HTML/Excel/PDF
 
Posts: 66 | Registered: April 16, 2008Report This Post
Virtuoso
posted Hide Post
quote:
SELECT T1.number,T1.site,T1.category FROM act T1;

-- note that the CASE construct is gone -- WF couldn't figue how to present that "WHERE [field defind by DECODE] LIKE [pattern]" predicate; instead WF asked for ALL the rows, and will impose the screening condition on the WF-server side of the connection.

It seems clear that the data adapter you specify in your synonym (you still didn't identify which) believes your data server undersands

WHERE ((CASE ... END) EQ '...')

-- while in fact your server cannot digest it.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
You never specified your database. Is it possible you're trying to use a SQL Server 2005 Adapter with an SQL Server 2000 database or something similar?


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
Aha, so the decode with the eq test IS passed to sql, while the decode with the like is not ...
This CASE statement is probably the culprit. From the look of it, it is indeed MS SQL server so what I would try to do is find out what the correct sql syntax should be - by running it in the native sql engine for instance, and when you found this out take out a ticket with IB to get it fixed.
Who knows, it may already be fixed in a later version than what you're using ...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
FYI,
Using INFORMIX adapter to connect to a INFORMIX database on a HP server running HPUX


7.7.02 Windows7
HTML/Excel/PDF
 
Posts: 66 | Registered: April 16, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Possible reasoning for getting a FOC1406) SQL OPEN CURSOR ERROR

Copyright © 1996-2020 Information Builders