Error Message: Unable to Understand after DECLARE





General Advice

Unable to understand after - "DECLARE" is a generic Progress syntax error. If you receive "DECLARE" errors, take the following action:
  1. Identify the SQL statement that is associated with this error. You may need to generate a client-side ODBC trace, if your client application generates SQL for you.
  2. Look for obvious flaws or incompatibilities in the structure of the statement.
    • Progress may not be able to handle complex and/or nested JOIN queries.
    • Progress databases do not recognize the AS keyword for column aliasing. Progress 9.x was the first version to support standard SQL column aliasing.
    • Progress 6, 7, and 8 do not support standard SQL column aliasing. They do have a special LABEL keyword which allows aliasing within "native" Progress applications. Our drivers do not support this keyword.
  3. Test the SQL statement through C++ Demo, odbctest, JDBC Demo, or another simple application. See if the error persists.
  4. Test the SQL statement through a Progress native interface such as clidemo. See if the problem persists.
  5. Finally, contact OpenLink Product Support, if you cannot detect the source of this problem. When you contact us, please be sure to include the results of the tests described above.

Advice for Crystal Reports and Business Objects users

Our drivers may produce varied results when used in conjunction with a Progress database and Seagate Crystal Reports.

Some Crystal Reports auto-generated SQL used with our Release 3.2 components will succeed.

However, Crystal Reports auto-generated SQL for use with our Release 4.x and up will often produce the error.

This error may be caused by one of the following:

JOIN query

Queries generated by Crystal Reports which join multiple tables will use advanced SQL JOIN syntax, which Progress' SQL-89 interface does not support. Note that all Multi-Tier Agents with the naming convention "pro8*" or "pro9*" connect to the Progress SQL-89 interface and thus will expose this problem. The SQL-92 based Agents and Lite drivers should get around this problem as they are using the advanced SQL-92 Progress engine which will understand this query syntax.

If you experience this problem, take the following action:

  1. Download ODBCRegs.zip from: http://support.crystaldecisions.com/updates.
  2. Replace the current C:\WINNT\SYSTEM32\P2SODBC.DLL file with the file contained in ODBCRegs.zip.
  3. Run OUTJOIN.REG from the zip file.
  4. Run regedt32 or regedit from a DOS prompt.
  5. Select HKEY_CURRENT_USER -> SOFTWARE -> SEAGATE SOFTWARE -> CRYSTAL REPORTS -> DatabaseOptions -> OuterJoin.
    • Note -- the exact drilldown varies with different versions of Crystal Reports.
  6. Double-click the "PlusEqual" key.
  7. At the end of the string, add the DLL name of the OpenLink driver you are using. E.g., for Release 4.x Multi-Tier, you would add: , olod4032
  8. Exit the registry editor and test.

To get around this issue entirely, you can change the syntax of the generated query manually. For example, if you were joining the customer, order, and order-line tables, you could change the Crystal Reports query to the following, where no JOIN syntax is used:

SELECT
    ol.Price, ol.Qty,
    o.Odate, o.Pdate, o.Shipped,
    c.Cust-num, c.Name, c.Address, c.City
FROM
    pro1.order-line ol,
    pro1.order o,
    pro1.customer c
WHERE
    ol.Order-num = o.Order-num
AND
    o.Cust-num = c.Cust-num

Spaced aliasing

If you are using Crystal Reports 9, a problem was introduced in this version which may produce the above error message. Queries using spaced aliasing will cause this problem, for example -- 

SELECT  customer . Name  FROM    DYN . Customer   customer


Our Release 5.x and later drivers have a built-in workaround for this issue.

Evidence

  • ODBC Client Trace
  • DBMS Version
Referenced by...