IBM Support

UDA-SQL-0358 when using substring() function in a query subject definition

Troubleshooting


Problem

A syntax error is thrown when editing the SQL of a query subject definition to use the substring function. However, the following syntax works correctly when run directly against the database using a 3rd-party querying tool: substring (fieldName, 1, 5)

Symptom

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 16: Syntax error near ",".

Cause

The Universal Data Access (UDA) layer is expecting Cognos SQL syntax rather than the syntax native to the RDBMS.

Resolving The Problem

The Cognos SQL syntax for substring is:

substring (fieldname from 1 for 5)

Changing the expression to fit this syntax allows the query to run as expected.

An alternative solution is to change the SQL type to Pass-Through, so that the SQL entered is sent straight to the database without being modified or validated by the UDA layer.

Steps:

  1. Open the Query Subject Definition of the Query Subject for which you want to change the SQL type.
  2. Click the Query Information tab.
  3. Click the Options link in the bottom right.
  4. Click the SQL Settings tab.
  5. In the SQL Type section, choose Pass-Through from the drop-down box.

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.2.2;10.2.1;10.2;10.1.1;10.1;8.4.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018

UID

swg21339703