ASPEN SQL Script to compare two tags glossaries

Question

What I want to do is bring up a list of all tags in a table (IP_AIDef and IP_DIDef) that contains "FDL" (fast data logged) in the 'name' along with their corresponding glossaries (ip_description). What I need to do is also bring up the ip_description of the same non-fdl tags and compare them. For example, if there is a "FDL_A_AI_0001' then I want to compare glossaries (ip_description) "FDL_A_AI_0001" to "A_AI_0001" and manually correct any glossaries that are incorrect.

I currently use:

SELECT Name AS FDL_Tagname, IP_Description AS Glossary
FROM IP_AIDef
WHERE Name LIKE '%FDL%'
UNION
SELECT Name AS FDL_Tagname, IP_Description AS Glossary
FROM IP_DIDef
WHERE Name LIKE '%FDL%' ORDER BY FDL_TagName

which returns hundreds of FDL tags in the table (I've masked the IP_DESCRIPTIONS). I'd like to add another column to the right to show the NAME and IP_DESCRIPTION for the non FDL version of the tag such as 'A_AOT_0224' and 'A_AC_1300' etc. I'm guessing I need to use a nested query and possibly a LEFT() or RIGHT() that removes the 'FDL_' from the initial query results and then a JOIN.

**Current Output:**

FDL_Tagname               Glossary
------------------------ -----------------------------------
FDL_A_AOT_0224           xxxxxxxxx
FDL_A_AC_1300            xxxxxxxxx
FDL_A_AC_2000            xxxxxxxxx
FDL_A_AO_0403            xxxxxxxxx
FDL_B_AC_0126            xxxxxxxxx


**My desired outcome would look like:**

FDL_Tagname             Glossary       NON_FDL_Tagname    Glossary   
------------------------ --------------------------------------------
FDL_A_AOT_0224           xxxxxxxxx        A_AOT_0224     xxxxxxxxxx
FDL_A_AC_1300            xxxxxxxxx        A_AC_1300      xxxxxxxxxx
FDL_A_AC_2000            xxxxxxxxx        A_AC_2000      xxxxxxxxxx
FDL_A_AO_0403            xxxxxxxxx        A_AO_0403      xxxxxxxxxx
FDL_B_AC_0126            xxxxxxxxx        B_AC_0126      xxxxxxxxxx

Answer 1

here is an example that use indirection, and REPLACE function. Note that this query don't check if record 'NON_FDL_Tagname' actually exists.

SELECT
    NAME AS FDL_Tagname
    , NAME->IP_Description AS Glossary
    , REPLACE('FDL_' WITH '' IN FDL_Tagname) AS NON_FDL_Tagname
FROM all_records
WHERE
    definition in ('IP_AIDef', 'IP_DIDef')
    AND NAME LIKE '%FDL%'
ORDER BY FDL_Tagname;