Table name ending with #

system · September 9, 2009, 4:49pm

Hi All,

I have some different scenario in one of my WebI reporting team design.

Actually they are designing the Universe with database connection “Fread” as user id accessing database. And also they are using some tables from “STG” schema.

When ever we use table from STG, the table display is like this STG.Table1. It is common.
The problem here is, STG schema has some tables ending with #. So when ever we insert those # tables, they look like “STG.Table2#”. BO is putting double quotes around that table.

Objects are parsing okay on universe side. But when it comes to WebI, it is giving “Fread.STG.Table2#” is an unidentified name. When i remove double quotes"STG.Table2#" using custom SQL in WebI, it is working fine. But end users are using this universe for adhoc reports, they cant edit the SQL.

There is any solution on Universe side?

Thanks in advance.

MightyBO (BOB member since 2007-06-28)

system · September 9, 2009, 4:57pm

Hi,

Will renaming of the tables in the universe work? Right click a table, select Rename Table option and remove double quotes from the table name.

Marek Chladny (BOB member since 2003-11-27)

system · September 9, 2009, 5:51pm

Hi,

I tried, but it is not showing double quotes in table name: box. It just showing Table2# and owner as STG.

Note: I cant remove both # and STG, because objects will give error like unidentified table.

MightyBO (BOB member since 2007-06-28)

system · September 9, 2009, 5:57pm

Try just removing the hash symbol, by renaming the table and repoint your objects.

Whoever came up with the naming convention neds shooting IMO… .

Mak 1 (BOB member since 2005-01-06)

system · September 9, 2009, 6:11pm

Hi Mak,

It’s not working.

The # is coming from database side, So when i remove hash objects are not parsing.

MightyBO (BOB member since 2007-06-28)

system · September 9, 2009, 6:14pm

to the amended table name…you can use find and replace for this.

Try amending the Select on one objects and see that it works…

Mak 1 (BOB member since 2005-01-06)

system · September 9, 2009, 6:17pm

We don’t need to re-point the objects.

When ever we rename the table name using rename table, the corresponding objects will be directly referred to the new table name.

MightyBO (BOB member since 2007-06-28)

system · September 9, 2009, 6:20pm

So, do your objects work or not… :? ?

Mak 1 (BOB member since 2005-01-06)

system · September 9, 2009, 6:23pm

No. When i remove hash, it is giving error STG.Table2 is Undefined name.

MightyBO (BOB member since 2007-06-28)

system · September 10, 2009, 7:23am

OK, well, what database and connectivity are you using?

You may be able to make changes to the connections PRM file to overcome this. This is stored on your BOXI server.

Really you should get the table names amended at the DB side, IMO.

I suspect that the same query would run in Deski and that it is the special character that is causing problems at the, Java based, Webi side.

One other, not recommended way, due to performance and maintenance overhead, would be to create derived tables to overcome this.

Mak 1 (BOB member since 2005-01-06)

system · September 10, 2009, 2:43pm

And if everything else fails, you could still create database views where for instance the view STG.Table2 would be defined as

select * from STG.Table2#

Marek Chladny (BOB member since 2003-11-27)