sql server programming: The syntax of a UNION in SQL says that result of a UNION [ALL] has no
column names. You need to write it as :

SELECT X.*
FROM (SELECT .. FROM Foo WHERE ..
UNION
SELECT .. FROM Bar WHERE ..)
AS X(..)

Then put an AS clause on the entire result, giving it a table name and
column names.

And real SQL programmers follow iSO-11179 rules and do not put crap like
"vw_" prefixes on things, unless they really do relate to a Volkswagen.
They also avoid proprietary code, which means UDFs, SELECT TOP and
getdate().

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

I have a Union Query that works as a View, and is the Source for an MS
Access
ADP Report. However, I need to pass in some variable parameters but
because it
uses "as" it won't work.

Is there any way to delimit/work around the "As" or some way to pass in
the parameters ?

I can use UDF in a View, but can't see how to pass in the values when
calling the View

Some of the SQL Statement follows.

TIA

--
Henry Craven {SBS-MVP}
Melbourne Australia

SELECT TOP 100 PERCENT
dbo.Vw_Format_AllStaff.GivenName + ' ' + dbo.Vw_Format_AllStaff.Surname
AS StaffName,
0 AS ListItems,
0 AS ListComm,
1 AS SellItems,
dbo.vwSellingCommission.SellingConsultantCommission AS SellComm,
dbo.vwSellingCommission.GrossCommission AS GrossComm,
dbo.tblCampaignVoucher.DateSettlement,
dbo.Vw_Format_AllStaff.StaffId,
0 AS ListItemsFY,
0 AS ListCommFY,
0 AS SellItemsFY,
0 AS SellCommFY,
0 AS GrossCommFY,
0 AS ListItemsCY,
0 AS ListCommCY,
0 AS SellItemsCY,
0 AS SellCommCY,
0 AS GrossCommCY
FROM dbo.Vw_Format_AllStaff
INNER JOIN dbo.vwSellingCommission
ON dbo.Vw_Format_AllStaff.StaffId =
dbo.vwSellingCommission.SellingConsultantId
INNER JOIN dbo.tblCampaignVoucher
ON dbo.vwSellingCommission.CampaignId =
dbo.tblCampaignVoucher.CampaignId

WHERE dbo.tblCampaignVoucher.DateSettlement >=

/*
This works...
dbo.fn_1stDayOfMonth(Getdate())

but need to pass in a Variable month
@varInput
dbo.fn_1stDayOfMonth(@varInput)
*/

UNION

SELECT TOP 100 PERCENT
dbo.Vw_Format_AllStaff.GivenName + ' ' + dbo.Vw_Format_AllStaff.Surname
AS StaffName,
1 AS ListItems,
dbo.vwListingCommission.ListingConsultantCommission AS ListComm,
0 AS SellItems,
0 AS SellComm,
dbo.vwListingCommission.GrossCommission AS GrossComm,
dbo.tblCampaignVoucher.DateSettlement,
dbo.Vw_Format_AllStaff.StaffId,
0 AS ListItemsFY,
0 AS ListCommFY,
0 AS SellItemsFY,
0 AS SellCommFY,
0 AS GrossCommFY,
0 AS ListItemsCY,
0 AS ListCommCY,
0 AS SellItemsCY,
0 AS SellCommCY,
0 AS GrossCommCY
FROM dbo.Vw_Format_AllStaff
INNER JOIN dbo.vwListingCommission ON dbo.Vw_Format_AllStaff.StaffId =
dbo.vwListingCommission.ListingConsultantId
INNER JOIN dbo.tblCampaignVoucher ON dbo.vwListingCommission.CampaignId
= dbo.tblCampaignVoucher.CampaignId
WHERE dbo.tblCampaignVoucher.DateSettlement >=

/*
This works...
dbo.fn_1stDayOfMonth(Getdate())

but need to pass in a Variable month
@varInput
dbo.fn_1stDayOfMonth(@varInput)
*/
UNION........etc

[quoted text, click to view]

Can you please give a reference or a quote from the standard document for
this?

I thought Section 7.10, syntax rule 12)b)i) of the SQL-92 standard applied,
and while the version I have with me may not be the final one, it doesn't
say what you say. The specification basically says this (for UNION queries
with no CORRESPONDING clause): if the i-th column has the same name in every
one of the individual tables, then the i-th column of the result has that
name. Otherwise, the i-th column name must differ from the name of any [not
i]-th column of any of the individual tables, but is otherwise
implementation-dependent.


Steve Kass
Drew University

You need to write it as :
[quoted text, click to view]