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?