Error message when try to run a function in function

Question

I create two functions that worked well as a stand alone functions. But when I try to run one of them inside the second one I got an error: 'SQL compilation error: Unsupported subquery type cannot be evaluated'

Can you advise? Adding code bellow:

CASE  
    WHEN (regexp_substr(ARGUMENTS_JSON,'drives_removed":\\[]') = 'drives_removed":[]') THEN **priceperitem(1998, returnitem_add_item(ARGUMENTS_JSON))**
    WHEN (regexp_substr(ARGUMENTS_JSON,'drives_added":\\[\\]') = 'drives_added":[]') THEN 1
    WHEN (regexp_substr(ARGUMENTS_JSON,'from_flavor') = 'from_flavor') THEN 1
    WHEN (regexp_substr(ARGUMENTS_JSON,'{}') = '{}') THEN 1
    ELSE 'Other'
END as Price_List,

The problem happened when with function 'priceperitem' If I replace returnitem_add_item with a string then it will work fine.

Function 1 : priceperitem get customer number and a item return pricing per the item from customer pricing list
Function 2 : returnitem_add_item parsing string and return a string

Nikita Kulkarni · Accepted Answer · 2020-04-14 17:56:36Z

1

As an alternative, you may try processing udf within a udf in the following way :

create function x()
returns integer
as
$$
select 1+2
$$
;

set q=x();

create function pi_udf(q integer)
  returns integer
  as 
  $$
  select q*3
  $$
  ;

select pi_udf($q);

If this does not work out as well, the issue might be data specific. Try executing the function with one record, and then add more records to see the difference in the behavior. There are certain limitations on using SQL UDF's in Snowflake and hence the 'Unsupported Subquery' Error.

answered Apr 14, 2020 at 17:56

Nikita Kulkarni

491 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error message when try to run a function in function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related