sql Help
I am running the query and getting an error message .
The column 'userKey' was specified multiple times for 'T2'.
SELECT *
FROM (
SELECT *
FROM trans T
inner join users U on U.userKey = T.userKey
LEFT OUTER JOIN (
orders O
inner join lineitems LI on LI.orderKey = O.orderKey
)on T.userKey = O.userKey
WHERE O.void = 'N'
AND T.TransDate BETWEEN '07/17/2015'
AND '07/18/2015'
)T2
Agreed. SELECT * from A WOMPLOAD OF TABLES is a poor programming practice, as it means that if those tables have columns of the same name then there will be issues returning that set. Usually this will happen with the JOIN keys.
And if that select is a subquery, it could throw the error.
This would also cause SSRS / SSIS to throw an error as well if it were the source of data.
So...
And if that select is a subquery, it could throw the error.
This would also cause SSRS / SSIS to throw an error as well if it were the source of data.
So...
Do you reeeeeallllyyy need to SELECT * from all tables, or will a subset of columns work?
Guessing if anything you should spell out the columns, intentionally not duplicate columns of the same name but from different tables.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
in addition don't use BETWEEN for date range filters
WHERE Items.void = 'N'
AND ( T.TransDate >= '20150717' AND T.TransDate < '20150718' )
This date filter will give you exactly 1 day of transactions.
See: "Beware of Between"
plus, believe it or not MM/DD/YYYY is not a universally understood date format. The safest possible format for date literals in SQL Server is: YYYYMMDD
WHERE Items.void = 'N'
AND ( T.TransDate >= '20150717' AND T.TransDate < '20150718' )
This date filter will give you exactly 1 day of transactions.
See: "Beware of Between"
Don't know what error you're getting, but try this -
SELECT * FROM
(SELECT * FROM trans T inner join users U on U.userKey = T.userKey
LEFT OUTER JOIN
(SELECT O.userKey, o.void FROM orders O inner join lineitems LI
on LI.orderKey = O.orderKey
where .Void = "N'
) X
ON T.userKey = X.userKey
WHERE T.TransDate BETWEEN '07/17/2015' AND '07/18/2015'
) T2
SELECT * FROM
(SELECT * FROM trans T inner join users U on U.userKey = T.userKey
LEFT OUTER JOIN
(SELECT O.userKey, o.void FROM orders O inner join lineitems LI
on LI.orderKey = O.orderKey
where .Void = "N'
) X
ON T.userKey = X.userKey
WHERE T.TransDate BETWEEN '07/17/2015' AND '07/18/2015'
) T2
There are duplicate column names in the inner SELECT. Usually that is no issue, but if used as an ad-hoc view (inline table, ...), all resulting columns need to be unqiue. Since you provided no aliases, the common columns of both tables are there twice.
You'll have to enumerate the columns you want to get, and make sure none of them are duplicates.