UDTF Error- SQL compilation error: Unsupported subquery type cannot be evaluated

Question

I created a User-Defined table function that translates part numbers for our company. Part numbers are 15-19 digit alphanumeric strings that give us different information about each product. A common comparison would be an Automotive VIN number. The one key difference is that, over the years, the structure of these numbers has changed. So, one year the color might be characters 11 & 12, and the next it might be 15 & 16. In addition, codes might have different meanings for different years. For instance, the code "BL" might be blue for one year, but the next it could be light blue. It's not ideal, but it's the hand I have been dealt. To decode the part numbers, I created a User Defined Table Function. The logic for this originally existed in MS SQL. It has procedural logic, but that is not allowed in UDTFs in Snowflake. So, I got creative and rewrote the function using CTEs. It is complicated because I have one table that identifies the structure of the part number, and another table that can match the codes to get the matching description based on any dependencies.

The UDTF runs successfully when I hardcode a value: SELECT * FROM TABLE(fDecodeSmartNumber('A19GELEXXXXGRGG'));

It also runs successfully if I join it with a table, but no values are returned:

SELECT sr.Company, sr.RMANum, sr.OrderNumber, d.FieldValue
    FROM
        tRMADataFinal sr
    JOIN
        table(fDecodeSmartNumber(sr.PartNumber)) d
    WHERE
        OrderNUmber IS NOT NULL AND
        WAVLine IS NULL AND
        Make IS NULL AND
        FieldName = 'Make' AND
        FieldValue IS NOT NULL

However, if I add fields to the RMA data that would not be filtered by the where clause, it errors.

I am curious if this is the same error seen here: https://docs.snowflake.com/en/sql-reference/udf-table-functions.html

However, I do not think that workaround will work for me because I need multiple join conditions in the "on" clause of my left join in the function.

Full function code:

CREATE OR REPLACE FUNCTION fDecodePartNumber
(
  vPartNumber varchar
)
RETURNS TABLE
(
    Code VARCHAR(25),
    FieldName VARCHAR(200),
    FieldValue VARCHAR(200)
)
AS
$$


with SNValues as (
  SELECT 
      FieldName,
      SUBSTRING(vPartNumber,Position,Length) as SNValue
  FROM 
      SmartDecodeParts
  WHERE
      SNFormat = (SELECT CASE WHEN SUBSTRING(vPartNumber,2,2) >= 18 THEN '2018' ELSE '2017' END)
),
FirstResultSet as (
  SELECT
      V.SNValue as Code, V.FieldName, K.Dsc, K.Dependency, K.Dvalue
  FROM
      SNValues V
  LEFT JOIN
      SmartDecodeKeys K
  ON
      V.SNValue = K.Code AND
      V.FieldName = K.FieldName 
),
extColor as (   
  SELECT 
      N.Code,
      REPLACE(N.FieldName,' ','') as FieldName,
      Color,
      STANDARD_COLOR,
      C.Dependency,
      C.DValue 
  FROM 
      FirstResultSet N
  INNER JOIN
      ColorTranslation C
  ON
      N.Code = C.SMART_CODE
  INNER JOIN
      FirstResultSet R
  ON
      R.Dsc = C.STANDARD_MAKE 
  WHERE
      N.FieldName = 'Ext Color'
),
intColor as (   
  SELECT 
      N.Code,
      Replace(N.FieldName,' ','') as FieldName,
      Color,
      STANDARD_COLOR,
      C.Dependency,
      C.DValue 
  FROM 
      FirstResultSet N
  INNER JOIN
      ColorTranslationInterior C
  ON
      N.Code = LEFT(C.SMART_CODE,(SELECT Length FROM SmartDecodeParts WHERE FieldName = 'Int Color' AND SNFormat = (SELECT CASE WHEN SUBSTRING(vPartNumber,2,2) >= 18 THEN '2018' ELSE '2017' END)))
  INNER JOIN
      FirstResultSet R
  ON
      R.Dsc = C.STANDARD_MAKE 
  WHERE
      N.FieldName = 'Int Color'
),
seatColor as (  
  SELECT 
      N.Code,
      REPLACE(N.FieldName,' ','') as FieldName,
      Color,
      STANDARD_COLOR,
      C.Dependency,
      C.DValue 
  FROM 
      FirstResultSet N
  INNER JOIN
      ColorTranslationSeat C
  ON
      N.Code = C.SMART_CODE
  INNER JOIN
      FirstResultSet R
  ON
      R.Dsc = C.STANDARD_MAKE 
  WHERE
      N.FieldName = 'Seat Color'
),
NormalizedResult as (
  SELECT * FROM FirstResultSet
  UNION ALL
  Select Code, FieldName, Color, Dependency, DValue FROM extColor
  UNION ALL
  SELECT Code, 'StdExtColor', STANDARD_COLOR, Dependency, DValue FROM extColor
  UNION ALL
  Select Code, FieldName, Color, Dependency, DValue FROM intColor
  UNION ALL
  SELECT Code, 'StdIntColor', STANDARD_COLOR, Dependency, DValue FROM intColor
  UNION ALL
  Select Code, FieldName, Color, Dependency, DValue FROM seatColor
  UNION ALL
  SELECT Code, 'StdSeatColor', STANDARD_COLOR, Dependency, DValue FROM seatColor
),
NormalizedWithDependencies as (
  SELECT
      Code, FieldName, Dsc, Dependency, m.value::string as DValue
  FROM
      NormalizedResult,
          lateral flatten(input=>split(DValue, ',')) m
  UNION ALL
  SELECT
    *
  FROM
      NormalizedResult
  WHERE
      DValue IS NULL
)
SELECT 
    D.Code,
    REPLACE(D.FieldName,' ',''),
    D.Dsc
FROM
    NormalizedWithDependencies D
LEFT JOIN
    NormalizedWithDependencies R
ON
    CASE WHEN LEFT(D.DValue,1) = '!' AND SUBSTRING(D.DValue,2,LEN(R.DValue)-1) <> R.Code THEN 1
    WHEN LEFT(D.DValue,1) <> '!' AND D.DValue = R.Code THEN 1 
    ELSE 0 END = 1 AND
    D.Dependency = R.FieldName 
LEFT JOIN
    NormalizedWithDependencies R2
ON
    CASE WHEN LEFT(R.DValue,1) = '!' AND SUBSTRING(R.DValue,2,LEN(R.DValue)-1) <> R2.Code THEN 1
    WHEN LEFT(R.DValue,1) <> '!' AND R.DValue = R2.Code THEN 1 
    ELSE 0 END = 1 AND
    R.Dependency = R2.FieldName 
WHERE
    (D.Dependency IS NULL OR
    (R.Code IS NOT NULL AND R.Dependency IS NULL) OR
    (R.Code IS NOT NULL AND R2.Code IS NOT NULL)) AND
    D.FieldName <> 'Ext Color' AND
    D.FieldName <> 'Int Color' AND
    D.FieldName <> 'Seat Color'

$$
;

Any ideas?

Simeon Pilgrim Simeon Pilgrim · Accepted Answer · 2020-03-31T01:09:57

You are joining a table function which probably should be a user defined function not a UDTF as you should only get one decoding per VIN.

But anyways the function turns into a correlated subquery which Snowflake does not support, thus the error message.

UDTF Error- SQL compilation error: Unsupported subquery type cannot be evaluated

1 Answers