CODEWITHSUNDEEP
sqloracle-database
user3096487
user3096487

Reputation: 399

Oracle check column in row before for a match

I am trying to create a query where I find a match on the substring of two different columns. I can get my results with a "with" table and then querying those results but it is slow.
Row2 is what the search condition will look for. To simplifiy Row2 columnC=1 Row2 columnB holds the value that the substring is taken out of to check to see if the Row1 columnA subtring= Row2 columnB substring. Any advice or thoughts are appreciated.

Upvotes: 0

Views: 132

Answers (1)

stefan
stefan

Reputation: 2252

Maybe you can use LAG() for this purpose (see documentation). Example:

Table & data

create table t1 ( column_a, column_b  )
as
select 'string100', 'string200' from dual union
select 'string200', 'string100' from dual union
select 'string300', 'string100' from dual union
select 'string400', 'string500' from dual union
select 'string500', 'string400' from dual ;

Find "preceding" values with LAG()

select
  column_a
, column_b
, lag ( column_a, 1, 0 ) over ( order by column_a ) preceding_a
from t1 ;

COLUMN_A   COLUMN_B   PRECEDING_A  
string100  string200  0            
string200  string100  string100    
string300  string100  string200    
string400  string500  string300    
string500  string400  string400

Final query and results

-- Check: is Row1 columnA subtring = Row2 columnB substring
select column_a, column_b,
  case
    when substr( preceding_a, 7, 1 ) = substr( column_b, 7, 1 ) then
      'substring Row1 columnA = substring Row2 columnB'
    else
      'substrings don''t match'
  end compare_substrings
from (
  select
    column_a
  , column_b
  , lag ( column_a, 1, 0 ) over ( order by column_a ) preceding_a
  from t1
) ;

COLUMN_A   COLUMN_B   COMPARE_SUBSTRINGS                               
string100  string200  substrings don't match                           
string200  string100  substring Row1 columnA = substring Row2 columnB  
string300  string100  substrings don't match                           
string400  string500  substrings don't match                           
string500  string400  substring Row1 columnA = substring Row2 columnB

You can probably make the "final query" more compact (ie without writing an inline view). Dbfiddle here.

Upvotes: 1

Related Questions