CODEWITHSUNDEEP
sql-server
FeliceM
FeliceM

Reputation: 4209

SQL query with ISNULL and computed column does not return result

I have this query in Sql 2012:

SELECT Id, Periodo, Due, Paid, Balance, Expenses, Notes, Completato, Data_pag, Ritardo FROM dbo.affitto_sa

Which working fine and returns the data as per top part of the picture:

enter image description here

I am now trying to replace null values in the column Data_pag with the system date(now). I have tried this query which returns the data in the bottom part of the picture:

SELECT Id, Periodo, Due, Paid, Balance, Expenses, Notes, Completato, ISNULL(Data_pag, SYSDATETIME()), Ritardo FROM dbo.affitto_sa

The problem I am experiencing is that the column name "Data_pag" disappears and even if the null value is correctly replaced, the value in column "Ritardo" is not calculated. This column "Ritado" has the following computed formula "(datediff(day,[Data_pag],[Periodo]))" and is set to persistent. How can I solve the two problems: Column name and get the result of the expression computed?

Upvotes: 1

Views: 1302

Answers (2)

vasin1987
vasin1987

Reputation: 2012

When you use ISNULL() function, the column name cease to exist. You need to define new column name.

As for your second problem, you need to replace [Data_pag] with value processed from ISNULL()

SELECT Id, Periodo, Due, Paid, Balance,
Expenses, Notes, Completato, 
ISNULL(Data_pag, SYSDATETIME()) as Data_pag, 
(datediff(day,ISNULL(Data_pag, SYSDATETIME()),[Periodo])) as Ritardo 
 FROM dbo.affitto_sa

Upvotes: 0

praveen
praveen

Reputation: 12271

You need to use alias

 SELECT Id, Periodo, Due, Paid, Balance, 
 Expenses, Notes, Completato, ISNULL(Data_pag, SYSDATETIME()) as Data_pag, 
 datediff(day,isnull(Data_pag,SYSDATETIME()),Periodo) as Ritardo 
 FROM dbo.affitto_sa

You cannot reference a computer column in the same query. Either use a derived query or just use the same computed expresion for your other column

datediff(day,isnull(Data_pag,SYSDATETIME()),periodo)

or 

Select a.*,
datediff(day,Data_pag,Periodo) as Ritardo 
from 
(
 SELECT Id, Periodo, Due, Paid, Balance, 
 Expenses, Notes, Completato, ISNULL(Data_pag, SYSDATETIME()) as Data_pag, 
 Ritardo
 FROM dbo.affitto_sa
 )a

Upvotes: 1

Related Questions