What formula to use in OTBI to compare two columns, then derive the joined value from a third column?

Question

I'm attempting to write a report where I am connecting replaced assets.

Here is a simplified table of the report as it is currently:

Column A	Column B	Column C	Column D
321	123	124
323	124	null
324	125	null

I am attempting to use a formula to populate Column D. The idea is that when any value in Column C matches any value in Column B, retrieve the value of Column A for the matched value in Column B.

In my example, because 124 is in both Column C and B, I want to populate row 1, Column D with 323.

For context, all columns are from the same table and the columns are automatically selected, the column results are all formatted as plain text, and Column A values will all be distinct, Column B values will also always be distinct, and Column C can have one or more of the same values in Column B, though for this purpose we can assume they are distinct values as well.

Thank you!

I've attempted to use the following (omitting the select and from clauses as they are automatically input):

CASE WHEN "Column C"="Column B" 
THEN "Column A" 
ELSE null END

This only produces the ELSE condition in Column D. I was hoping to get the value of Column A, row 2 because of the matched value in Column B, row 2.

Then I was able to populate Column D with:

CASE WHEN "Column C" 
IN( SELECT "Column B" FROM  "TABLE") 
THEN "COLUMN A" 
ELSE null END

This evaluated the expression correctly and populated Column D, but now with the row's value for Column A. See result below:

Column A	Column B	Column C	Column D
321	123	124	321
323	124	null
324	125	null

I was hoping to populate Column D with 323. How can I get the value of Column A, row 2 into Column D, row 1?

Thank you!

Answer 1

If I got it right, you should join your dataset to itself like below. Added some rows to sample data for testing...

WITH     --  S a m p l e    D a t a :
    tbl ( COL_A,    COL_B,  COL_C ) AS
        ( Select  '321', '123', '124' From Dual Union All
          Select  '323', '124', null  From Dual Union All
          Select  '324', '125', null  From Dual Union All
          Select  '327', '126', '125' From Dual Union All
          Select  '328', '127', '124' From Dual
        )

--    S Q L :
Select     t1.COL_A, t1.COL_B, t1.COL_C,
           t2.COL_A "COL_D"
From       tbl t1
Left Join  tbl t2 ON (t2.COL_B = t1.COL_C)
Order By   t1.COL_A

/*      R e s u l t :
COL_A COL_B COL_C COL_D
----- ----- ----- -----
321   123   124   323
323   124        
324   125        
327   126   125   324   
328   127   124   323   */