SQL Queries - CAST Knowledge Base - Queries on objects - How to find out if database objects belong to the same extraction

Purpose of Query

The purpose of the query is to find out if two objects belong to the same database extraction. If the results of the query are two identical lines, then they belong to the same extraction.

Applicable CAST Version

Release	Yes/No
8.3.x	✅
8.2.x	✅
8.1.x	✅
8.0.x	✅

Applicable RDBMS

RDBMS	Yes/No
Oracle Server	✅
Microsoft SQL Server	✅
CSS2	✅

Query for CSS

SELECT reverse(substring(Reverse(rp.path),position ('\' IN reverse(rp.path)))) 
FROM   refpath rp 
JOIN   objfilref ofr 
ON     ofr.idfilref = rp.idfilref 
WHERE  ofr.idobj IN (<object_id_1>, 
                     <object_id_2>)

Query result example

“S:\Sources\DB\APP"
“S:\Sources\DB\APP"

Query result interpretation

The objects are from the same Database extraction because the two lines are identical

Query for Oracle

SELECT reverse(substring(Reverse(rp.path),position ('\' IN reverse(rp.path)))) 
FROM   refpath rp 
JOIN   objfilref ofr 
ON     ofr.idfilref = rp.idfilref 
WHERE  ofr.idobj IN (<object_id_1>, 
                     <object_id_2>)

Query result example

“S:\Sources\DB\APP"
“S:\Sources\DB\APP"

Query result interpretation

The objects are from the same Database extraction because the two lines are identical

Query for SQL server

SELECT reverse(substring(Reverse(rp.path),position ('\' IN reverse(rp.path)))) 
FROM   refpath rp 
JOIN   objfilref ofr 
ON     ofr.idfilref = rp.idfilref 
WHERE  ofr.idobj IN (<object_id_1>, 
                     <object_id_2>)

Query result example

“S:\Sources\DB\APP"
“S:\Sources\DB\APP"

Query result interpretation

The objects are from the same Database extraction because the two lines are identical

Notes/comments