Please help with this 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

General SQL Server Forums

New to SQL Server Programming

Please help with this query

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2007-01-17 : 02:17:19

I have a table SIM_TempCustomer where I have 5 fileds nad Values Like

SIM_TempCustomer
----------------

Val1.........Val2.........Val3.........Val4.........Val5
---------------------------------------------------------
A.............NUll........1.............Null.........3
B.............Null........Null..........Null.........2
C.............Null........6.............12...........Null

What I want i,

For the first row,Val2 is null,second row and third row also having val2 as null, so I have to leave it like that only.
But first & third rows of Val3 are having values, Second row is not having any value, but since first & third row are having values, I have to get val3 of second row as '0'
For Val4m third row is having value, so I have to replace val4's second and first rows with 0

etc etc...

Like the select statement of this SIM_TempCustomer should give a result like

Val1.........Val2.........Val3.........Val4.........Val5
---------------------------------------------------------
A.............NUll........1.............0............3
B.............Null........0.............0............2
C.............Null........6.............12...........0

Could any one help me.

Thanks
Ceema

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 02:27:39

SELECT Val1, Val2, ISNULL(Val3, 0) AS Val3, ISNULL(Val4, 0) AS Val4, ISNULL(Val5, 0) AS Val5
FROM SIM_TempCustomer

Peter Larsson
Helsingborg, Sweden

ceema
Yak Posting Veteran

80 Posts

Posted - 2007-01-17 : 02:33:42

Hello Peso,

Here the problem is, Val2 is always not null...it can have values some times, so if I will replace it with

ISNULL(Val2, 0) AS Val2, then it will get replaced by 0. But it should not happen. If all the rows of val2 is null,
it should remain like that, if val2 is having value, it should replace null only with '0'.

I hope you understood my problem.

Thank you
Ceema

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 02:35:46

Or more general

-- prepare sample data
declare	@t table (val1 varchar(2), val2 int, val3 int, val4 int, val5 int)

insert	@t
select	'a', null, 1, null, 3 union all
select	'b', null, null, null, 2 union all
select	'c', null, 6, 12, null

-- show the result
select		t.val1,
		isnull(t.val2, x.val2) as val2,
		isnull(t.val3, x.val3) as val3,
		isnull(t.val4, x.val4) as val4,
		isnull(t.val5, x.val5) as val5
from		@t as t
cross join	(
			select		case when max(val2) is null then null else 0 end as val2,
					case when max(val3) is null then null else 0 end as val3,
					case when max(val4) is null then null else 0 end as val4,
					case when max(val5) is null then null else 0 end as val5
			from		@t
		) as x

Peter Larsson
Helsingborg, Sweden

ceema
Yak Posting Veteran

80 Posts

Posted - 2007-01-17 : 02:39:05

Hello Peso,

Thank you so much, this will do.

Thanks again

Ceema

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources