Row Count 1

[aumstu]

I am writing a case statement and need to add a value if the row count is 0 ...Here is my code

update A
Set SDBACHE_STLEV 
= CASE
When (b.Hours_Earned) Between 0 and 27.990 Then '02'
When (b.Hours_Earned) @@RowCount = 0 Then '02'
When (b.Hours_Earned) Between 28 and 57.990 Then '03'
When (b.Hours_Earned) Between 58 and 87.990 Then '04'
When (b.Hours_Earned) Between 88 and 999 Then '05'
Else '10'
End
from sdbache A join Hours b
on a.sdbache_id = b.Hours_id
and sdbache_stlev = '10'

does anyone have any suggestions

thanks

 

[vongrunt]

Row count of previous statement? Plz explain...

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[aumstu]

I have two tables...one SDBACHE and another Named Hours. Most records in SDBAHCE have a value in the table Hours...but there are a few records that dont.

The code above works great if I take out the
When (b.Hours_Earned) @@RowCount = 0 Then '02'


Wwhenever a row does not exists in the Hours table...I need to set the value to 02. I would put 02 in the Else statement, but I have to do additional checks and I also want to make sure that there is no record in the Hours table.

Hope that helps.
Thanks

 

[SQLDenis]

what about this?

update A
Set SDBACHE_STLEV 
= CASE
When (b.Hours_Earned) Between 0 and 27.990 Then '02'
When (b.Hours_Earned) is null Then '02'
When (b.Hours_Earned) Between 28 and 57.990 Then '03'
When (b.Hours_Earned) Between 58 and 87.990 Then '04'
When (b.Hours_Earned) Between 88 and 999 Then '05'
Else '10'
End
from sdbache A left join Hours b
on a.sdbache_id = b.Hours_id
and sdbache_stlev = '10'

or

update A
Set SDBACHE_STLEV 
= CASE
When coalesce(b.Hours_Earned,0) Between 0 and 27.990 Then '02'
When (b.Hours_Earned) Between 28 and 57.990 Then '03'
When (b.Hours_Earned) Between 58 and 87.990 Then '04'
When (b.Hours_Earned) Between 88 and 999 Then '05'
Else '10'
End
from sdbache A left join Hours b
on a.sdbache_id = b.Hours_id
and sdbache_stlev = '10'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[aumstu]

Thanks SQLDenis..

I tried to the isnull but since the row does not exists in the Hours Table...it skips over it.

I like the coalesce code...Never seen that before. Definitely will use that one day


Thanks

 

[vongrunt]

You tried with inner join... Denis used outer join... big difference.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLDenis]

you did add the left join right?

take a look at this

CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)


CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)


select case when tj.id is null then 0
when tj.id between 1 and 3 then 8
else 9 end as SomeField,tj.id from testnulls tn left join testjoin tj on tn.id =tj.id


select case when coalesce(tj.id,0) = 0 then 0
when tj.id between 1 and 3 then 8
else 9 end as SomeField,tj.id from testnulls tn left join testjoin tj on tn.id =tj.id

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[aumstu]

I have to learn to pay attention to details....that worked out perfect.

I did have to change the last line -

and sdbache_stlev = '10'

to

where sdbache_stlev = '10'

but that was my typo.

Thanks again and here is a star