CREATE VIEW w/ UNION

I have the following query that works fine when run manually, but I don't
know how to create a view of the equivalent data. When I uncomment the
CREATE VIEW it errors out.
--CREATE VIEW vwCPA
--AS
DECLARE @.TypeA nchar(10)
DECLARE @.TypeP nchar(10)
DECLARE @.TypeC nchar(10)
SET @.TypeA='Adjustment'
SET @.TypeP='Payment'
SET @.TypeC='Charge'
SELECT @.TypeA AS Type, *
FROM vwAdjustmentDetail2
UNION
SELECT @.TypeP AS Type, *
FROM vwPaymentDetail2
UNION
SELECT @.TypeC AS Type, *
FROM vwCharges2
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.sessioncomputing.comPatrick Rouse wrote:

> I have the following query that works fine when run manually, but I
> don't know how to create a view of the equivalent data. When I
> uncomment the CREATE VIEW it errors out.
> --CREATE VIEW vwCPA
> --AS
> DECLARE @.TypeA nchar(10)
> DECLARE @.TypeP nchar(10)
> DECLARE @.TypeC nchar(10)
> SET @.TypeA='Adjustment'
> SET @.TypeP='Payment'
> SET @.TypeC='Charge'
You can't use variables in views. Create a stored procedure instead.
HTH,
Stijn Verrept.|||"Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
news:5456E06E-4869-46CD-A177-C9A1B2580CFE@.microsoft.com...
>I have the following query that works fine when run manually, but I don't
> know how to create a view of the equivalent data. When I uncomment the
> CREATE VIEW it errors out.
> --CREATE VIEW vwCPA
> --AS
> DECLARE @.TypeA nchar(10)
> DECLARE @.TypeP nchar(10)
> DECLARE @.TypeC nchar(10)
> SET @.TypeA='Adjustment'
> SET @.TypeP='Payment'
> SET @.TypeC='Charge'
> SELECT @.TypeA AS Type, *
> FROM vwAdjustmentDetail2
> UNION
> SELECT @.TypeP AS Type, *
> FROM vwPaymentDetail2
> UNION
> SELECT @.TypeC AS Type, *
> FROM vwCharges2
>
Include the literal values in the queries, perhaps with an explicit cast.
And assuming that none of the individual queries returns duplicate rows, use
UNION ALL since your Type column guarantees that no row will be duplicated
between queries.
EG
CREATE VIEW vwCPA
AS
SELECT cast(N'Adjustment' as nchar(10)) Type, *
FROM vwAdjustmentDetail2
UNION ALL
SELECT cast(N'Payment' as nchar(10)) Type, *
FROM vwPaymentDetail2
UNION ALL
SELECT cast(N'Charge' as nchar(10)) Type, *
FROM vwCharges2
David|||Thanks David, that's exactly what I was looking for.
--
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.sessioncomputing.com
"David Browne" wrote:

> "Patrick Rouse" <PatrickRouse@.discussions.microsoft.com> wrote in message
> news:5456E06E-4869-46CD-A177-C9A1B2580CFE@.microsoft.com...
> Include the literal values in the queries, perhaps with an explicit cast.
> And assuming that none of the individual queries returns duplicate rows, u
se
> UNION ALL since your Type column guarantees that no row will be duplicated
> between queries.
> EG
> CREATE VIEW vwCPA
> AS
> SELECT cast(N'Adjustment' as nchar(10)) Type, *
> FROM vwAdjustmentDetail2
> UNION ALL
> SELECT cast(N'Payment' as nchar(10)) Type, *
> FROM vwPaymentDetail2
> UNION ALL
> SELECT cast(N'Charge' as nchar(10)) Type, *
> FROM vwCharges2
>
> David
>
>