SQL Server Invalid column - ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator

goucqfw6 于 2023-08-02 发布在其他

关注(0)|答案(2)|浏览(117)

I am trying to add column headers while extracting the table data, but my query throws the following error:
Invalid column name 'JobTaskID'.
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

This is my query

SELECT 
    'JobTaskID', 'Geo', 'Environment', 'JobName', 'JobStarttime', 'JobEndtime',
    'JobProcessingTime', 'JobTaskname', 'TaskStarttime', 'TaskEndtime', 
    'TaskProcessingTime', 'TotalActiveInventory', 'NewInventoryAdded',
    'TotalActiveSKUs', 'TotalActiveStores', 'TotalCalculatedAllocations',
    'TotalActiveAllocations'  

UNION ALL        

SELECT 
    CAST([JobTaskID] AS NVARCHAR(MAX)), 
    CAST([Geo] AS NVARCHAR(MAX)), 
    CAST([Environment] AS NVARCHAR(MAX)), 
    CAST([JobName] AS NVARCHAR(MAX)), 
    CAST([JobStarttime] AS NVARCHAR(MAX)), 
    CAST([JobEndtime] AS NVARCHAR(MAX)), 
    CAST([JobProcessingTime] AS NVARCHAR(MAX)), 
    CAST([JobTaskname] AS NVARCHAR(MAX)), 
    CAST([TaskStarttime] AS NVARCHAR(MAX)), 
    CAST([TaskEndtime] AS NVARCHAR(MAX)),
    CAST([TaskProcessingTime] AS NVARCHAR(MAX)), 
    CAST([TotalActiveInventory] AS NVARCHAR(MAX)), 
    CAST([NewInventoryAdded] AS NVARCHAR(MAX)), 
    CAST([TotalActiveSKUs] AS NVARCHAR(MAX)), 
    CAST([TotalActiveStores] AS NVARCHAR(MAX)),   
    CAST([TotalCalculatedAllocations] AS NVARCHAR(MAX)),  
    CAST([TotalActiveAllocations] AS NVARCHAR(MAX))       
FROM 
    ctbl_jobReport 
ORDER BY 
    JobTaskID

The table ctbl_jobreport does contain the JobTaskID column:

CREATE TABLE [dbo].[ctbl_jobReport]
(
    [JobTaskID] [int] NOT NULL IDENTITY(1, 1),
    [Geo] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL,
    [Environment] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL,
    [JobName] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL,
    [JobStarttime] [datetime] NULL,
    [JobEndtime] [datetime] NULL,
    [JobProcessingTime] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL,
    [JobTaskname] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL,
    [TaskStarttime] [datetime] NULL,
    [TaskEndtime] [datetime] NULL,
    [TaskProcessingTime] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL,
    [TotalActiveInventory] [int] NULL,
    [NewInventoryAdded] [int] NULL,
    [TotalActiveSKUs] [int] NULL,
    [TotalActiveStores] [int] NULL,
    [TotalCalculatedAllocations] [int] NULL,
    [TotalActiveAllocations] [int] NULL
) ON [PRIMARY]

When I run the two select statements individually, I do not see that error

sql-server

来源：https://stackoverflow.com/questions/76582563/invalid-column-order-by-items-must-appear-in-the-select-list-if-the-statement

2条答案

按热度按时间

sg24os4d1#

Putting aside why you would want to do something like this (which is almost certainly trying to solve a different problem with the wrong kind of solution), you can achieve it by correctly aliasing the fields (as you're using a UNION ) so they can be sorted on, and then adding an additional static field to force the "column headers" to be returned first, before the actual data:

CREATE TABLE #ctbl_jobReport
(
    [JobTaskID] [int] NOT NULL IDENTITY(1, 1),
    [Geo] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL,
    [Environment] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL,
    [JobName] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL,
    [JobStarttime] [datetime] NULL,
    [JobEndtime] [datetime] NULL,
    [JobProcessingTime] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL,
    [JobTaskname] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL,
    [TaskStarttime] [datetime] NULL,
    [TaskEndtime] [datetime] NULL,
    [TaskProcessingTime] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL,
    [TotalActiveInventory] [int] NULL,
    [NewInventoryAdded] [int] NULL,
    [TotalActiveSKUs] [int] NULL,
    [TotalActiveStores] [int] NULL,
    [TotalCalculatedAllocations] [int] NULL,
    [TotalActiveAllocations] [int] NULL
)

insert into #ctbl_jobReport values
('GeoValue','EnvValue','JobNameValue','2023-01-01','2023-06-01','JobProcTimeValue','JobTasknameValue','2023-01-02','2023-05-31','TaskProcTimeValue',1,2,3,4,5,6),
('GeoValue2','EnvValue2','JobNameValue2','2023-01-01','2023-06-01','JobProcTimeValue2','JobTasknameValue2','2023-01-02','2023-05-31','TaskProcTimeValue2',1,2,3,4,5,6),
('GeoValue3','EnvValue3','JobNameValue3','2023-01-01','2023-06-01','JobProcTimeValue3','JobTasknameValue3','2023-01-02','2023-05-31','TaskProcTimeValue3',1,2,3,4,5,6),
('GeoValue4','EnvValue4','JobNameValue4','2023-01-01','2023-06-01','JobProcTimeValue4','JobTasknameValue4','2023-01-02','2023-05-31','TaskProcTimeValue4',1,2,3,4,5,6)

SELECT 
    1 as orderRef,
    'JobTaskID' as JobTaskID,
    'Geo' as Geo, 
    'Environment' as Environment, 
    'JobName' as JobName, 
    'JobStarttime' as JobStarttime, 
    'JobEndtime' as JobEndtime,
    'JobProcessingTime' as JobProcessingTime, 
    'JobTaskname' as JobTaskname, 
    'TaskStarttime' as TaskStarttime, 
    'TaskEndtime' as TaskEndtime, 
    'TaskProcessingTime' as TaskProcessingTime, 
    'TotalActiveInventory' as TotalActiveInventory, 
    'NewInventoryAdded' as NewInventoryAdded,
    'TotalActiveSKUs' as TotalActiveSKUs, 
    'TotalActiveStores' as TotalActiveStores, 
    'TotalCalculatedAllocations' as TotalCalculatedAllocations,
    'TotalActiveAllocations' as TotalActiveAllocations  

UNION ALL        

SELECT 
    2,
    CAST([JobTaskID] AS NVARCHAR(MAX)), 
    CAST([Geo] AS NVARCHAR(MAX)), 
    CAST([Environment] AS NVARCHAR(MAX)), 
    CAST([JobName] AS NVARCHAR(MAX)), 
    CAST([JobStarttime] AS NVARCHAR(MAX)), 
    CAST([JobEndtime] AS NVARCHAR(MAX)), 
    CAST([JobProcessingTime] AS NVARCHAR(MAX)), 
    CAST([JobTaskname] AS NVARCHAR(MAX)), 
    CAST([TaskStarttime] AS NVARCHAR(MAX)), 
    CAST([TaskEndtime] AS NVARCHAR(MAX)),
    CAST([TaskProcessingTime] AS NVARCHAR(MAX)), 
    CAST([TotalActiveInventory] AS NVARCHAR(MAX)), 
    CAST([NewInventoryAdded] AS NVARCHAR(MAX)), 
    CAST([TotalActiveSKUs] AS NVARCHAR(MAX)), 
    CAST([TotalActiveStores] AS NVARCHAR(MAX)),   
    CAST([TotalCalculatedAllocations] AS NVARCHAR(MAX)),  
    CAST([TotalActiveAllocations] AS NVARCHAR(MAX))       
FROM 
    #ctbl_jobReport 
ORDER BY 
    orderRef,
    JobTaskID

drop table #ctbl_jobReport

赞(0）回复(0）举报 2023-08-02

kuhbmx9i2#

Create a db.view then apply the separate query Select * from db.view order by JobTaskID

赞(0）回复(0）举报 2023-08-02