I am running the following code:

APP PATH BEL
JOIN
INNER SUBSCRIBER.SUBSCRIBER.CUSTOMER_NUMBER IN SUBSCRIBER TO MULTIPLE
CUSTOMER.CUSTOMER.CUSTOMER_NUMBER IN CUSTOMER TAG J0 AS J0
END
TABLE FILE SUBSCRIBER
PRINT
'SUBSCRIBER.SUBSCRIBER.CUSTOMER_NUMBER'
HEADING
""
FOOTING
""
WHERE ( SUBSCRIBER.SUBSCRIBER.PUB_NAME IN ('Environmental Nutrition') ) AND ( J0.CUSTOMER.COUNTRY IN ('USA') ) AND ( SUBSCRIBER.SUBSCRIBER.TIMES_RENEWED EQ 0 ) AND ((( SUBSCRIBER.SUBSCRIBER.DATE_PAID GE '2013-05-10' ) AND ( SUBSCRIBER.SUBSCRIBER.DATE_PAID LE '2013-08-15' ) AND ( SUBSCRIBER.SUBSCRIBER.SUBSCRIBER_STATUS IN ('Active') )) OR (( SUBSCRIBER.SUBSCRIBER.DATE_PAID GE '2013-05-10' ) AND ( SUBSCRIBER.SUBSCRIBER.DATE_PAID LE '2013-08-15' ) AND ( SUBSCRIBER.SUBSCRIBER.SUBSCRIBER_STATUS IN ('Open account') )));
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END


And getting the following error:

________________________________________
No HTML Output!
________________________________________
(FOC1400) SQLCODE IS -104 (HEX: FFFFFF98)
: [42601] Token = was not valid. Valid tokens: ( + - ? : DAY INF NAN NOT R
: ID ROW RRN CASE CAST CHAR DATE DAYS.
L (FOC1406) SQL OPEN CURSOR ERROR. : SUBSCRIBER


Here is the SQL that is being generated:

FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
SELECT T1."CUSTOMER_NUMBER",T1."PUB_NAME",T1."TIMES_RENEWED",
T2."COUNTRY" FROM BELDB/SUBSCRIBER T1,BELDB/CUSTOMER T2 WHERE
(T2."CUSTOMER_NUMBER" = T1."CUSTOMER_NUMBER") AND
(T1."TIMES_RENEWED" = 0) AND (T1."PUB_NAME" = 'Environmental Nutrition') AND ( = 1) AND (T2."COUNTRY" = 'USA')
FOR FETCH ONLY;
...RETRIEVAL KILLED
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0

The issue is SQL is generating the (= 1). Any help would be greatly appreciated.

I thik that's the problem:

(T1."TIMES_RENEWED" = 0) AND 
(T1."PUB_NAME" = 'Environmental Nutrition') AND [COLOR:RED]( = 1)[/COLOR] AND 
(T2."COUNTRY" = 'USA')[

That looks like a bug in the FOCUS to SQL translation. You should report it to IBI.

Do you still get the problem if you split your single WHERE clause into several, with only 1 condition on each line? I suspect you're running into one of the many length limits in FOCUS...

For example:

WHERE SUBSCRIBER.SUBSCRIBER.PUB_NAME IN ('Environmental Nutrition');
WHERE J0.CUSTOMER.COUNTRY IN ('USA');
WHERE SUBSCRIBER.SUBSCRIBER.TIMES_RENEWED EQ 0
WHERE (SUBSCRIBER.SUBSCRIBER.DATE_PAID FROM '2013-05-10' TO '2013-08-15'
 AND SUBSCRIBER.SUBSCRIBER.SUBSCRIBER_STATUS IN ('Active'))
 OR (SUBSCRIBER.SUBSCRIBER.DATE_PAID FROM '2013-05-10' TO '2013-08-15' AND SUBSCRIBER.SUBSCRIBER.SUBSCRIBER_STATUS IN ('Open account'));

N.B. I think I miscounted on the braces there, you have so many! I prefer to not use unnecessary braces so that I can keep track of them

Always a good idea.

quote:

Wep5622: split your single WHERE clause into several (imho)

To narrow down the problem, I would create DEFINE fields for those WHERE expressions, and then drop the WHERE clauses but add the defined columns as verb objects, to see if the generated SQL is correct correct. Then add the conditions back in, with WHERE clauses referencing the Defines version of the condition clauses. Play with it, see where it breaks.

The first thing I would try is EQ instead of IN (one value)