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.