Snowflake subquery

3
votes

I have two tables. Transaction(ID, TERMINALID) and Terminal(ID, TERMINALID, EXPORT_DATE). The goal is to obtain for each row from Transaction table newest recored from Terminal table. Snowflake is used as a backend.

I have this SQL query:

SELECT tr.ID,
       (SELECT te.ID
        FROM "Terminal" te
        WHERE te.TERMINALID = tr.TERMINALID
        ORDER BY te.EXPORT_DATE DESC
        LIMIT 1)
FROM "Transaction" tr;

But I get this error:

SQL compilation error: Unsupported subquery type cannot be evaluated

Error disappears if I replace tr.TERMINALID with a specific value. So I can't reference parent table from nested SELECT. Why this is not possible? Query works in MySQL.

2
sqldatabasepostgresqlsnowflake-cloud-data-platform
Ist the subquery supposed to be a scalar subquery? BTW: add a column alias to the subquery. - wildplasser
Yes, subquery shoud return one column value from one row. Therefore, there is the LIMIT. - michal4
Please add the table definitions for the tables to your question. (Are the table column names really mixed/upper cased?) - wildplasser
Yes, names are ok. Every column is VARCHAR(16777216) with no primary or unique keys. - michal4
ERROR: length for type varchar cannot exceed 10485760 BTW: you dont have to specify a size for varchar(). - wildplasser

2 Answers

11
votes

I'm afraid Snowflake doesn't support correlated subqueries of this kind.

You can achieve what you want by using FIRST_VALUE to compute best per-terminalid id :

-- First compute per-terminalid best id
with sub1 as (
  select 
    terminalid, 
    first_value(id) over (partition by terminalid order by d desc) id
  from terminal 
),
-- Now, make sure there's only one per terminalid id
sub2 as (
  select 
    terminalid, 
    any_value(id) id
  from sub1
  group by terminalid
)
-- Now use that result
select tr.ID, sub2.id
FROM "Transaction" tr
JOIN sub2 ON tr.terminalid = sub2.terminalid

You can run subqueries first to see what they do.

We're working on making our support for subqueries better, and possibly there's a simpler rewrite, but I hope it helps.

0
votes
SELECT
tr.ID
  , (SELECT te.ID
     FROM "Terminal" te 
     WHERE te.TERMINALID = tr.TERMINALID
     ORDER BY te.EXPORT_DATE DESC
     LIMIT 1
    ) AS the_id -- <<-- add an alias for the column
FROM "Transaction" tr
    ;

UPDATE:

  • length for type varchar cannot exceed 10485760
  • just use type varchar (or text) instead

Works here (with quoted identifiers):

CREATE TABLE "Transaction" ("ID" VARCHAR(123), "TERMINALID"  VARCHAR(123)) ;
CREATE TABLE "Terminal" ( "ID"  VARCHAR(123), "TERMINALID"  VARCHAR(123), "EXPORT_DATE" DATE);

SELECT tr."ID"
        , (SELECT te."ID"
        FROM "Terminal" te
        WHERE te."TERMINALID" = tr."TERMINALID"
        ORDER BY te."EXPORT_DATE" DESC
        LIMIT 1) AS meuk
FROM "Transaction" tr
        ;

BONUS UPDATE: avoid the scalar subquery and use plain old NOT EXISTS(...) to obtain the record with the most recent date:

SELECT tr."ID"
        , te."ID" AS meuk
FROM "Transaction" tr
JOIN "Terminal" te ON te."TERMINALID" = tr."TERMINALID"
        AND NOT EXISTS ( SELECT *
        FROM "Terminal" nx
        WHERE nx."TERMINALID" = te."TERMINALID"
        AND nx."EXPORT_DATE" > te."EXPORT_DATE"
        )
        ;