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,

What happens if you use:

  
IF SITE EQ 2

This statement does work also.

Jeff

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.

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

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.

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?

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,

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.

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?

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

FYI,
Using INFORMIX adapter to connect to a INFORMIX database on a HP server running HPUX