CREATE VIEW w/ UNION

2 Dec 2005 1:52 AM

Patrick Rouse

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.com

2 Dec 2005 2:13 AM

Stijn Verrept

Patrick 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.

2 Dec 2005 4:18 AM

David Browne

Show quote

"Patrick Rouse" <PatrickRo***@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

2 Dec 2005 4:58 AM

Patrick Rouse

Thanks David, that's exactly what I was looking for.

--
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.sessioncomputing.com

Show quote

"David Browne" wrote:

>
> "Patrick Rouse" <PatrickRo***@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
>
>
>