CODEWITHSUNDEEP
sqlsql-servert-sqlstring
Thunder
Thunder

Reputation: 11016

Sql select query with where from multiple columns

I have a simple table 

CREATE TABLE  a(
    id int IDENTITY(1,1) NOT NULL,
    x varchar(50) 
)

I found that following query works 

select cast (id as varchar(3))+cast (x as varchar(3)) c from a 
where cast (id as varchar(3))+cast (x as varchar(3))='1a'

but this does not work

select cast (id as varchar(3))+cast (x as varchar(3)) c from a 
where c='1a'

Does any one knows why is that? Please not that for some reason I don't want to use 

where id=1 and x ='a'

Upvotes: 0

Views: 1631

Answers (4)

Disillusioned
Disillusioned

Reputation: 14842

You could use a subquery as demonstrated by Bill Karwin. However: Please I urge you, do not do this. Please use the where clause you "don't want to use".

The following permits the query optimiser to determine an index to use and make the query run efficiently.

where id=1 and x ='a'

The following (and Bill's equivalent) prevents the query optimser from using indexes and will contribute to general performance problems on your server.

where cast (id as varchar(3))+cast (x as varchar(3))='1a'

Upvotes: 1

gbn
gbn

Reputation: 432667

Building on Bill Karwin's answer, I'd consider a computed column to encapsulate the expression so it can be reused elsewhere

ALTER TABLE a ADD COLUMN c AS cast (id as varchar(3)) +cast (x as varchar(3))

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332731

You'd have to use:

SELECT *
  FROM (SELECT CAST(id AS VARCHAR(3)) + CAST(x AS VARCHAR(3)) AS 'output' FROM a) x
 WHERE x.output = '1a'

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562891

Because expressions in the WHERE clause are evaluated to restrict rows before expressions and aliases are evaluated in the select-list.

Here's a workaround:

select aprime.*
from (select cast (id as varchar(3))+cast (x as varchar(3)) c from a) aprime
where aprime.c='1a';

Upvotes: 8

Related Questions