Purpose of Query
The SQL query in this page lists the merge child having more then one mother
- The Datafunction being a merge root is the data function that has been merged with others, and it is the root of the merge, so it appears on the dashboard and TCC.
- The Datafunction being a merged item is the data function that has been merged as a child of another one, so it does not appear in the dashboard and TCC
Applicable CAST Version
Release | Yes/No |
|---|---|
| 8.2.x |  |
| 8.1.x | |
| 8.0.x | |
| 7.3.x | |
Applicable RDBMS
RDBMS | Yes/No |
|---|---|
| Oracle Server | |
| Microsoft SQL Server | |
| CSS2 | |
Query for CSS
SELECT DISTINCT d.object_id,
k.object_name,
f.cal_flags,
d.mergeflags,
d.table_id,
n.object_name
FROM dss_datafunction f
JOIN dss_datafunctiondetails d
ON d.object_id = f.object_id
AND f.cal_flags NOT IN ( 8, 4, 128, 256 )
JOIN dss_keysextra k
ON k.object_id = d.object_id
JOIN cdt_objects n
ON n.object_id = d.table_id
AND d.table_id IN (SELECT table_id
FROM dss_datafunction f
JOIN dss_datafunctiondetails d
ON d.object_id = f.object_id
AND f.cal_flags NOT IN (
8, 4, 128, 256 )
JOIN cdt_objects a
ON a.object_id = d.table_id
GROUP BY table_id,
a.object_name,
a.object_type_str
HAVING Count(DISTINCT d.object_id) > 1)
ORDER BY cal_flags,
table_id
Query result example
--1505319;"Mother_1";0;0;16965;"child"--
--1505320;"Mother_2";0;0;16965;"child"
Query result interpretation
This query returns the childs that have more then one mother, in the above example the object "child" have 2 mothers Mother_1 and Mother_2
To avoid this, one work around is to ignore these tables in the build-in parameters of datafunction, but this is not going to correct all situations, especially if previously some of these wrongly merged datafunction were merged manually with in other datafunctions.
Query for Oracle
Enter the SQL query
Query result example
Query result interpretation
Query for SQL server
Enter the SQL query
Query result example
Query result interpretation
Notes/comments
Related Pages
