Getting unsupported subquery type when trying to insert into a table

Question

I have a query as follows:

INSERT ALL
    WHEN NEWEST_ID IS NOT NULL AND 
         (SELECT COUNT(1) FROM (
                              SELECT * 
                              FROM MY_TABLE 
                              WHERE ID = NEWEST_ID 
                              QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY OFFSET DESC) = 1
                                ) 
          WHERE ACTIVE) = 0 THEN 
      INTO MY_TABLE VALUES(
        NEWEST_ID,
        CURRENT_DATE,
        NAME,
        FALSE
   )
SELECT * FROM TEST_TABLE;

However I am getting an unsupported subquery type error when I try to write the select count(1) or count(*) from the subquery. Why is this so?/ How can I change this? In my subquery I am just trying to get the first row in a group of IDs after ordering by the descending offset. And then I am trying to determine whether the ACTIVE column from that result row is TRUE.

Answer 1

the QUALIFY can have the WHERE ACTIVE added to it:

SELECT COUNT(1) 
FROM (
    SELECT 1
    FROM MY_TABLE as x
    WHERE x.ID = NEWEST_ID 
    QUALIFY ROW_NUMBER() OVER (PARTITION BY x.ID ORDER BY x.OFFSET DESC) = 1 AND x.ACTIVE
)

this the inner only keeps the "last" offset per id AND if it is also active

the count = 0 can be turned into a NOT EXIST like:

INSERT ALL
    WHEN newest_id IS NOT NULL 
        AND NOT EXISTS (
            SELECT 1
            FROM my_table AS x
            WHERE x.id = newest_id 
            QUALIFY ROW_NUMBER() OVER (PARTITION BY x.id ORDER BY x.offset desc) = 1 AND x.active
            
        ) THEN 
    INTO my_table VALUES( newest_id, current_date, name, false)
  
SELECT * FROM TEST_TABLE;

"in theory"

the other option is to push that into a CTE:

WITH last_id_active AS (
    SELECT x.id
    FROM my_table AS x
    QUALIFY 
        ROW_NUMBER() OVER (PARTITION BY x.id ORDER BY x.offset desc) = 1 
        AND x.active
)

which would need to be on the SELECT like:

INSERT ALL
    WHEN tt.newest_id IS NOT NULL 
        AND lia.id IS NOT NULL THEN 
    INTO my_table VALUES( tt.newest_id, tt.current_date, tt.name, false)
    
WITH last_id_active AS (
    SELECT x.id
    FROM my_table AS x
    QUALIFY 
        ROW_NUMBER() OVER (PARTITION BY x.id ORDER BY x.offset desc) = 1 
        AND x.active
)  
SELECT * FROM TEST_TABLE as tt
LEFT JOIN last_id_active as lia 
    ON tt.newest_id = lia.id;

*based on theory

which could also be simplyfied, as lia.id will be null when tt.newest_id is also null, thus the INSERT_ALL could be simplefied more as:

INSERT ALL
    WHEN lia.id IS NOT NULL THEN 
    INTO my_table VALUES( tt.newest_id, tt.current_date, tt.name, false)
    
WITH last_id_active AS (
    SELECT x.id
    FROM my_table AS x
    QUALIFY 
        ROW_NUMBER() OVER (PARTITION BY x.id ORDER BY x.offset desc) = 1 
        AND x.active
)  
SELECT * FROM TEST_TABLE as tt
LEFT JOIN last_id_active as lia 
    ON tt.newest_id = lia.id;