Re: Using Case colum in where clause

From: Malc (Malc_at_discussions.microsoft.com)
Date: 11/22/04 

Date: Mon, 22 Nov 2004 03:43:04 -0800

Thanks but Hugo came up with that one - it just seems a shame that SQL can't
use a derived column's alias in a where clause. I was wondering if there was
any syntax problem I had - obviously not unfortunately !

"Mal" wrote:

> Ok you dont want to retype the case...
>
> "Mal" wrote:
>
> > Why can't you use this ?
> >
> > select col1, col2,col3
> > -- case
> > -- when col1>col3 then 1
> > -- else 0
> > -- end as A
> > from dummytable a
> > where col2 <> (select case
> > when a.col1>a.col3 then 1
> > else 0 end )
> >
> > "Malc" wrote:
> >
> > > Thx H,
> > > I have used similar to that before but in this case I was wondering if it
> > > could be used in the context:
> > > select
> > > tbl1.col1, tbl1.col2,tbl1.col3
> > > case
> > > when tbl1.col2>tbl1.col3 then 1
> > > else 0
> > > end as A -- I would use a better term - calcvalue or similar
> > >
> > > what I really wanted to do was then use a where clause
> > >
> > > where tbl1.col1 <> A -- calcvalue just used A for ease
> > >
> > > on the basis that the database should have a calculted field col1 that has
> > > a value of 1 if col2> col3
> > >
> > > In prcatoce it is a hell of a lot more complicated, col1 being an overnight
> > > calculated
> > > value and my code is to be run to independantly double check that the
> > > overnight job is correct.. The principle is as above though.
> > > The problem is Hugo that TSQL quite happily shows the name that I give the
> > > case result in the TSQL table but doesn't let me use it.
> > > So....
> > > Can I continue to use CASE in my Select rather than in the FROM
> > > or
> > > If I recode and put it in the FROM...... as A
> > > will it recognise it then and allow me to use it in a where clause ?
> > >
> > > Cheers
> > >
> > >
> > > "Hugo Kornelis" wrote:
> > >
> > > > On Fri, 19 Nov 2004 03:25:04 -0800, Malc wrote:
> > > >
> > > > >I have a case column called A. When I try and use the column A to compare
> > > > >with one of the other columns B it says that A is invalid
> > > > >Invalid column name 'A'
> > > > >I need to list a number of columns from multiple tables, carry out a
> > > > >calculation using the CASE to create a new column and then compare the new
> > > > >column with one on the database.
> > > > >I can get around it by pasting into SQL but ideally I want to return rows
> > > > >where the database value and my CASE column ar not equal.
> > > > >I could of courese repeat all the case information and include a check for
> > > > >the existing colum, but as the case is about 35 lines of code, I'd prefer to
> > > > >use the resulting column instead.
> > > > >Any ideas ?
> > > > >
> > > > >Thx
> > > >
> > > > Hi Malc,
> > > >
> > > > Use a derived table:
> > > >
> > > > SELECT .......
> > > > FROM (SELECT CASE .....
> > > > FROM ......
> > > > WHERE ....) AS x
> > > > WHERE .....
> > > >
> > > > Best, Hugo
> > > > --
> > > >
> > > > (Remove _NO_ and _SPAM_ to get my e-mail address)
> > > >

Relevant Pages

  • Re: Aliases and the WHERE clause
    ... The one hope we could hold is that future releases of engines will build on ... To me, SQL also feels just like a set of principles, or if you wish, mental ... I was thinking about the ORDER BY clause. ... alias in the WHERE clause. ...
    (microsoft.public.access.queries)
  • Re: Query doesnt always sort
    ... ORDER BY clause or when using ODBC to linked MS SQL tables. ... I don't believe the query is from SQL2K since ... you can set an alias ...
    (microsoft.public.access.queries)
  • Re: Aliases and the WHERE clause
    ... I was thinking about the ORDER BY clause. ... alias in the WHERE clause. ... based on other aliases). ... Note that MS SQL Server cannot use expression ...
    (microsoft.public.access.queries)
  • Re: Using Case colum in where clause
    ... >it makes it easier to understand n the where clause. ... >Odd that SQL doen't allow the use of table Alias names in where clauses - ho ... Not odd at all - very logical if you consider how queries ...
    (microsoft.public.sqlserver.programming)
  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)