Return all fields with the same name with function

Following sample function. This one works in query analyzer, as well as
works as stored procedure. Because of the nature of the program I need it to
be function and return all fields from all tables, BUT this one do not want
to compile because it has fileds with the same names in different tables.
How to fix it?
ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
RETURNS TABLE
AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
column names on different tables to be displayed
FROM Table1 INNER JOIN (((Table5 AS uu
INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
Table4.Filed5
WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/Tamir,
Do not use "*", instead specify all column names and if two column have the
same name then you have to use an alias.
SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
AMB
"Tamir Khason" wrote:

> Following sample function. This one works in query analyzer, as well as
> works as stored procedure. Because of the nature of the program I need it
to
> be function and return all fields from all tables, BUT this one do not wan
t
> to compile because it has fileds with the same names in different tables.
> How to fix it?
> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
> RETURNS TABLE
> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
> column names on different tables to be displayed
> FROM Table1 INNER JOIN (((Table5 AS uu
> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
> Table4.Filed5
> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>
> --
> Tamir Khason
> "The computer is no better
> than its program." [Elting Elmore Morison]
> http://www.dotnet.us/
>
>|||Thank you for response, BUT there are 10 tables with about 100 columns each
one. It's seemed me rather supid to specified 1000 columns for such
function.
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B37C0B0D-4876-4CB1-A80A-6F9A15972FB4@.microsoft.com...[vbcol=seagreen]
> Tamir,
> Do not use "*", instead specify all column names and if two column have
> the
> same name then you have to use an alias.
> SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
>
> AMB
> "Tamir Khason" wrote:
>|||Tamir Khason wrote:
> Thank you for response, BUT there are 10 tables with about 100
> columns each one. It's seemed me rather supid to specified 1000
> columns for such function.
>
I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at
best obfuscates what columns really need to be in the query and at worst
breaks compilation. For example, had there been no overlap in the
columns, your function would have compiled. If you then added a column
to one of the tables that caused an overlap, the next time the function
was altered, it would fail to compile. It would also not pick up the
column change even in a case of no overlap.
Also, most queries do not require all column values be returned.
Especially when there is more than one table and joins on common columns
are involved. It just adds SQL Server overhead.
To help script out long lists of columns, you can use QA and copy a
SELECT statement for a table to the clipboard.
David Gugick
Imceda Software
www.imceda.com|||That's fine, but in this case I need all columns (this is for DWH
applicaiton) so I'm still in trouble with large number of columns in table
the function returns
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OlbcdLBTFHA.3040@.TK2MSFTNGP10.phx.gbl...
> Tamir Khason wrote:
> I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at best
> obfuscates what columns really need to be in the query and at worst breaks
> compilation. For example, had there been no overlap in the columns, your
> function would have compiled. If you then added a column to one of the
> tables that caused an overlap, the next time the function was altered, it
> would fail to compile. It would also not pick up the column change even in
> a case of no overlap.
> Also, most queries do not require all column values be returned.
> Especially when there is more than one table and joins on common columns
> are involved. It just adds SQL Server overhead.
> To help script out long lists of columns, you can use QA and copy a SELECT
> statement for a table to the clipboard.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com