oracle ebs r12报告的Oracle flex值层次结构SQL查询
I am new to oracle and oracle ebs and I need some help.
我是oracle和oracle ebs的新手,我需要一些帮助。
I am doing a report in oracle ebs r12 and I need to list flex values from fnd_flex_values_vl view in a hierarchical way using a SQL query. It doesn`t necessary has to be a hierarchial query. Any query will do. I just need a SQL statement that will return me flex values in their hierarchial way.
我在oracle ebs r12中做了一个报告,我需要使用SQL查询以分层方式列出fnd_flex_values_vl视图中的flex值。它不一定是分层查询。任何查询都可以。我只需要一个SQL语句,它将以层次结构的方式返回flex值。
There are two objects, that store information about flex values hierarchy. It is FND_FLEX_VALUE_NORM_HIERARCHY (table) and fnd_flex_value_children_v (view). I assume one of these is enough, since fnd_flex_value_children_v is made using FND_FLEX_VALUE_NORM_HIERARCHY and some other objects.
有两个对象,用于存储有关flex值层次结构的信息。它是FND_FLEX_VALUE_NORM_HIERARCHY(表)和fnd_flex_value_children_v(视图)。我假设其中一个就足够了,因为fnd_flex_value_children_v是使用FND_FLEX_VALUE_NORM_HIERARCHY和其他一些对象制作的。
However, the problem I faced is, that several parents may be listed for one flex value and that I need to find all top parents or leaves in order to do an up-bottom or bottom-up hierarchy. As far as I understand fnd_flex_value_children_v doesn`t necessary store top parents (stores only children).
然而,我遇到的问题是,可能会列出几个父母的一个弹性值,我需要找到所有顶级父母或叶子,以便进行上下或自下而上的层次结构。据我所知,fnd_flex_value_children_v没有必要存储顶级父母(仅存储子女)。
Also it seems that there is probably not one, but there may be multiple hierarchies (if so, I need to list them all in one query).
似乎可能没有一个,但可能有多个层次结构(如果是这样,我需要在一个查询中列出它们)。
Your help will be really appreciated. I`ve been struggling with this one quite a while. Thank you very much for your attention.
非常感谢您的帮助。我一直在努力解决这个问题。非常感谢您的关注。
Best regards, new user. =)
最好的问候,新用户。 =)
2 个解决方案
#1
0
You should use the table, APPLSYS.FND_FLEX_VALUE_SETS. The objects you identify are metadata objects about the FND_FLEX_VALUE_SETS table.
您应该使用表APPLSYS.FND_FLEX_VALUE_SETS。您标识的对象是关于FND_FLEX_VALUE_SETS表的元数据对象。
I like to start with the root record.
我喜欢从根记录开始。
Here is my method to find root records (no parent).
这是我找到根记录的方法(没有父记录)。
SELECT DISTINCT
FVS.PARENT_FLEX_VALUE_SET_ID
FROM
APPLSYS.FND_FLEX_VALUE_SETS FVS
WHERE
FVS.PARENT_FLEX_VALUE_SET_ID IS NOT NULL
ORDER BY 1 ;
Once I find the root record, I develop my start by clause:
一旦找到根记录,我就开发了我的start by子句:
START WITH
(
FVS.FLEX_VALUE_SET_ID IN
(SELECT DISTINCT FVS.PARENT_FLEX_VALUE_SET_ID
FROM APPLSYS.FND_FLEX_VALUE_SETS FVS
WHERE FVS.PARENT_FLEX_VALUE_SET_ID IS NOT NULL
)
This clause does capture all root records (you could select just one).
该子句捕获所有根记录(您只能选择一个)。
Next, I develop my connect by clause. Since I want my hierarchy to start at the root, I would take this approach:
接下来,我开发了我的connect by子句。既然我希望我的层次结构从根开始,我会采用这种方法:
level 1 flex_value_set_id ....prior level
级别1 flex_value_set_id ....先前级别
level 2 parent_flex_value_set_id
2级parent_flex_value_set_id
CONNECT BY fvs.parent_flex_value_set_id = prior fvs.flex_value_set_id ;
This results in this statement:
这导致了这个陈述:
SELECT LEVEL,
FVS.*
FROM APPLSYS.FND_FLEX_VALUE_SETS FVS
START WITH
(
FVS.FLEX_VALUE_SET_ID IN
(SELECT DISTINCT FVS.PARENT_FLEX_VALUE_SET_ID
FROM APPLSYS.FND_FLEX_VALUE_SETS FVS
WHERE FVS.parent_flex_value_set_id IS NOT NULL
)
)
CONNECT BY FVS.PARENT_FLEX_VALUE_SET_ID = PRIOR FVS.FLEX_VALUE_SET_ID ;
One then can add the flex values as follows:
然后可以添加flex值,如下所示:
SELECT
LEVEL,
FVS.*
FROM
(SELECT
FLEX.FLEX_VALUE_SET_ID,
FLEX.PARENT_FLEX_VALUE_SET_ID,
FLEX.FLEX_VALUE_SET_NAME,
FVAL.FLEX_VALUE
FROM
APPLSYS.FND_FLEX_VALUE_SETS FLEX,
APPLSYS.FND_FLEX_VALUES FVAL
WHERE
FLEX.FLEX_VALUE_SET_ID = FVAL.FLEX_VALUE_SET_ID(+)) FVS
START WITH
(FVS.FLEX_VALUE_SET_ID IN
(SELECT DISTINCT
FVS.PARENT_FLEX_VALUE_SET_ID
FROM APPLSYS.FND_FLEX_VALUE_SETS FVS
WHERE FVS.parent_flex_value_set_id IS NOT NULL ) )
CONNECT BY
FVS.PARENT_FLEX_VALUE_SET_ID = PRIOR FVS.FLEX_VALUE_SET_ID;
更多相关文章
- 如何通过使用where子句与字符串格式(varchar(103),...,10)将103格式化
- SQL:使用IN子句搜索列值
- 如何在drupal视图中添加DISTINCT,GROUP BY子句
- 从MySQL转储中删除DEFINER子句。
- 从Access表将数据导入Excel,从子句中出现语法错误
- SSAS中用于层次结构的数据驱动安全性
- 将非常简单的Expression >转换为SQL where子句
- HSQLDB / Oracle - IN子句中的1000多个项目