Using alias within a calculation 1

[NotSQL]

Hi guys,

Im trying to use a alias with in a query but don’t seem to be getting the syntax correct.

I need to find the due date then use it to determine if its late or early.

Example

select z.DueDate, c.Name, case when z.DueDate < getdate() then 'Future' else 'ODue' end as 'ODUE'
from (Select CASE WHEN c.AckDate IS NULL
THEN c.RequiredDate ELSE c.AckDate END AS DueDate
From PURCHASE_ORDER_DETAIL c)z

Any help would be grateful
Regards

 

[bborissov]

And where is your [c] alias in main query?
There is no one
You can't use aliases used in derived tables
BTW you don't need that derived table anyway

select DueDate, 
       Name,
       case when ISNULL(AckDate, RequiredDate) < getdate() 
                 THEN 'Future'
            else 'ODue' end as 'ODUE'
From PURCHASE_ORDER_DETAIL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PWise]

try

select z.DueDate,  z.Name, case when z.DueDate < getdate() then 'Future' else 'ODue' end as 'ODUE'
from (Select [name], isnull(AckDate,RequiredDate) 
From  PURCHASE_ORDER_DETAIL )z

 

[NotSQL]

Thanks

but my duedate is base on:

when AckDate IS NULL i need the RequiredDate ELSE its the AckDate.

How is the Duedate calculated in your sql?

 

[bborissov]

Check what ISNULL() function returns in BOL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[NotSQL]

Thanks bborissov,

I still need to calculate duedate though dont i? so would this be ok?

select ISNULL(AckDate, RequiredDate) as DueDate,
Name,
case when ISNULL(AckDate, RequiredDate) < getdate()
THEN 'Future'
else 'ODue' end as 'ODUE'
From PURCHASE_ORDER_DETAIL

 

[bborissov]

Sure.
I will be OK.
I missed the part you need duedate in your main query.



Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.