|
|
You're in the
sql server new users
group:query help
sql server new users:
than zero (amount > 0) but I can't actually use 'amount' as it isn't actually a column. So, how would I go about it? select amount = (total-paid) ,datedue = dateadd(day, 30, stmtdate) from iheader where total <> paid and guarantor = 26752 and stmtdate < '2007-07-31' -- and amount > 0 THIS WILL NOT WORK TIA, Jim
Actually, the (total-paid) > 0 works for my purposes.
Total > Paid would not have worked for me since I needed to test for the final amount after paid is subtracted from total. In some cases, it returned negative values which I needed to filter out. Thanks Ron! [quoted text, click to view] "Roy Harvey" <roy_harvey@snet.net> wrote in message news:ru6ed31770ko8054a3jlk0vqcl5272nvlm@4ax.com... > Well you could simply say: > and (total-paid) > 0 > > but since you already test for: >> total <> paid > > why not simply test for > total > paid > > which sure looks like what you are trying to do anyway. > > Roy Harvey > Beacon Falls, CT > > On Thu, 30 Aug 2007 12:26:47 -0700, "Jim in Arizona" > <tiltowait@hotmail.com> wrote: > >>In the statement(s) below I'm trying query out 'amount' values that are >>less >>than zero (amount > 0) but I can't actually use 'amount' as it isn't >>actually a column. So, how would I go about it? >> >> select >> amount = (total-paid) >>,datedue = dateadd(day, 30, stmtdate) >> from iheader >> where >> total <> paid >> and guarantor = 26752 >> and stmtdate < '2007-07-31' >>-- and amount > 0 THIS WILL NOT WORK >> >>TIA, >>Jim >>
I was getting this error when I tried doing 'and amount > 0'.
Msg 207, Level 16, State 3, Line 1 Invalid column name 'amount'. [quoted text, click to view] "Ekrem Önsoy" <ekrem@btegitim.com> wrote in message news:0A88A4AD-2383-4F10-8972-5CE8C85DD350@microsoft.com... > Following code works in my environment. > > "and amount > 0" included > > -- > Ekrem Önsoy > MCBDA, MCTS: SQL Server 2005, MCSD.Net, MCSE, MCT > > > > "Jim in Arizona" <tiltowait@hotmail.com> wrote in message > news:uctK2uz6HHA.3900@TK2MSFTNGP02.phx.gbl... >> In the statement(s) below I'm trying query out 'amount' values that are >> less than zero (amount > 0) but I can't actually use 'amount' as it isn't >> actually a column. So, how would I go about it? >> >> select >> amount = (total-paid) >> ,datedue = dateadd(day, 30, stmtdate) >> from iheader >> where >> total <> paid >> and guarantor = 26752 >> and stmtdate < '2007-07-31' >> -- and amount > 0 THIS WILL NOT WORK >> >> TIA, >> Jim >> >
[quoted text, click to view]
"Roy Harvey" <roy_harvey@snet.net> wrote in message news:vrced355k6gpm3jgjqr8sdfmkd71adbj3b@4ax.com... > On Thu, 30 Aug 2007 13:23:44 -0700, "Jim in Arizona" > <tiltowait@hotmail.com> wrote: > >>I was getting this error when I tried doing 'and amount > 0'. >> >>Msg 207, Level 16, State 3, Line 1 >>Invalid column name 'amount'. > > That is expected at execution time. The WHERE cause is evaluated > before any SELECT list columns have been created. All the WHERE > clause has available are actual column names, not calculated values > from the SELECT list If you have a calculation in the SELECT list you > have to repeat the calculation in the WHERE clause rather than > reference it by the alias. > > But if you simply use the option to check the syntax, which is all > someone without the tables in place can do, it will pass the syntax > check. For all the optimizer knows the table could have a column > named "amount". > > Roy Harvey > Beacon Falls, CT That was informative, Roy. Thanks. Actually, I now recall reading about execution order in a book I got not too long ago (ms press, T-SQL Querying 2005). Perhaps it's time for me to revisit my book(s).. :)
Well you could simply say:
and (total-paid) > 0 but since you already test for: [quoted text, click to view] > total <> paid why not simply test for total > paid which sure looks like what you are trying to do anyway. Roy Harvey Beacon Falls, CT On Thu, 30 Aug 2007 12:26:47 -0700, "Jim in Arizona" [quoted text, click to view] <tiltowait@hotmail.com> wrote:
>In the statement(s) below I'm trying query out 'amount' values that are less >than zero (amount > 0) but I can't actually use 'amount' as it isn't >actually a column. So, how would I go about it? > > select > amount = (total-paid) >,datedue = dateadd(day, 30, stmtdate) > from iheader > where > total <> paid > and guarantor = 26752 > and stmtdate < '2007-07-31' >-- and amount > 0 THIS WILL NOT WORK > >TIA, >Jim
On Thu, 30 Aug 2007 22:49:57 +0300, Ekrem Önsoy <ekrem@btegitim.com>
[quoted text, click to view] wrote: >Following code works in my environment. > >"and amount > 0" included It passes a syntax check because, without reference to the underlying tables, the optimizer is unaware that there is no column named amount in the table being queried. But it will not execute. Roy Harvey
On Thu, 30 Aug 2007 13:23:44 -0700, "Jim in Arizona"
[quoted text, click to view] <tiltowait@hotmail.com> wrote: >I was getting this error when I tried doing 'and amount > 0'. > >Msg 207, Level 16, State 3, Line 1 >Invalid column name 'amount'. That is expected at execution time. The WHERE cause is evaluated before any SELECT list columns have been created. All the WHERE clause has available are actual column names, not calculated values from the SELECT list If you have a calculation in the SELECT list you have to repeat the calculation in the WHERE clause rather than reference it by the alias. But if you simply use the option to check the syntax, which is all someone without the tables in place can do, it will pass the syntax check. For all the optimizer knows the table could have a column named "amount". Roy Harvey
Following code works in my environment.
"and amount > 0" included -- Ekrem Önsoy MCBDA, MCTS: SQL Server 2005, MCSD.Net, MCSE, MCT [quoted text, click to view] "Jim in Arizona" <tiltowait@hotmail.com> wrote in message
news:uctK2uz6HHA.3900@TK2MSFTNGP02.phx.gbl... > In the statement(s) below I'm trying query out 'amount' values that are > less than zero (amount > 0) but I can't actually use 'amount' as it isn't > actually a column. So, how would I go about it? > > select > amount = (total-paid) > ,datedue = dateadd(day, 30, stmtdate) > from iheader > where > total <> paid > and guarantor = 26752 > and stmtdate < '2007-07-31' > -- and amount > 0 THIS WILL NOT WORK > > TIA, > Jim >
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |