asked on

SQL Server Database Error

I am trying to recreate a view a vendor built in a test db. Here is the create view script:

CREATE VIEW [faster].[VW_DPN_DEOWNERSHIP] WITH
SCHEMABINDING
AS
SELECT EXUID,
EHKey,
EXOpID,
EXEHUID,
EXDeptTo EXDeptFrom,
EHDateCreated,
EXEffectiveDate BeginDate,
NULL EndDate
FROM faster.EXTransfer, faster.EHeader
WHERE EXEHUID = EHUID
AND EXDeptFrom = '---NONE---'
AND EXEHUID NOT IN (SELECT DISTINCT EXEHUID
FROM faster.EXTransfer
WHERE EXDeptFrom <> '---NONE---')
--Get Transfers in date order for Eq with more than one transfer that is not the base record transfer
UNION ALL
SELECT EX1.EXUID,
EHeader.EHKey,
EX1.EXOpID,
EX1.EXEHUID,
EX1.EXDeptTo,
EHeader.EHDateCreated FirstDate,
EX1.EXEffectiveDate BeginDate,
EX2.EXEffectiveDate EndDate
FROM (SELECT DISTINCT EXUID,
EXOpID,
EXEHUID,
EXDeptFrom,
EXDeptTo,
EXEffectiveDate
FROM faster.EXTRANSFER, faster.EHeader
WHERE EXDeptFrom <> '---NONE---') EX1
JOIN (SELECT DISTINCT EXUID,
EXOpID,
EXEHUID,
EXDeptFrom,
EXDeptTo,
EXEffectiveDate
FROM faster.EXTRANSFER
WHERE EXDeptFrom <> '---NONE---') EX2
ON EX2.EXDeptFrom = EX1.EXDeptTo
AND EX1.EXEHUID = EX2.EXEHUID
AND EX2.EXEffectiveDate >= EX1.EXEffectiveDate
AND EX2.EXDeptFrom <> '---NONE---'
JOIN faster.EHeader
ON EX1.EXEHUID = EHUID
UNION ALL
--Gets all transfers that are to the current DPN (EndDate is today
SELECT EXUID,
EHKey,
EXOpID,
EXEHUID,
EXDeptTo,
EHDateCreated,
EXEffectiveDate BeginDate,
NULL EndDate
FROM faster.EXTransfer, faster.EHEader
WHERE EXEHUID = EHUID --is the current department transfer record
AND EXDeptTo = EHDept
AND EXEffectiveDate =
(SELECT MAX (ex.EXEffectiveDate)
FROM faster.EXTransfer ex
WHERE ex.EXDeptTo = EHDept AND ex.EXEHUID = EHUID)
GO

Here is the error I get when I try to create it:

SQL Server Database Error: The column prefix 'EHeader' does not match with a table name or alias name used in the query.

I added it to the FROM portion and that didn't fix it, is there something else I am missing?

knightEknight

You only added it to the first query - you will need to add it to every query in this UNION of queries.

martyuni

ASKER

Sorry, that is the original, I did a find and with all the from statements I added it to them. Still didn't work.

ASKER CERTIFIED SOLUTION

Imran Javed Zia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sventhan

< CREATE VIEW [faster].[VW_DPN_DEOWNERSHIP] WITH
SCHEMABINDING

You trying to create an "INDEXED VIEW" and "UNION/UNION ALL" is not allowed. You simply cannot recreate this view with UNION ALL

its worth a reading
http://msdn.microsoft.com/en-us/library/dd171921(v=sql.100).aspx

martyuni

ASKER

Thank you so much, that worked.

Imran Javed Zia

You are most Welcome