SQL0104 when accessing DB2

[lamunta]

A report that I have returns this error when connecting to DB2;

&quot;SQL0104 Token ( was not valid. Valid tokens: + - AS <IDENTIFIER>&quot;

Crystal then displays that an ODBC error has occured and then crashes.

This only occurs on one report in Crystal 8.5. I also have versions of this report in both Cognos and in Business object and these both work without problems. I assume that there are no issues with the ODBC connection or the database as I do get the details out in other reports.

The Crystal website and the IBM website are little help on SQL0104 and I have tried removing calculations from the report with no success.

Has anyone experienced this issue or could point me in the right direction to identify the issues further?

Thanks

 

[synapsevampire]

I'd check the SQL being generated by Crystal (Database->Show SQL Query).

Next you might trace the ODBC connection, it should provide more details, or trace what the database is being passed, which should be even more informative.

Sometimes numerous errors are returned by ODBC, and the one you're seeing may be just the 3rd (standard) in the error object, and not all that informative, whereas another message in the object might help to identify the nuisance.

DB2 does work with Crystal, and this generic error is fairly common for ODBC drivers.

-k

 

[lamunta]

This is the SQL it processes. The only thing I am unsure about is the way that the date is parsed, but I have removed the date processing from the report and still get the error.

SELECT
doc.&quot;DOCCODE&quot;, doc.&quot;DOCNUM&quot;, doc.&quot;CURDOC&quot;, doc.&quot;DOCDATE&quot;, doc.&quot;INPDATE&quot;,
cmp.&quot;CODE&quot;, cmp.&quot;NAME&quot;, cmp.&quot;CUR&quot;,
line.&quot;DOCLINE&quot;, line.&quot;CODE1&quot;, line.&quot;CODE2&quot;, line.&quot;CODE3&quot;, line.&quot;CODE4&quot;, line.&quot;CODE5&quot;, line.&quot;CODE6&quot;, line.&quot;CODE7&quot;, line.&quot;CODE8&quot;, line.&quot;VALUE2&quot;, line.&quot;VALUE1&quot;,
desc1.&quot;NAME1&quot;,
desc2.&quot;NAME2&quot;,
desc3.&quot;NAME3&quot;,
desc4.&quot;NAME4&quot;,
desc5.&quot;NAME5&quot;,
desc6.&quot;NAME6&quot;,
desc7.&quot;NAME7&quot;,
desc8.&quot;NAME8&quot;,
{fn DATABASE()},
{fn USER()}
FROM
(((((((((&quot;DOC&quot; doc INNER JOIN &quot;LINE&quot; line ON
doc.&quot;CMPCODE&quot; = line.&quot;CMPCODE&quot; AND
doc.&quot;DOCCODE&quot; = line.&quot;DOCCODE&quot; AND
doc.&quot;DOCNUM&quot; = line.&quot;DOCNUM&quot
INNER JOIN &quot;CMP&quot; cmp ON
doc.&quot;CMPCODE&quot; = cmp.&quot;CODE&quot
INNER JOIN &quot;DESC2&quot; desc2 ON
line.&quot;CMPCODE&quot; = desc2.&quot;CMPCODE&quot; AND
line.&quot;CODE2&quot; = desc2.&quot;CODE&quot
INNER JOIN &quot;DESC3&quot; desc3 ON
line.&quot;CMPCODE&quot; = desc3.&quot;CMPCODE&quot; AND
line.&quot;CODE3&quot; = desc3.&quot;CODE&quot
INNER JOIN &quot;DESC4&quot; desc4 ON
line.&quot;CMPCODE&quot; = desc4.&quot;CMPCODE&quot; AND
line.&quot;CODE4&quot; = desc4.&quot;CODE&quot
INNER JOIN &quot;DESC5&quot; desc5 ON
line.&quot;CMPCODE&quot; = desc5.&quot;CMPCODE&quot; AND
line.&quot;CODE5&quot; = desc5.&quot;CODE&quot
INNER JOIN &quot;DESC6&quot; desc6 ON
line.&quot;CMPCODE&quot; = desc6.&quot;CMPCODE&quot; AND
line.&quot;CODE6&quot; = desc6.&quot;EL6_CODE&quot
INNER JOIN &quot;DESC7&quot; desc7 ON
line.&quot;CMPCODE&quot; = desc7.&quot;CMPCODE&quot; AND
line.&quot;CODE7&quot; = desc7.&quot;CODE&quot
INNER JOIN &quot;DESC8&quot; desc8 ON
line.&quot;CMPCODE&quot; = desc8.&quot;CMPCODE&quot; AND
line.&quot;CODE8&quot; = desc8.&quot;CODE&quot
INNER JOIN &quot;DESC1&quot; desc1 ON
line.&quot;CMPCODE&quot; = desc1.&quot;CMPCODE&quot; AND
line.&quot;CODE1&quot; = desc1.&quot;CODE&quot;
WHERE
cmp.&quot;CODE&quot; LIKE '%' AND
doc.&quot;INPUTDATE&quot; >= {d '1900-01-01'} AND
doc.&quot;INPUTDATE&quot; <= {d '2050-01-01'}
ORDER BY
cmp.&quot;NAME&quot; ASC,
doc.&quot;DOCCODE&quot; ASC,
doc.&quot;DOCNUM&quot; ASC,
line.&quot;DOCLINE&quot; ASC

These are the lines from the ODBC trace. Thay mean nothing to me! The rest of the lines are just hex & DLL information

22065828+240 [01] err: [IBM][iSeries Access ODBC Driver]Invalid attribute or option identifier. dsn: db2_src sys: AS400A

 

[synapsevampire]

The SQL posted still has the date being passed, which might be causing some grief.

I also don't like the looks of the cmp.&quot;CODE&quot; LIKE '%' AND

This looks wrong, it should either have an explicit LIKE, as in LIKE 'Smith%', or it is meaningless.

If you are doing this in your record selection formula, remove it and replace it with one that passes SQL when required, not always, as in:

If {?parameter} <> &quot;&quot; then
{cmp.CODE} like + {?parameter}+&quot;%&quot;
else
If {?parameter} = &quot;&quot; then
true

-k

 

[lamunta]

Thanks for your help. I've learnt a lot from your advice which will come in useful. I have now managed to track down the cause of the problem to {fn DATABASE()} and {fn USER()}.

I don't know why they are causing the report problems, but once removed, the report runs ok. As they are not essential to the report its a solution (in part).

Thanks again.