July 12, 2013, 04:20 PM
cnslpsError code
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.
July 15, 2013, 01:37 AM
MichaelBalleI thik that's the problem:
(T1."TIMES_RENEWED" = 0) AND
(T1."PUB_NAME" = 'Environmental Nutrition') AND [COLOR:RED]( = 1)[/COLOR] AND
(T2."COUNTRY" = 'USA')[
July 15, 2013, 03:48 AM
Wep5622That 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
July 15, 2013, 09:02 AM
Doug Always a good idea.
quote:
Wep5622: split your single WHERE clause into several (imho)
July 15, 2013, 10:26 AM
j.grossTo 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.
July 26, 2013, 01:45 PM
Brian SuterThe first thing I would try is EQ instead of IN (one value)