SQL stmt shows table numbers rather than table names!!!

building semantic layer
1

My inferred sql statement (in webi) is showing table numbers rather than table names. This makes it harder to recognize what table a query is using. I think somewhere along the way I didnt change the names of the table in universe or contexts which might be causing this. However, all tables in the universe are properly named.

Here’s an example:
WHERE
(Table_16.SALE_ID= Table_17.SALE_ID)
AND (Table_16.SALE_TYPE=‘R’)

anyone know what might be causing this and how could it be resolved.
Thanks.

2

I’ve seen this when using JOIN_BY_SQL. Check your universe parameters. Is the JOIN_BY_SQL parameter set to "Yes?

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

3

I looked under universe parameters…couldn’t find that option there. What tab of parameters are you referring to? Sorry im fairly new to Universe.

4

It would be on the Parameters tab. Scroll down to find that parameter.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

5

For some reason, parameters tab doesnt list JOIN_BY_SQL. It does have the BEGIN_SQL and END_SQL.

6

Another reason you may see Table No. is when you use sub-queries, nested sub-queries or conditions in your report while building it ? Do you have any of the above…?

BO_Chief :us: (BOB member since 2004-06-06)

7

Dont really know what you mean by nested sub-queries, but… I do have classes, sub-classes, measures, conditions, contexts and aliases in my universe.

8

Hi,

I have seen this behavior when a Query contains an Alias Table and its Base Table.

Can you check the query (which displays the Table Numbers) to confirm whether all the tables are different tables or it includes one of the Aliased tables?

Rajat Sapru :us: (BOB member since 2008-08-28)

9

Is this not normal, then? I’ve been using Bob for 9 years from v5 and the generated SQL has always had table numbers rather than names. I have universes over oracle and sql server, with aliases and without. I don’t think I’ve ever seen webi-generated SQL (and deski in the early days) without table numbers!

Debbie

Debbie :uk: (BOB member since 2005-03-01)

10

No, it’s not normal. I’ve seen it, but not often. It’s not a problem, obviously, but makes the generated SQL a little harder to debug.

GreyWolf, if you don’t see JOIN_BY_SQL in the list, then it is not activated. Check your queries in Webi. Do they contain subqueries?

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

11

See my response in this thread, I think the same applies to Aliases.

But basically don’t user lower case or spaces in the alias name as it will automatically get enclosed in quotes and then use table numbers when SQL is generated.

i.e. use NIX_TABLE as opposed to Nix Table or Nix_Table

Nniixx :australia: (BOB member since 2009-09-02)