how to correct the below join query to get correct results

Question

how to correct the below join query to get correct results

asita

SSCarpal Tunnel

Points: 4144
More actions
February 13, 2012 at 11:08 am

#268216

select chl.GuaranteeCode GuaranteeCode , bal.GuaranteeCode as BalanceSheetGuaranteeCode
from dbo.Guarantees chl
left join dbo.Guarantees par on par.HyperionEntityId = chl.ParentHyperionEntityId
left join dbo.Guarantees gpar on gpar.HyperionEntityId = par.ParentHyperionEntityId
left join dbo.Guarantees ggpar on ggpar.HyperionEntityId = gpar.ParentHyperionEntityId
left join dbo.Guarantees bal
on ( chl.ParentHyperionEntityId = bal.ParentHyperionEntityId or
par.ParentHyperionEntityId =bal.ParentHyperionEntityId or
gpar.ParentHyperionEntityId =bal.ParentHyperionEntityId OR
ggpar.ParentHyperionEntityId =bal.ParentHyperionEntityId
)
and bal.HfmCode like 'bal_%'
where chl.HfmCode like 'PL%'
order by 1
Hello All,
in above query what i am doing is get the parent or great parent or great grand parent id, the proble here is if there is no parent then get from grand parent if not then get from great grand parent
so in the above query it is working fine it gets parent also gets grand parent also get great grand parent so could you please modify the above query i will only get one the parent is found (from low to high., i.e., if parent found then parent then leave it, if not parent found then get great parent value if no great parent then get great grand parent value etc..)
please help me,
Thanks in advance
asitta

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76996 More actions · Answer 1

asita (2/13/2012)
select chl.GuaranteeCode GuaranteeCode , bal.GuaranteeCode as BalanceSheetGuaranteeCode
from dbo.Guarantees chl
left join dbo.Guarantees par on par.HyperionEntityId = chl.ParentHyperionEntityId
left join dbo.Guarantees gpar on gpar.HyperionEntityId = par.ParentHyperionEntityId
left join dbo.Guarantees ggpar on ggpar.HyperionEntityId = gpar.ParentHyperionEntityId
left join dbo.Guarantees bal
on ( chl.ParentHyperionEntityId = bal.ParentHyperionEntityId or
par.ParentHyperionEntityId =bal.ParentHyperionEntityId or
gpar.ParentHyperionEntityId =bal.ParentHyperionEntityId OR
ggpar.ParentHyperionEntityId =bal.ParentHyperionEntityId
)
and bal.HfmCode like 'bal_%'
where chl.HfmCode like 'PL%'
order by 1
Hello All,
in above query what i am doing is get the parent or great parent or great grand parent id, the proble here is if there is no parent then get from grand parent if not then get from great grand parent
so in the above query it is working fine it gets parent also gets grand parent also get great grand parent so could you please modify the above query i will only get one the parent is found (from low to high., i.e., if parent found then parent then leave it, if not parent found then get great parent value if no great parent then get great grand parent value etc..)
please help me,
Thanks in advance
asitta

If the parent ID is missing, isn't the following join going to fail to produce an matches?

left join dbo.Guarantees gpar on gpar.HyperionEntityId = par.ParentHyperionEntityId

Similarly with the grandparent and great grandparent?

Since you are working with a hierarchy, you should try searching on this website for working with hierarchies. There have been a number of discussions about using recursive CTEs for hierarchies.

Another option is to change your existing join condition to the following:

bal.ParentHyperionEntityId = CASE

WHEN chl.ParentHyperionEntityId IS NOT NULL THEN chl.ParentHyperionEntityId

WHEN par.ParentHyperionEntityId IS NOT NULL THEN par.ParentHyperionEntityId

WHEN gpar.ParentHyperionEntityId IS NOT NULL THEN gpar.ParentHyperionEntityId

ELSE ggpar.ParentHyperionEntityId

END

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

asita SSCarpal Tunnel Points: 4144 More actions · Answer 2

Hi Drew,

Thanks a lot for your input,

it is not working, you case statement,

it seems to be logically correct but when sql processing it it is applying case sattement once per query execute not for every row

case statement result and the below results are same

select chl.HyperionCode HyperionCode , bal.HyperionCode as BalanceSheetHyperionCode

from WorldwideBalanceReporting.dbo.HyperionEntity chl

left join WorldwideBalanceReporting.dbo.HyperionEntity par on par.HyperionEntityId = chl.ParentHyperionEntityId

left join WorldwideBalanceReporting.dbo.HyperionEntity gpar on gpar.HyperionEntityId = par.ParentHyperionEntityId

left join WorldwideBalanceReporting.dbo.HyperionEntity ggpar on ggpar.HyperionEntityId = gpar.ParentHyperionEntityId

left join WorldwideBalanceReporting.dbo.HyperionEntity bal

on ( chl.ParentHyperionEntityId = bal.ParentHyperionEntityId or

--par.ParentHyperionEntityId =bal.ParentHyperionEntityId or

--gpar.ParentHyperionEntityId =bal.ParentHyperionEntityId OR

--ggpar.ParentHyperionEntityId =bal.ParentHyperionEntityId

)

and bal.HfmCode like 'bal_%'

where chl.HfmCode like 'PL%'

order by 1

which is causing issue,

is there anyway that we can execute it per row wise (apply case statement)

please help me any other ideas too

thanks again drew

asitti

drew.allen SSC Guru Points: 76996 More actions · Answer 3

There's not much we can do without sample data and expected results. Check out the following link: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA