We help IT Professionals succeed at work.

SQL Server Database Error

255 Views
Last Modified: 2012-05-11
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?
Comment
Watch Question

CERTIFIED EXPERT

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

Author

Commented:
Sorry, that is the original, I did a find and with all the from statements I added it to them.  Still didn't work.
Consultant Software Engineer - .NET Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
< 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

Author

Commented:
Thank you so much, that worked.
Imran Javed ZiaConsultant Software Engineer - .NET Architect
CERTIFIED EXPERT

Commented:
You are most Welcome
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.