Advanced SQL query - 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

SQL Server Development (2000)

Advanced SQL query

Author

Topic

asimonse
Starting Member

4 Posts

Posted - 2003-10-02 : 04:24:31

I wan't to make a query as follow:
Source Table:
Column_A
1
2
3
4
5
Query result:
Column_A | Column_B
1 | 0
2 | 1
3 | 1
4 | 1
5 | 0
Add a column in the query result. For all the values form Column_A between 2 and 5 set the value in Column_B to value "1". All the other values should be value "0".

OMB
Yak Posting Veteran

88 Posts

Posted - 2003-10-02 : 04:50:36

try this



create table #mytable(
	id numeric(18,0))

insert into #mytable  (id) select 1
insert into #mytable  (id) select 2
insert into #mytable  (id) select 3
insert into #mytable  (id) select 4
insert into #mytable  (id) select 5
insert into #mytable  (id) select 6
insert into #mytable  (id) select 7
insert into #mytable  (id) select 8



select id ,
case when id between 2 and 4 then 1 else 0 end  as column_b 
from #mytable

drop table #mytable

asimonse
Starting Member

4 Posts

Posted - 2003-10-02 : 05:18:43

It doesn't work. I use MS Access or VB to compile this function (not the SQL profile analyser). I get a syntax error. I am glad that you understand my question and quick answering.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-10-02 : 07:18:17

CASE is a MS-SQL construct....Look into the ACCESS equivalent...IFF

Please note the FAQ's on this site...most questions posed here are related to the use of the MS-SQL product....there is an ACCESS sub-forum....but that is mainly because there is SOME (historical) knowledge here of MS-ACCESS

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-02 : 14:42:18

Andrew was talking about IIF:

IIF(condition, true value, false value)

Owais

Make it idiot proof and someone will make a better idiot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources