Hierarchy with indeterminate number of level

[IanWaterman]

Trying to build an investment query where I drill down from a fund or a company and find all underlying funds of investee companies.
In reality this is unlikely to go down more than 3-4 levels but could go further.

A company can invest in another company or fund and a fund can invest in another fund or company, hence the 4 groups of left joined queries.

The query works fine for a level. However, I then need to repeat but using the INVESTEE_FUND_IQID and INVESTEE_COMPANY_IQID as my starting point.

I need to produce a Stored Proc which can accept ACCOUNTID, FUNDID or COMPANYId and then show all levels.
I can pass the result of the table to a Temp table and identify the level/layer. So my plan is to append output for each level to temp table and then use that again in another embedded SP to filter on layer number and use that data as below.

My issue is that I can not find any code that allows me to loop and then also know when to stop trying to find data when hierarchy comes to an end.
Unfortunately I do not have any sample data. I am using Management Studio 17.9 but database is 2012

Thank you
Ian

select --TOP 100
			ACCOUNT_NAME,
			ACCOUNT_IQID,
			MANAGED_ENTITY_IQID,
			MANAGED_ENTITY_NAME,
			MANAGED_FUND_IQID,
			MANAGED_FUND_NAME,
			MANAGED_COMPANY_IQID,
			MANAGED_COMPANY_NAME ,
			STATUS_DECSR,
			'1' AS LAYER,
			ISNULL(MF.IQID, MCF.IQID) AS INVESTEE_FUND_IQID,
			ISNULL(MF.FUND, MCF.FUND) AS INVESTEE_FUND_NAME,
			ISNULL(MC.IQID, MIC.IQID) AS INVESTEE_COMPANY_IQID,
			ISNULL(MC.NAME, MIC.NAME) AS INVESTEE_COMPANY_NAME

FROM		MANAGED_ENTITY AS ME
--MANAGED FUND LINK (FOF LINK)
LEFT JOIN	VCSUBSCRVEHICUL AS SV ON ME.MANAGED_FUND_IQID = SV.FUND AND SV.IQDELETED = 0
LEFT JOIN	VCSUBSCRIBER AS S ON SV.SUBSCRIBER = S.IQID AND S.IQDELETED = 0
LEFT JOIN	VCFUND AS MF ON S.FUND = MF.IQID AND MF.IQDELETED = 0

LEFT JOIN	VCSUBSCRVEHICUL AS SCV ON ME.MANAGED_COMPANY_IQID = SCV.ACCOUNT AND SCV.IQDELETED = 0
LEFT JOIN	VCSUBSCRIBER AS SC ON SCV.SUBSCRIBER = SC.IQID AND SC.IQDELETED = 0
LEFT JOIN	VCFUND AS MCF ON SC.FUND = MCF.IQID AND MCF.IQDELETED = 0

--MANAGED COMPANY LINK (DIRECT)
LEFT JOIN	VCINVESTMENT AS I ON MANAGED_COMPANY_IQID = I.FIRM AND I.IQDELETED = 0
LEFT JOIN	SFAACCOUNT AS MC ON I.ACCOUNT = MC.IQID AND MC.IQDELETED = 0
--
LEFT JOIN	VCINVESTMENT AS IC ON MANAGED_FUND_IQID = IC.FUND AND IC.IQDELETED = 0
LEFT JOIN	SFAACCOUNT AS MIC ON I.ACCOUNT = MIC.IQID AND MIC.IQDELETED = 0

 

[johnherman]

looping in SQL Server is via Transact-SQL, which is the Microsoft proprietary extensions to ANSI-SQL. You would need to use a TRANSACT-SQL CURSOR (CURrent Set Of Rows). You would need to test for the endpoint (no more rows in hierarchy) by checking the error/return code from the cursor's FETCH command.

You may be able to find examples of CURSORS within this forum, or certainly can find some online.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[TamarGranor]

If I understand your question, what you need is a recursive CTE. I've written about these here:

http://www.tomorrowssolutionsllc.com/Articles/Handling Hierarchical Data.pdf.

(Ignore the Visual FoxPro pieces of the article.) This also looks like a good basic explanation:

https://www.essentialsql.com/recursive-ctes-explained/

Tamar

 

[IanWaterman]

Tamar

Thank you for your guides, I have tried what you suggest, here is my code.
However, it will not execute as it will not allow left outer joins. I have to use left outer joins in the investee company and fund tables

Msg 462, Level 16, State 1, Line 107
Outer join is not allowed in the recursive part of a recursive common table expression 'LEVEL2'.

Any suggestions
Ian

LEVEL2 AS (
select --DISTINCT
			ACCOUNT_NAME,
			ACCOUNT_IQID,
			MANAGED_ENTITY_IQID,
			MANAGED_ENTITY_NAME,
			INVESTEE_FUND_IQID		AS	MANAGED_FUND_IQID,
			INVESTEE_FUND_NAME		AS	MANAGED_FUND_NAME,
			INVESTEE_COMPANY_IQID	AS	MANAGED_COMPANY_IQID,
			INVESTEE_COMPANY_NAME	AS	MANAGED_COMPANY_NAME ,
			STATUS_DECSR,
			2 AS LAYER,
			ISNULL(MF.IQID, MCF.IQID) AS INVESTEE_FUND_IQID,
			ISNULL(MF.FUND, MCF.FUND) AS INVESTEE_FUND_NAME,
			ISNULL(MC.IQID, MIC.IQID) AS INVESTEE_COMPANY_IQID,
			ISNULL(MC.NAME, MIC.NAME) AS INVESTEE_COMPANY_NAME
			
FROM		LEVEL1 AS ME
--MANAGED FUND LINK (FOF LINK)
LEFT JOIN	VCSUBSCRVEHICUL AS SV ON ME.INVESTEE_FUND_IQID = SV.FUND AND SV.IQDELETED = 0
LEFT JOIN	VCSUBSCRIBER AS S ON SV.SUBSCRIBER = S.IQID AND S.IQDELETED = 0
LEFT JOIN	VCFUND AS MF ON S.FUND = MF.IQID AND MF.IQDELETED = 0 and MF.IQID <> ME.MANAGED_FUND_IQID

LEFT JOIN	VCSUBSCRVEHICUL AS SCV ON ME.INVESTEE_COMPANY_IQID = SCV.ACCOUNT AND SCV.IQDELETED = 0
LEFT JOIN	VCSUBSCRIBER AS SC ON SCV.SUBSCRIBER = SC.IQID AND SC.IQDELETED = 0
LEFT JOIN	VCFUND AS MCF ON SC.FUND = MCF.IQID AND MCF.IQDELETED = 0 and MCF.IQID <> ME.MANAGED_FUND_IQID 

--MANAGED COMPANY LINK (DIRECT)
LEFT JOIN	VCINVESTMENT AS I ON INVESTEE_COMPANY_IQID = I.FIRM AND I.IQDELETED = 0
LEFT JOIN	SFAACCOUNT AS MC ON I.ACCOUNT = MC.IQID AND MC.IQDELETED = 0 and MC.IQID <> ME.MANAGED_COMPANY_IQID
--
LEFT JOIN	VCINVESTMENT AS IC ON INVESTEE_FUND_IQID = IC.FUND AND IC.IQDELETED = 0
LEFT JOIN	SFAACCOUNT AS MIC ON I.ACCOUNT = MIC.IQID AND MIC.IQDELETED = 0 and MIC.IQID <> ME.MANAGED_COMPANY_IQID


--WHERE	ACCOUNT_IQID	= 'EFD7FF92A1CD48B29626FC3A77D12238'
WHERE	ACCOUNT_IQID	= '44BF4D2D7518466AAE6B2A942C93075E'
		AND MANAGED_ENTITY_NAME like '%SVF%'
UNION ALL
	select --DISTINCT
			ACCOUNT_NAME,
			ACCOUNT_IQID,
			MANAGED_ENTITY_IQID,
			MANAGED_ENTITY_NAME,
			INVESTEE_FUND_IQID		AS	MANAGED_FUND_IQID,
			INVESTEE_FUND_NAME		AS	MANAGED_FUND_NAME,
			INVESTEE_COMPANY_IQID	AS	MANAGED_COMPANY_IQID,
			INVESTEE_COMPANY_NAME	AS	MANAGED_COMPANY_NAME ,
			STATUS_DECSR,
			--case when Coalesce(ISNULL(MF.IQID, MCF.IQID), ISNULL(MC.IQID, MIC.IQID)) is null then ME.Layer else  '1' end  AS LAYER,
			LAYER +1,
			ISNULL(MF.IQID, MCF.IQID) AS INVESTEE_FUND_IQID,
			ISNULL(MF.FUND, MCF.FUND) AS INVESTEE_FUND_NAME,
			ISNULL(MC.IQID, MIC.IQID) AS INVESTEE_COMPANY_IQID,
			ISNULL(MC.NAME, MIC.NAME) AS INVESTEE_COMPANY_NAME

FROM		LEVEL2 AS ME
--MANAGED FUND LINK (FOF LINK)
LEFT JOIN	VCSUBSCRVEHICUL AS SV ON ME.INVESTEE_FUND_IQID = SV.FUND AND SV.IQDELETED = 0
LEFT JOIN	VCSUBSCRIBER AS S ON SV.SUBSCRIBER = S.IQID AND S.IQDELETED = 0
LEFT JOIN	VCFUND AS MF ON S.FUND = MF.IQID AND MF.IQDELETED = 0 and MF.IQID <> ME.MANAGED_FUND_IQID

LEFT JOIN	VCSUBSCRVEHICUL AS SCV ON ME.INVESTEE_COMPANY_IQID = SCV.ACCOUNT AND SCV.IQDELETED = 0
LEFT JOIN	VCSUBSCRIBER AS SC ON SCV.SUBSCRIBER = SC.IQID AND SC.IQDELETED = 0
LEFT JOIN	VCFUND AS MCF ON SC.FUND = MCF.IQID AND MCF.IQDELETED = 0 and MCF.IQID <> ME.MANAGED_FUND_IQID

--MANAGED COMPANY LINK (DIRECT)
LEFT JOIN	VCINVESTMENT AS I ON INVESTEE_COMPANY_IQID = I.FIRM AND I.IQDELETED = 0
LEFT JOIN	SFAACCOUNT AS MC ON I.ACCOUNT = MC.IQID AND MC.IQDELETED = 0 and MC.IQID <> ME.MANAGED_COMPANY_IQID
--
LEFT JOIN	VCINVESTMENT AS IC ON INVESTEE_FUND_IQID = IC.FUND AND IC.IQDELETED = 0
LEFT JOIN	SFAACCOUNT AS MIC ON I.ACCOUNT = MIC.IQID AND MIC.IQDELETED = 0 and MIC.IQID <> ME.MANAGED_COMPANY_IQID


--WHERE	ACCOUNT_IQID	= 'EFD7FF92A1CD48B29626FC3A77D12238'-- 
WHERE	ACCOUNT_IQID	= '44BF4D2D7518466AAE6B2A942C93075E' -- 
		AND MANAGED_ENTITY_NAME like '%SVF%'
		AND Layer < 8
		)

select * from LEVEL2 where INVESTEE_FUND_IQID is not null OR INVESTEE_COMPANY_IQID is not null

 

[IanWaterman]

Please note I have other CTEs above which create the table Level1. The recursion occurs from level 2 down.
My plan is to union the level above to the recursive data. Works OK when I manually create the levels.

Hence no WITH in above code.

Ian

 

[johnherman]

You mention that things work OK when you manually create the levels. So......what is different about the automated process? Do the automated processes have the proper permissions? Does it have to do with the Active Directory account? Are you using integrated security (integrated with Windows) or a separate SQL Server signon. I would make a thorough investigation of the two environments, noting any differences between the working and non-working environments.

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[IanWaterman]

Hi John

Sorry my statement was perhaps a bit ambiguous. When I say manually create. I have strung together a series of CTEs which drill down through data and then UNION all the CTEs. I want to avoid this as I do not know how deep data can go.

So either a recursive CTE or your Cursor suggestion should work. Ideally I would like to use the Recursive CTE if possible as I understand these whilst my knowledge of Cursors is very limited.

Ian

 

[TamarGranor]

I haven't run into this, but a quick search suggests using OUTER APPLY instead of an outer join.

Tamar

 

[IanWaterman]

Solved problem by using two temp tables and looping through data until no records were found.

Thanks for your input

Ian