Skip to Content
0
Former Member
Jun 11, 2009 at 10:15 AM

Error duplicate name for tables on SQL in Derived Tables

54 Views

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