Invalid SET statement in the triggered action

building semantic layer
1

Hi,

I am getting the following error while parsing the SQL query (in SQL Viewer in property tab) for dimension object. But when parsing in Definition tab, it is saying “Parsing OK”.

The error is…
" Exception: DBD, [NCR][ODBC Teradata Driver][Teradata Database] Invalid SET statement in the triggered action. State: HY000 "

The universe connection is working fine and i can view the table values also.

Am using BOBJ XI3 designer and Teradata database. Please help me to avoid this error.

Advance thanks,
GPS

GPS (BOB member since 2006-06-20)

2

What is the select statement that you are parsing?

3

Hi,

Even if i use any kind of sql qery, it is giving the error. for example, if i use " select ‘a’ " or " select dept_name from dept ".

GPS (BOB member since 2006-06-20)

4

You don’t need to put “select dept_name from dept” in the select statement.

Just put dept.dept_name in the select box.

5

Hi,

You are asking me to put in Definition tab, yes i did it already.

I am facing the error while parsing the query in query panel where we edit the query(through properties tab and click Edit option near to Associate List of values part).

The sql query will be there below the line
SET QUERY_TYPE = ‘USER = ‘@Variable(‘BOUSER’)’; DOCNAME = ‘@Variable(‘DNAME’)’; DPNAME = ‘@Variable(‘DPNAME’)’; DPTYPE = ‘@Variable(‘DPTYPE’)’; UNIVERSE = ‘@Variable(‘UNVNAME’)’; UNIVERSEID = ‘@Variable(‘UNVID’)’;’ FOR TRANSACTION;

GPS (BOB member since 2006-06-20)

6

Ah, with you. You can’t do that. It’s a simple select statement to return values. It is expecting a select statement, not a SET argument.

7

Recently we enabled query banding in our universe parameters and since then the LOV in the object properties returns an error.

Exception: DBD, [Teradata][ODBC Teradata Driver][Teradata Database] Invalid SET statement in the triggered action. State: HY000

Anyway to workaround this? The DBA’s are requesting that we use the query band feature to track poor performing queries.

So in the BEGIN_SQL we have it set to the following =
SET QUERY_BAND=‘UserID=’@Variable(‘DBUSER’)’; Universe=’@Variable(‘UNVNAME’)’;Document=’@Variable(‘DOCNAME’)’;WFAppID=XXX;’ for transaction;

I can understand why this would return an error but SAP/BO documentation recommends this. So wondering anyone else running into this and if they found a workaround.

Thanks,

ppmysore (BOB member since 2007-02-16)

8

Wanted to add - Error only in Designer when trying to Display LOV. Works fine via Webi.

Thanks,

ppmysore (BOB member since 2007-02-16)

9

Even i faced the same issue after setting the query banding.
So removed the query banding, when ever i want see the LOV in unv level with out saving the unv.

hari4bo (BOB member since 2005-10-10)

10

LOL! Thanks! I was doing the same. Not fun when you have to do this to check each time.

ppmysore (BOB member since 2007-02-16)