帐户层次结构 Salesforce 帐户 - SQL Server [英] Account Hierarchy Salesforce Accounts - SQL Server
问题描述
我正在处理使用 Salesforce Datawarehouse 的报告.本质上,给定一个特定帐户,我需要为此找到层次结构.这可以在任何级别下,但它必须获得所有节点.
I am working on a report that uses Salesforce Datawarehouse. Essentially, given a particular account, I need to find the hierarchy just for that. This could be at any level down but it HAS to get ALL the NODES.
以下是我所拥有的:
WITH tree (id, parentid, level, name) as
(
SELECT id, parentid, 0 as level, name
FROM accounts
WHERE (parentid IS NULL
AND recordtypeid NOT IN ('012G0000001NLJSIA4', '012G0000001NLKGIA4'))
UNION ALL
SELECT c2.id, c2.parentid, tree.level + 1, c2.name
FROM accounts c2
INNER JOIN tree ON tree.id = c2.parentid
AND recordtypeid NOT IN ('012G0000001NLJSIA4', '012G0000001NLKGIA4')
)
SELECT *
FROM tree
我真正应该做的是提供一个帐户参数,我正在寻找其详细信息,并让它只返回层次结构.
What I really should be able to do is provide an account parameter, whose details I am looking for and have it only return the hierarchy for that.
有什么帮助吗?我已经为此苦苦挣扎了几天了.
Any Help? I have been struggling this with for a couple of days now.
推荐答案
不确定您的帐户 ID 的数据类型是什么;然而,我保持安全并选择了INT.您需要做的就是为 CTE 的锚点部分使用一个参数,该参数将定义为级别 0,递归部分返回给定父项的所有子项.为什么不尝试以下操作:
Not sure what the datatype of your account ID's are; however, I stayed safe and chose INT. All you need to do is use a parameter for the anchor portion of the CTE, and that will be defined as level 0 with the recursive portion returning all of the children of the given parent. Why don't you try the following:
Declare @id INT = 1234;
WITH tree (id, parentid, level, name) as
(
SELECT id, parentid, 0 as level, name
FROM accounts
WHERE id = @id
AND recordtypeid NOT IN ('012G0000001NLJSIA4', '012G0000001NLKGIA4')
UNION ALL
SELECT c2.id, c2.parentid, tree.level + 1, c2.name
FROM accounts c2
INNER JOIN tree ON tree.id = c2.parentid
AND recordtypeid NOT IN ('012G0000001NLJSIA4', '012G0000001NLKGIA4')
)
SELECT *
FROM tree
这篇关于帐户层次结构 Salesforce 帐户 - SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!