I have a column named link_name. It contains id of two assets like (123_456). I need to check if the voltage(have a column in main table) of asset 123 matches with voltage of asset 456.

I'm unable to find any solution for this.

CodePudding user response：

If your sample data looks like ...

WITH
    tbl AS
        (
            Select '123' "ASSET", 110 "VOLTAGE", 'Some other column(s)' "COL_3" From Dual Union All
            Select '234' "ASSET", 220 "VOLTAGE", 'Some other column(s)' "COL_3" From Dual Union All
            Select '345' "ASSET", 360 "VOLTAGE", 'Some other column(s)' "COL_3" From Dual Union All
            Select '456' "ASSET", 110 "VOLTAGE", 'Some other column(s)' "COL_3" From Dual Union All
            Select '567' "ASSET", 220 "VOLTAGE", 'Some other column(s)' "COL_3" From Dual 
        ),
    assets AS
        (
            Select 1 "ID", 'Name for ID 1' "A_NAME", '123_234' "LINK_NAME" From Dual Union All
            Select 2 "ID", 'Name for ID 2' "A_NAME", '345_456' "LINK_NAME" From Dual Union All
            Select 3 "ID", 'Name for ID 3' "A_NAME", '123_456' "LINK_NAME" From Dual Union All
            Select 4 "ID", 'Name for ID 4' "A_NAME", '234_567' "LINK_NAME" From Dual 
        )

You can use SUBSTR() function to join two assets from yout table using two JOIN ON conditions.

Select
    a.LINK_NAME, 
    SubStr(a.LINK_NAME, 1, InStr(a.LINK_NAME, '_') - 1) "ASSET_1",  
    SubStr(a.LINK_NAME, InStr(a.LINK_NAME, '_')   1) "ASSET_2",
    t1.VOLTAGE "VOLTAGE_1",
    t2.VOLTAGE "VOLTAGE_2",
    CASE WHEN t1.VOLTAGE = t2.VOLTAGE THEN 'Y' ELSE 'N' END "MATCH"
From
    assets a
Inner Join
    tbl t1 ON(t1.ASSET = SubStr(a.LINK_NAME, 1, InStr(a.LINK_NAME, '_') - 1))
Inner Join
    tbl t2 ON(t2.ASSET = SubStr(a.LINK_NAME, InStr(a.LINK_NAME, '_')   1))
Order By
  a.LINK_NAME

R e s u l t :

CodePudding user response：

You should normalize your data model: having IDs as part of a string to do any relational operation is the worst idea.