How to modify Oracle SQL query to Snowflake

0
votes

Using Oracle SQL, there is a function, noted below, that will allow you to create a "list" of names, phone numbers, etc., without using multiple DUAL queries and UNION/UNION ALL to get more than one record. The query below produces a list in this case of 10 names.

SELECT COLUMN_VALUE USERNAME FROM TABLE(SYS.DBMS_DEBUG_VC2COLL( 'WARNER,JEFF', 'MALITO,CARL', 'MOODY,JEANNE', 'PHILLIPS,HUGH & KELLY', 'PATSANTARAS,VICTORIA', 'BROWN,ROLAND', 'RADOSEVICH,MIKE', 'RIDER,JACK', 'MACLEOD,LENARD', 'SCOTT,DAN' ))

However, when trying to run this same query in Snowflake, it will not work. I receive this error: SQL compilation error: Invalid identifier SYS.DBMS_DEBUG_VC2COLL

Is there a "Snowflake version" of this query that can be used?

1
oraclesnowflake-cloud-data-platform
Have you tried to grant execution rights to your user ? (If in stored procedure/function, it should be granted directly or using dynamic parsing) – igr

1 Answers

1
votes

Here are some options, you can see which works best for you.

  1. This works if you can get your SQL to look similar:

    SELECT $1::VARCHAR AS column_value FROM (VALUES ('WARNER,JEFF'), ('MACLEOD,LENARD'), ('SCOTT,DAN'));

  2. This also works if you can get your list to be in a single string, delimited by a pipe or similar:

    SELECT value::VARCHAR AS column_value FROM LATERAL FLATTEN(INPUT=>SPLIT('WARNER,JEFF|MACLEOD,LENARD|SCOTT,DAN', '|'));

  3. If you have the strings in the format 'a','b' and find it painful to do one of the above, I'd do something like this:

    SELECT value::VARCHAR AS column_value FROM LATERAL FLATTEN(INPUT=>SPLIT(ARRAY_TO_STRING(ARRAY_CONSTRUCT('WARNER,JEFF', 'MALITO,CARL', 'MOODY,JEANNE'), '|'), '|'));

  4. Similar to the above suggestions, you can try this:

    SELECT VALUE::VARCHAR as column_name FROM TABLE(FLATTEN(INPUT => ARRAY_CONSTRUCT('WARNER,JEFF', 'MALITO,CARL', 'MOODY,JEANNE'), MODE => 'array'));