I tried to create a Universe against the DB2 for POC. I was able to create an object has the statement in the object select panel "current date FROM sysibm.sysdummy1 (I won’t be able to use current date since it gave me the error) and parse all right. However, I tried to use it to do the last 30 days as the statement I found in Google. Assuming this should work - current date FROM sysibm.sysdummy1 - 30 days, but it didn’t. When I ran a query to get that current date, I can’t see the value at all. It gave me the error said the query doesn’t not reference a table. If log in to DB2 use the same user id as the connection string for this Universe, I was able to see the current date by this command: select current date FROM sysibm.sysdummy1. Any one has done something similar with DB2 in the BO? Tanks!
So, are you saying that this object did not return anything when used with objects in the full client that do reference a table, or did it just not parse in Designer…???
This is the error message, even it parse ok in designer -
Exception: DBD, [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token “current date FROM sysibm.sysdummy1 , AMY.D_CPU_M” was found following "SELECT ". Expected tokens may include: “”. SQLSTATE=42601
State: 42601
If you had the time to look at the sql created by ur report you could have found the reason behind the error.
Anyhow the sql generated should be something on the lines like
select current date FROM sysibm.sysdummy1,
a.field1,a.field2
from
table a
Remove the from statement in the object you created for current date, select any table from the tables option so that the object refers to a table and not to throw a parsing error.You should be fine after doing this. let us know.
Create your universe object in the SELECT portion as:
Current Date - 30 days
This object might not parse as in Business Objects terms it does not reference any table in the universe. But it will work in a data provider together with “normal” universe objects that reference a table in the universe. Do not fall into the trap of using a table hint for this object (to make it parse by itself) as this will create more problems than solve, for example: what about contexts or forcing a join to a table via table hint.
If you want to use this in a query that’s all you need on the object. No table, nothing else.
IT WILL NOT PARSE IN DESIGNER
It will work in your queries as a part of the select clause or in the where clause. You will, of course, need objects from another table to make the query run.
(BOB member since 2005-01-06)
(BOB member since 2002-06-20)