Netezza array columns split

1
votes

In Netezza i have single column with below values in table1

Row1: checkOption,checkEquity,checkSubAccount,checkPosition,checkdigital
Row2: checkOption,checkEquity,checkSubAccount
Row3: checkSubAccount,checkPosition,checkdigital

in table2 - single column values as below
Row1: checkOption
Row2: checkEquity
Row3: checkSubAccount
Row4: checkPosition
Row5: checkDigital

I want to compare table1 vs table 2 in Netezza i want only matching rows.
Any suggestion please ?

2
sqlnetezza

2 Answers

0
votes

use like operation to compare the values like '%Option%' looks like that will solve the issue.

0
votes

If you actually have arrays (and not just strings of varchar elements that you're calling arrays), just using a like operator isn't going to cut it. You'll need to combine this idea with array_combine.

Structure

Table1

select array_combine(arr,',') arr from table1;
                                ARR
--------------------------------------------------------------------
 checkSubAccount,checkPosition,checkDigital
 checkOption,checkEquity,checkSubAccount,checkPosition,checkDigital
 checkOption,checkEquity,checkSubAccount

Table2

select * from table2;

       VAL
-----------------
 checkDigital
 checkOption
 checkEquity
 checkPosition
 checkSubAccount

Now, to get the output you want, you'll need to join using array_combine to create a string.

select 
  val
  ,array_combine(arr,',') arr 
from 
  table1 tb1 
  join table2 tb2 on 
    array_combine(tb1.arr,',') like '%' || tb2.val || '%' 
order by 1;

       VAL       |                                ARR
-----------------+--------------------------------------------------------------------
 checkDigital    | checkOption,checkEquity,checkSubAccount,checkPosition,checkDigital
 checkDigital    | checkSubAccount,checkPosition,checkDigital
 checkEquity     | checkOption,checkEquity,checkSubAccount
 checkEquity     | checkOption,checkEquity,checkSubAccount,checkPosition,checkDigital
 checkOption     | checkOption,checkEquity,checkSubAccount,checkPosition,checkDigital
 checkOption     | checkOption,checkEquity,checkSubAccount
 checkPosition   | checkSubAccount,checkPosition,checkDigital
 checkPosition   | checkOption,checkEquity,checkSubAccount,checkPosition,checkDigital
 checkSubAccount | checkOption,checkEquity,checkSubAccount,checkPosition,checkDigital
 checkSubAccount | checkOption,checkEquity,checkSubAccount
 checkSubAccount | checkSubAccount,checkPosition,checkDigital