October 21, 2009 at 2:19 pm
I have a set up somewhat similar to what I have written below.
I wondered if there was an easy way to rewrite it without the Cross Apply statement?
Basically, all the code does is run some procedural code against a parents child rows to determine what the parents setting value should be.
create table #parents
(
pkint not null
)
go
create table #children
(
pk int identity(1,1) not null,
fkParents int not null,
setting int
)
go
insert #parents
select 1
union all
select 2
union all
select 3
go
insert #children(fkParents, setting)
select 1, null
union all
select 1, null
union all
select 1, null
union all
select 2, 1
union all
select 2, 1
union all
select 2, 1
union all
select 3, 0
union all
select 3, null
union all
select 3, 1
go
select parents.pk, children.setting
from #parents parents
cross apply
(
selectcase when exists (select setting from #children where setting is null and fkParents = parents.pk) then 'x'
when exists (select setting from #children where setting = 1 and fkParents = parents.pk) then 'y'
else 'z' end as setting
) as children
go
drop table #children;
drop table #parents;
October 21, 2009 at 7:50 pm
"Easy" is a relative term. The following code is short, and should produce the same results, but I haven't tested for performance. Also, it's not nearly as readable. If you use it, comment it heavily because after a month I'm not sure that I could read it and remember what the rules were in the original case statement.
Just out of curiousity, why do you want to avoid the Cross Apply?
select p.pk, casewhen sum(case when setting is null then 1 else 0 end) > 0
and count(fkParents) > 0 then 'x'
when sum(setting) > 0 and count(fkParents) > 0 then 'y'
else 'z'
end as setting
from #parents p
left join #children c on p.pk = c.fkParents
group by p.pk
order by p.pk
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 22, 2009 at 1:22 pm
Thanks for the response, Bob.
Acutally, I don't intend to avoid the Cross Apply at all. I thought it looks quite readable, as you have pointed out.
I just wondered what the alternative approaches could be.
I think I'll stick with the Cross Apply then.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply