Error duplicate name for tables on SQL in Derived Tables

[Former Member]

Hi, i've created a derived table with this SQL

SELECT DISTINCT admin_key, account_key                                                                       

FROM fact_sales                                                                          

WHERE (fact_sales.data = @Prompt('enter month AAAAMM:','N',,,,,)         

to retrieve the accounts that manage each admin in a prompted month

In the universe i've build a relation between the drived table to the table fact_sales (on account_key). In fact_sales i have the sales for each month and account and admin. I need the sales of each month of the year from January until entered month (propmted) for each admin, but not for the account that each admin have in each month (an account can change from one admin to another) but each month for each admin for the accounts that this admin have in the fixed (prompted) month ...

This SQL on DB runs OK (i show you to explain better the case)

select month, z.admin_key, account_key, sales

from fact_sales

join

(SELECT DISTINCT admin_key, account_key

FROM fact_sales

WHERE (fact_sales.data = @Prompt('enter month AAAAMM:','N',,,,,)) z

on z.account_key = fact_sales.account_key

And to move this to the universe with the derived table (above) and the relation to simulate the previous join when the SQL is created from report ... it give me (at executing query report) this error:

(sorry is in spanish, i imagine the translation like this...)

Error:

Consulta1 - Margeproves

Error en base de datos: las tablas o funciones

fact_SALES y fact_SALES tienen los mismos nombres expuestos. Use nombres de

correlación para distinguirlas. Pongase en contacto con el

administrador de BO o de la DB.

The tables FACT_SALES and FACT_SALES have the same exposed names ?!. Use correlations names to diferenciate?!

Can you help me please ... (sorry for my bad english)

jaume

[former_member142702]

Hi,

create the derived table as follows:

SELECT DISTINCT admin_key, account_key

FROM fact_sales Alias1

WHERE (Alias1.data = @Prompt('enter month AAAAMM:','N',,,,,))

Regards,

Stratos

[Former Member]

Hi,

Statos solution should work, but the issue should not have arisen as the derived table should have its own alias. Can you post the query before running it. To do this edit the report query and click view sql.

Thanks

Alan

[former_member142702]

This may be a driver problem. How do you execute the SQL query in the database in order to try it out and which kind of driver do you use in your universe?

Regards,

Stratos

[Former Member]

Ok, with alias don't give the error. Thanks.

The problem now is that the report create two query (SQL) instead of only one with a join between fact_sales and derived table, then the report give in one query the list of admin and in the other the total of sales, but i can't show together because it shows them like incompatibles...

Can you help me in this ...

[former_member142702]

Hi

do you use contexts in your universe?

Regards,

Stratos