CODEWITHSUNDEEP
sqlsql-serveralias
Zusee Weekin
Zusee Weekin

Reputation: 1358

SQL alias not identify

Please help me to correct the query. Here the alias "startDate" not identify. 

SELECT TOP 1 a.[ID]                         
    ,b.[Name]
    ,a.[VName]
    ,a.[StartKM]
    ,a.[RecurringKM]
    ,dateadd (day,-(120000 - a.[StartKM])/80,GETDATE())as startDate
    ,dateadd(day,(a.[RecurringKM]/80),[startDate])as [DueDate]
FROM MaintenanceEventInstance AS a
INNER JOIN MaintenanceEventDescriptor AS b ON a.ID=b.ID
WHERE a.VName='test' AND a.CompletedDate  IS NULL AND 
    a.ReType='4' 
order by [DueDate] asc

Any help would be appreciated.

Upvotes: 2

Views: 57

Answers (2)

Jatin Patel
Jatin Patel

Reputation: 2104

You can not use column alias in same select, instead add the expression there,

SELECT TOP 1 a.[ID]
            ,b.[Name]
            ,a.[VName]
            ,a.[StartKM]
            ,a.[RecurringKM]
            ,dateadd (day,-(120000 - a.[StartKM])/80,GETDATE())as startDate
            ,dateadd(day,(a.[RecurringKM]/80),dateadd (day,-(120000 - a.[StartKM])/80,GETDATE()))as [DueDate]
FROM MaintenanceEventInstance AS a
INNER JOIN MaintenanceEventDescriptor AS b ON a.ID=b.ID
WHERE a.VName='test' AND a.CompletedDate  IS NULL AND 
    a.ReType='4' order by [DueDate] as

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You cannot use the alias in the SELECT clause. Try this instead:

SELECT TOP 1 a.[ID]
            ,b.[Name]
            ,a.[VName]
            ,a.[StartKM]
            ,a.[RecurringKM]
            ,x.startDate
            ,dateadd(day,(a.[RecurringKM]/80),x.[startDate])as [DueDate]
FROM MaintenanceEventInstance AS a
INNER JOIN MaintenanceEventDescriptor AS b ON a.ID=b.ID
CROSS APPLY (SELECT dateadd (day,-(120000 - a.[StartKM])/80,GETDATE())) AS x(startDate)
WHERE a.VName='test' AND a.CompletedDate  IS NULL AND 
      a.ReType='4' order by [DueDate] asc

Upvotes: 2

Related Questions