SWQL Query Unnamed Columns Problem

SWQL does not support referencing an alias in the same SELECT block if that alias is defined by a subquery, expression, or function. 

In your original version, scalar subqueries are used in the SELECT clause and aliased as RunningBackupTime, StartupBackupTime, etc. These aliases cannot be reused in CASE statements or ORDER BY clauses within the same SELECT block. 

I also prefer JOIN + MAX aggregation for performance, readability, and compatibility in SWQL. This avoids aliasing issues and runs faster on large datasets.

SELECT
  [OrionNodes].[Caption] AS [Caption],
  [OrionNodes].[DetailsUrl] AS [DetailsUrl],
  [OrionNodes].[Status] AS [Status],
  [OrionNodes].[Vendor] AS [Vendor],
  [OrionVendors].[Icon] AS [Icon],
  [OrionNodes].[IP_Address] AS [IP_Address],
  [OrionNodes].[MachineType] AS [MachineType],
  -- [OrionNodes].[CustomProperties].[MUSTERI_ADI] AS [MUSTERI_ADI],
  [NcmNodeProperties].[NodeID] AS [NodeID],

  [Running].[DownloadTime] AS [RunningBackupTime],
  [Startup].[DownloadTime] AS [StartupBackupTime],
  [Running].[ConfigID] AS [RunningConfigID],
  [Startup].[ConfigID] AS [StartupConfigID],
  tostring([Running].[AttemptedDownloadTime]) AS [running_status_detail],
  tostring([Startup].[AttemptedDownloadTime]) AS [backup_status_detail],

  CASE
    WHEN [Running].[DownloadTime] IS NULL AND [Startup].[DownloadTime] IS NULL THEN 0
    WHEN [Running].[DownloadTime] IS NULL THEN 1
    WHEN [Startup].[DownloadTime] IS NULL THEN 2
    ELSE 3
  END AS [SortCategory]

FROM [NCM].[NodeProperties] AS [NcmNodeProperties]

INNER JOIN [Orion].[Nodes] AS [OrionNodes]
  ON [NcmNodeProperties].[CoreNodeID] = [OrionNodes].[NodeID]

INNER JOIN [Orion].[Vendors] AS [OrionVendors]
  ON [OrionNodes].[Vendor] = [OrionVendors].[Name]

-- Optimized LEFT JOIN to get latest Running config per node
LEFT JOIN (
  SELECT [NodeID],
         MAX([DownloadTime]) AS [DownloadTime],
         MAX([ConfigID]) AS [ConfigID],
         MAX([AttemptedDownloadTime]) AS [AttemptedDownloadTime]
  FROM [NCM].[ConfigArchive]
  WHERE [ConfigType] = 'Running'
  GROUP BY [NodeID]
) AS [Running]
  ON [Running].[NodeID] = [NcmNodeProperties].[NodeID]

-- Optimized LEFT JOIN to get latest Startup config per node
LEFT JOIN (
  SELECT [NodeID],
         MAX([DownloadTime]) AS [DownloadTime],
         MAX([ConfigID]) AS [ConfigID],
         MAX([AttemptedDownloadTime]) AS [AttemptedDownloadTime]
  FROM [NCM].[ConfigArchive]
  WHERE [ConfigType] = 'Startup'
  GROUP BY [NodeID]
) AS [Startup]
  ON [Startup].[NodeID] = [NcmNodeProperties].[NodeID]

WHERE [OrionNodes].[Status] != 9

ORDER BY
  [SortCategory],
  [Running].[DownloadTime] ASC

I have noticed that Aliases do not work in the where clauses either. Looks like it has to do with the SQL order of operations that SWQL lives under.  The Where statements are processed before the Select statements (and aliases). This most likely applies to group by and having as they are executed prior to Select statements also. 

I have noticed that Aliases do not work in the where clauses either. Looks like it has to do with the SQL order of operations that SWQL lives under.  The Where statements are processed before the Select statements (and aliases). This most likely applies to group by and having as they are executed prior to Select statements also.