SQL Server adding its own aliases and getting them wrong

90 Views Asked by At

I wrote the following short, simple view:

--DROP VIEW ReportObjects.vStarts;
CREATE VIEW ReportObjects.vStarts AS
SELECT *
FROM ReportObjects.vLotHistory
WHERE
        LotType = 'Some constant value'
    AND CDOName = 'Some constant value'
    AND SpecId = 'Some constant value'

When I execute this script then click "Design" on the view in SSMS, I get this:

SELECT ContainerName, SpecName, ProductName, LotStatus, CDOName, ResourceName AS LotType, EmployeeName AS WorkflowName, LotType AS DieQty, 
                  WorkflowName AS LotQty, DieQty AS TxnDate, LotQty AS TxnDateGMT, TxnDate AS ContainerId, TxnDateGMT AS HistoryMainlineId, ContainerId AS ProductId, 
                  HistoryMainlineId AS SpecId, ProductId AS WorkflowId, SpecId AS WorkflowBaseId, WorkflowId AS WorkflowStepId, WorkflowBaseId, WorkflowStepId, ResourceId, 
                  EmployeeId
FROM     ReportObjects.vLotHistory
WHERE  (LotType = 'Some constant value') AND (CDOName = 'Some constant value') AND (SpecId = 'Some constant value')

See the problem? DieQty AS TxnDate, LotQty AS TxnDateGMT, TxnDate AS ContainerId It's aliasing columnA with the name of columnC!

I've tried dropping/recreating the view several times. I know it can be argued that SELECT * is icky, but that's beside the point (and it is necessary sometimes in production code).

The view above is selecting from another view defined as:

CREATE VIEW ReportObjects.vLotHistory AS
SELECT
    lot.ContainerName,
    hist.SpecName,
    hist.ProductName,
    lot.Status LotStatus,
    hist.CDOName,
    hist.ResourceName,
    hist.EmployeeName,
    lot.csiLotType LotType,
    WorkFlowBase.WorkflowName,
    hist.Qty DieQty,
    hist.Qty2 LotQty,
    hist.TxnDate,
    hist.TxnDateGMT,
    lot.ContainerId,
    hist.HistoryMainlineId,
    hist.ProductId,
    hist.SpecId,
    Workflow.WorkflowId,
    Workflow.WorkflowBaseId,
    hist.WorkflowStepId,
    hist.ResourceId,
    hist.EmployeeId
FROM ODS.MES_Schema.Container lot
    LEFT JOIN ODS.MES_Schema.HistoryMainline hist
        ON lot.ContainerId = hist.ContainerId
    LEFT JOIN ODS.MES_Schema.WorkflowStep WS
        ON hist.WorkflowStepId = WS.WorkflowStepId
    LEFT JOIN ODS.MES_Schema.Workflow
        ON WS.WorkflowId = Workflow.WorkflowId
    LEFT JOIN ODS.MES_Schema.WorkflowBase
        ON WorkflowBase.WorkflowBaseId = Workflow.WorkflowBaseId

Does anyone know why SSMS or SQL Server is eating my query and spitting out nonsense? Note that I am not using SSMS tools to create the view -- I am using CREATE VIEW as shown above.

0

There are 0 best solutions below