snowflake sql - lateral flatten function compare with sql server - Split_String()

user_1810575 0
Viewed 86

I have task to compare the counts and see if it's > 0

snowflake sql below

select 'abc'
from tableA
where 
(SELECT count(T1.value) as val
from tableA,
    lateral flatten(input=>split(abc, ',')) T1
    INNER JOIN (select count(T2.value) as val from 
lateral flatten(input=>split('1,2', ','))) as T2 on T1.val = T2.val) > 0

here is the error msg

SQL compilation error: error line 7 at position 29 invalid identifier 'T2.VALUE'

Sql server comparison that works.

(select Count(T1.value) as [Count] from STRING_SPLIT(abc,',') AS T1
    INNER JOIN STRING_SPLIT('9,10', ',') AS T2 on T1.value = T2.value))>0
snowflake-schema snowflake-cloud-data-platform sql-server-2008-r2
2 Answers

You might need to reposition like this -

select 'abc'
from tableA
where
(select count(*) from
(SELECT count(flat_tab_alias1.value) as val
from tableA,
lateral flatten(input=>split(abc, ',')) flat_tab_alias1
) as T1
INNER JOIN
(
select count(flat_tab_alias2.value) as val from
lateral flatten(input=>split('1,2', ',')) flat_tab_alias2
) as T2
on T1.val = T2.val) > 0;
'ABC' 
0 Row(s) produced.

With following source table -

select * from tablea;
ABC
a,b,c,d,e,f,g
user_139153130

You SQL has many problems, but given we have working T-SQL lets decode that.

(select Count(T1.value) as [Count] from STRING_SPLIT(abc,',') AS T1
    INNER JOIN STRING_SPLIT('9,10', ',') AS T2 on T1.value = T2.value))>0

This code is splitting a string array, and joining it to a set of "known" values, and counting how mean are equal, and just asking if there is any overlap.

ARRAY_OVERLAP answer this, and using SPLIT to make the arrays:

SELECT abc, 
    split(abc, ',') as a1,
    split('1,2', ',') as a2,
    ARRAYS_OVERLAP( a1, a2)
FROM values
    ('1'),
    ('0,4'),
    ('0,1,5')
    t(abc);

gives:

ABC A1 A2 ARRAYS_OVERLAP( A1, A2)
1 [ "1" ] [ "1", "2" ] TRUE
0,4 [ "0", "4" ] [ "1", "2" ] FALSE
0,1,5 [ "0", "1", "5" ] [ "1", "2" ] TRUE

So your SQL should be:

SELECT 'abc', abc
FROM tableA
WHERE ARRAYS_OVERLAP( split(abc,','), split('1,2',','));

which with the dummy data:

    select * from values
    ('1'),
    ('0,4'),
    ('0,1,5')

gives:

'ABC' ABC
abc 1
abc 0,1,5
user_439920
Related