replacing values - 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

General SQL Server Forums

New to SQL Server Programming

replacing values

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2005-11-18 : 08:02:04

Hi i have 2 cols

col1 col2
A1 21
A2 22
A3 21
A4 23

I want to create a report such that i should see all values of col1 but in col2 i want to show only value of 21 and the remaining should be zero.
So basically all the other values except 21 should be zero in the report but should not be updated in the database.

So the report should look like

col1 col2
A1 21
A2 0
A3 21
A4 0

can you please tell me how to do that

Thanks
vic

Vicky

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-18 : 08:06:37

Which report are you using?
You can create a formula field having this code

If col2<>21 then 0

Otherwise write this query and design the report using that query

Select col1, case when col2<>21 then 0 else col2 end from yourTable

Madhivanan

Failing to plan is Planning to fail

svicky9
Posting Yak Master

232 Posts

Posted - 2005-11-18 : 08:55:08

how do i write this like

" select col1 , (col2 ???) from table1"

How do i want to constraint the col2 table with just 21 and the remaining to zero

thanks
Vic

Vicky

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-18 : 09:00:43

>> select col1 , (col2 ???) from table1"

What do you mean by that?

>>How do i want to constraint the col2 table with just 21 and the remaining to zero

How do you get data?
Are you using Front End Application?
If so, filter and send only VALID data to the table
If it is importing, then you can run an update statement

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-18 : 09:37:16

I guess this is what you want ?

select col1, case when col2 <> 21 then
			    0
		        else
			    col2
		        end as col2
from	#table1

[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-18 : 23:54:53

quote:

Originally posted by khtan

I guess this is what you want ?

select col1, case when col2 <> 21 then
			    0
		        else
			    col2
		        end as col2
from	#table1

[KH]

This is what I suggested in my first reply

Madhivanan

Failing to plan is Planning to fail

svicky9
Posting Yak Master

232 Posts

Posted - 2005-11-20 : 03:59:12

Hi guys

Thanks for your replies
however if you have more than one table

as below

select * from sample1

cola colb
a1 20
a2 30
a3 20
a4 20

select * from sample2

cola colb
a1 30
a2 40
a3 30
a4 30

and i execute the query like this

select sample1.col1,(case when sample3.col2 = 40 then sample3.col2
else 0 end as sample3.col2)
from sample1 join sample3
on sample1.col1 = sample3.col1

then it gives the error as

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'sample3'.

Can you please let me know where the error is ?

Thanks
Vic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-20 : 05:06:13

quote:
Originally posted by svicky9

Hi guys

Thanks for your replies
however if you have more than one table

as below

select * from sample1

cola colb
a1 20
a2 30
a3 20
a4 20

select * from sample2

cola colb
a1 30
a2 40
a3 30
a4 30

and i execute the query like this

select sample1.col1,(case when sample3.col2 = 40 then sample3.col2
else 0 end as sample3.col2 End )-- you missed out end..
from sample1 join sample3
on sample1.col1 = sample3.col1

then it gives the error as

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'sample3'.

Can you please let me know where the error is ?

Thanks
Vic

Hope this work for u ..

Complicated things can be done by simple thinking

svicky9
Posting Yak Master

232 Posts

Posted - 2005-11-20 : 05:51:27

select sample1.col1,(case when sample3.col2 = 40 then sample3.col2
else 0 as sample3.col2 end)
from sample1 join sample3
on sample1.col1 = sample3.col1

the error is

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'as'.

Vicky

svicky9
Posting Yak Master

232 Posts

Posted - 2005-11-20 : 05:53:12

Hi

I got it...

select sample1.col1,(case when sample3.col2 = 40 then sample3.col2
else 0 end)
from sample1 join sample3
on sample1.col1 = sample3.col1

thanks
Vic

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-20 : 06:11:26

opps first i didnt saw.. there was the end..

Also you can try out is ..

select sample1.col1,(case when sample3.col2 = 40 then sample3.col2
else 0 end) As <Alias Name >
from sample1 join sample3
on sample1.col1 = sample3.col1

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources