troubleshooting Question

SQL Server Database Error

Avatar of martyuni
martyuni asked on
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server
6 Comments1 Solution259 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
Imran Javed Zia
Consultant Software Engineer - .NET Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros