Duplicate column names in Universe

building semantic layer
1

Hello,

We are querying a datasource that does not allow having two columns with the same name in the Select clause.

Here is the Select generated (reduced to the minimum) :

Select
Customer.Name,
Table__1.Name
from 
Customer,
Customer Table__1,
where
Customer.Spouse_ID = Table__1.ID.

Is there a way to have an automatic column aliasing, like BO is doing for tables sometimes (Aliasing as “Table__1” for instance) ?

Thank you !

sdut :iceland: (BOB member since 2017-09-21)

2

In this query there are two different tables Customer and table_1. So there will be no problem with same column names in two different tables. As you are not having two column names same in one table

surya_sudheer :india: (BOB member since 2006-11-01)

3

You didn’t read what the OP wrote:

I can’t think of a way to do automatic column aliasing…I guess you could manually update affected objects so the definition is Customer.Name “Customer_Name” (depending on what database this is)

Nick Daniels :uk: (BOB member since 2002-08-15)

4

It’s hack-ish, but you could create a derived table instead of an alias:

select
   id,
   name as spouse_name
from
    customer

Curious - which datasource are you using?

joepeters :us: (BOB member since 2002-08-29)

5

I can’t remember where I read it but I’m sure there was a setting which made Bobj append table aliases for queries. I was getting annoyed when the SQL generated had lots of unnecessary aliases and I’m sure I found a way to stop it doing that.

But I can’t remember how. Not much use really. I’ll think on it …

Debbie

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

6

Strategies maybe?

Nick Daniels :uk: (BOB member since 2002-08-15)

7

Thanks, we’ve started with this workaround for now but it’s a lot of work (we’re switching a few universes to this new DB system from Oracle).

The datasource is Denodo VirtualDataPort, not supported by BO I know…

That’s the thing, I’m almost sure I’ve seen it too somewhere :frowning:

sdut :iceland: (BOB member since 2017-09-21)

8

Hmm.

Well, it’s really hackish, but what if you put the alias name in the object definition? That is, “table__1.name as spouse_name”. Most databases will complain about an alias in GROUP BY but maybe Denodo doesn’t.

joepeters :us: (BOB member since 2002-08-29)

9

Well… it doesn’t support aliases in group by clauses, but it supports group by with column index(group by 1, 2, 3) and there is a parameter for that in the prm file (YES)

Gave it a try, it seems to work :

  • Added an “as xxxx” in every select definition that exists more than once
  • enabled the group by column index

So far so good…


Select 
Customer.Name, 
Table__1.Name as Spouse,
count(*) as Dummy_count
from 
Customer, 
Customer Table__1, 
where 
Customer.Spouse_ID = Table__1.ID
group by
1,
2

Problem solved then, thank you !

sdut :iceland: (BOB member since 2017-09-21)

10

Well, how about that! Glad it worked.

joepeters :us: (BOB member since 2002-08-29)

11

Can you please give me the steps to configure Denodo JDBC Driver to connect from BO ?

I have the jar file but looking for configuration details/Files.

Ravichandrak (BOB member since 2011-11-30)

12

I"m also interested in connecting to Denodo. I found the article below.

Anyone got it to work with an ODBC connection?

https://community.denodo.com/kb/view/document/How%20to%20access%20VDP%20from%20SAP%20BusinessObjects?category=Northbound+Connections

bension (BOB member since 2005-09-01)