|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Why can't i use "AS" results in the WHERE clause?SELECT TBL_Invoice.InvoiceID, DATEDIFF(d, TBL_Invoice.InvoiceDateCreated,
GETDATE()) AS DaysOld, FROM TBL_Invoice WHERE(TBL_Invoice.DaysOld>=50) If you look at this SQL statement I created a new "field" called DaysOld with the AS keyword. Why does this not allow me to put parameters in the WHERE clause? Is there a way to get around this? If I had say, 1 million records, returned and the only thing to prevent the database from reading back too much data and cutting it down to 100 records instead of 1 million is to use the DaysOld "field" I created, why would this not work? Please help, there's gotta be a way to get this working. It does not make sense that any SQL programmer should have to exclude a filter just because the created "field" is not part of the original table. Thanks in advance. Just wanted to correct the SQL, it's really:
SELECT TBL_Invoice.InvoiceID, DATEDIFF(d, TBL_Invoice.InvoiceDateCreated, GETDATE()) AS DaysOld FROM TBL_Invoice WHERE (DaysOld>=50) The error: Invalid column name 'DaysOld'. Show quote "R Reyes" wrote: > SELECT TBL_Invoice.InvoiceID, DATEDIFF(d, TBL_Invoice.InvoiceDateCreated, > GETDATE()) AS DaysOld, FROM TBL_Invoice WHERE(TBL_Invoice.DaysOld>=50) > > If you look at this SQL statement I created a new "field" called DaysOld > with the AS keyword. Why does this not allow me to put parameters in the > WHERE clause? > > Is there a way to get around this? If I had say, 1 million records, > returned and the only thing to prevent the database from reading back too > much data and cutting it down to 100 records instead of 1 million is to use > the DaysOld "field" I created, why would this not work? > > Please help, there's gotta be a way to get this working. It does not make > sense that any SQL programmer should have to exclude a filter just because > the created "field" is not part of the original table. > > Thanks in advance. because the WHERE is processed prior to the aliasing.
just include the expression in the where instead, e.g.: SELECT TBL_Invoice.InvoiceID, DATEDIFF(d, TBL_Invoice.InvoiceDateCreated, GETDATE()) AS DaysOld FROM TBL_Invoice WHERE tbl_invoice.InvoiceDateCreated <= dateadd(day,datediff(day,0,GETDATE()),0)-50 [note: the above would have better performance than ... where datediff(...)>=50 ... ] R Reyes wrote: Show quote > Just wanted to correct the SQL, it's really: > > SELECT TBL_Invoice.InvoiceID, DATEDIFF(d, TBL_Invoice.InvoiceDateCreated, > GETDATE()) AS DaysOld FROM TBL_Invoice WHERE (DaysOld>=50) > > The error: Invalid column name 'DaysOld'. > > "R Reyes" wrote: > > >>SELECT TBL_Invoice.InvoiceID, DATEDIFF(d, TBL_Invoice.InvoiceDateCreated, >>GETDATE()) AS DaysOld, FROM TBL_Invoice WHERE(TBL_Invoice.DaysOld>=50) >> >>If you look at this SQL statement I created a new "field" called DaysOld >>with the AS keyword. Why does this not allow me to put parameters in the >>WHERE clause? >> >>Is there a way to get around this? If I had say, 1 million records, >>returned and the only thing to prevent the database from reading back too >>much data and cutting it down to 100 records instead of 1 million is to use >>the DaysOld "field" I created, why would this not work? >> >>Please help, there's gotta be a way to get this working. It does not make >>sense that any SQL programmer should have to exclude a filter just because >>the created "field" is not part of the original table. >> >>Thanks in advance. ORDER BY is parsed last.
SELECT list is parsed just before it. When the WHERE list is parsed, it has no idea what is in the SELECT list. So, you can (a) repeat the expression in the where clause, (b) derive the column in a subquery: SELECT InvoiceID, DaysOld FROM ( SELECT InvoiceID, DaysOld = DATEDIFF(DAY, InvoiceDateCreated, CURRENT_TIMESTAMP) FROM TBL_Invoice ) x WHERE DaysOld >= 50; or (c) be smarter about your where clause. SELECT InvoiceID, DaysOld = DATEDIFF(DAY, InvoiceDateCreated, CURRENT_TIMESTAMP) FROM TBL_Invoice WHERE InvoiceDateCreated < (CURRENT_TIMESTAMP - 50); Show quote "R Reyes" <RRe***@discussions.microsoft.com> wrote in message news:57B484A4-931D-43C5-9108-FE80F9D4696E@microsoft.com... > SELECT TBL_Invoice.InvoiceID, DATEDIFF(d, TBL_Invoice.InvoiceDateCreated, > GETDATE()) AS DaysOld, FROM TBL_Invoice WHERE(TBL_Invoice.DaysOld>=50) > > If you look at this SQL statement I created a new "field" called DaysOld > with the AS keyword. Why does this not allow me to put parameters in the > WHERE clause? > > Is there a way to get around this? If I had say, 1 million records, > returned and the only thing to prevent the database from reading back too > much data and cutting it down to 100 records instead of 1 million is to > use > the DaysOld "field" I created, why would this not work? > > Please help, there's gotta be a way to get this working. It does not make > sense that any SQL programmer should have to exclude a filter just because > the created "field" is not part of the original table. > > Thanks in advance. R Reyes wrote:
Show quote > SELECT TBL_Invoice.InvoiceID, DATEDIFF(d, It would still require a table scan and calculation for all columns even > TBL_Invoice.InvoiceDateCreated, GETDATE()) AS DaysOld, FROM > TBL_Invoice WHERE(TBL_Invoice.DaysOld>=50) > > If you look at this SQL statement I created a new "field" called > DaysOld with the AS keyword. Why does this not allow me to put > parameters in the WHERE clause? > > Is there a way to get around this? If I had say, 1 million records, > returned and the only thing to prevent the database from reading back > too much data and cutting it down to 100 records instead of 1 million > is to use the DaysOld "field" I created, why would this not work? > > Please help, there's gotta be a way to get this working. It does not > make sense that any SQL programmer should have to exclude a filter > just because the created "field" is not part of the original table. > > Thanks in advance. it what you propose could work. You can use the same calculation in the WHERE clause or do something like this to avoid too many double-calculations: create table ttt (col1 datetime) insert into ttt values (getdate() - 1) insert into ttt values (getdate() - 2) insert into ttt values (getdate() - 3) select DATEDIFF(d, a.MyDate, getdate()) MyDate From ( Select col1 MyDate From ttt Where DATEDIFF(d, col1, getdate()) > 1) a drop table ttt TY everyone, it works now! =)
Show quote "David Gugick" wrote: > R Reyes wrote: > > SELECT TBL_Invoice.InvoiceID, DATEDIFF(d, > > TBL_Invoice.InvoiceDateCreated, GETDATE()) AS DaysOld, FROM > > TBL_Invoice WHERE(TBL_Invoice.DaysOld>=50) > > > > If you look at this SQL statement I created a new "field" called > > DaysOld with the AS keyword. Why does this not allow me to put > > parameters in the WHERE clause? > > > > Is there a way to get around this? If I had say, 1 million records, > > returned and the only thing to prevent the database from reading back > > too much data and cutting it down to 100 records instead of 1 million > > is to use the DaysOld "field" I created, why would this not work? > > > > Please help, there's gotta be a way to get this working. It does not > > make sense that any SQL programmer should have to exclude a filter > > just because the created "field" is not part of the original table. > > > > Thanks in advance. > > It would still require a table scan and calculation for all columns even > it what you propose could work. You can use the same calculation in the > WHERE clause or do something like this to avoid too many > double-calculations: > > create table ttt (col1 datetime) > insert into ttt values (getdate() - 1) > insert into ttt values (getdate() - 2) > insert into ttt values (getdate() - 3) > > select DATEDIFF(d, a.MyDate, getdate()) MyDate > From ( > Select col1 MyDate From ttt Where DATEDIFF(d, col1, getdate()) > 1) a > > > drop table ttt > > > > > > -- > David Gugick > Quest Software > www.imceda.com > www.quest.com > >  |
|||||||||||||||||||||||
Other interesting topics