NOT IN SUBQUERIES SQL compilation error: Unsupported subquery type cannot be evaluated

Question

I would like to understand an issue when i am querying two table with pretty similar queries. You can see tables as below:

When i execute this one, i have no errors and it works perfectly:

SELECT
    DISTINCT(A."Ref"),
    A."Period",
    A."Country",
    A."Tag",
    A."Name",
    1 AS "Won",
    0 AS "Lost"
FROM
    "main_table1" A
WHERE
    A."Period" = DATE_TRUNC('MONTH', A."Date Begin")
    AND A."Ref" NOT IN (
        SELECT
            B."Ref"
        FROM
            "main_table1" B
        WHERE
            B."Period" = DATEADD(MONTH, -1, A."Period")
    )

When i execute this one i have the error below the query. Can you telle me why and how workaround this ?

SELECT
    A."Ref",
    A."Name",
    A."Country",
    A."Tag",
    A."Period",
    1 AS "Won",
    0 AS "Lost"
FROM
    "main_table2" A
WHERE
    A."Ref" NOT IN (
        SELECT
            B."Ref"
        FROM
            "main_table2" B
        WHERE
            B."Period" = A."Report_Period_M-1"
    )

SQL compilation error: Unsupported subquery type cannot be evaluated

What is this Report_Preiod_M column? As far as I see there is no such column defined in main_table2,is that right? — Srinath Menon

Mike Walton Mike Walton · Accepted Answer · 2021-05-26T00:17:05

Wouldn't just using a LEFT JOIN directly work?

SELECT
    A."Ref",
    A."Name",
    A."Country",
    A."Tag",
    A."Period",
    1 AS "Won",
    0 AS "Lost"
FROM "main_table2" A
LEFT JOIN "main_table2" B
  ON A."Report_Period_M-1" = B."Period"
WHERE A."Ref" IS NULL

NOT IN SUBQUERIES SQL compilation error: Unsupported subquery type cannot be evaluated

1 Answers