Refer to calculated column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Refer to calculated column

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-03-16 : 16:41:04

Hi there,

SELECT i1.custID,
datediff(dd, (select max(i3.start)
from intervals i3
where i3.custID = i1.custID and i3.start < i1.start),i1.start) as 'gaps'
FROM intervals i1

In the above query, I want to filter on the column called 'gaps'. Is it possible to do this without using a derived table?

I tried adding..

WHERE gaps is not null

..but it said, 'Invalid column name 'gaps''

Cheers,

X-Factor.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-16 : 16:48:22

Could you provide DDL for the table and DML for sample data? It is possible that your query can be rewritten, but without seeing what it is currently doing, it's hard to provide a solution.

Tara

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-03-16 : 17:34:52

Thanks for your reply.

I know it can be expressed differently. You get the two columns with a join and then filter the join using a similar sub query.

SELECT i1.custID, datediff(dd,i1.start, i2.start) as 'gaps' 
FROM intervals i1, intervals i2
WHERE 
i1.custID = i2.custID and i1.start =
(SELECT max(i3.start)
FROM intervals i3
WHERE i3.custID = i1.custID and i3.start < i2.start)

I would like to achieve the same result without a join.

Is there another way you have in mind?

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2004-03-16 : 19:22:55

The point I'm trying to make is that getting a correct result set is not an issue. I just want to know if its possible to filter on a calculated column.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-17 : 00:34:19

Didn't I show you the derived table trick before? Still works


SELECT custID, gaps FROM
(
 SELECT i1.custID,
   DATEDIFF(dd, (SELECT MAX(i3.start)
     FROM intervals i3
     WHERE i3.custID = i1.custID and i3.start < i1.start),i1.start) AS 'gaps'
   FROM intervals i1 
) A
WHERE gaps IS NOT NULL