sql subquery where in does not work ( at snowflake)-CodePudding

I try to create a table with moving current three month by using subquery for only selecting current 3 months.

I have

`select * from dataTable where month in 
(select max(month),max(month)-1,max(month)-2 from month_table);`

snowflake returns me this error msg SQL compilation error: error line 4 at position 26 Invalid argument types for function '=': (NUMBER(6,0), ROW(NUMBER(6,0), NUMBER(7,0), NUMBER(7,0)))

not sure what I miss. when I ran a query for that 3 current months by

select max(month),max(month)-1,max(month)-2 from month_table

it does return three months enter image description here

any idea? or any alternative approach for getting data with moving three months is appreciated.

thanks

btw, subquery works only in one month eg: select xxxx from (select max(month) from xxx) so I am now using a clumsy union for 3 queries select xxx from max(month) union select xxx from max(month)-1 union select xxx from max(month)-2...

so any better efficient approach is appreciated.

CodePudding user response：

Sub query for In clause requires rows with each value you want to look up. So, Please try the following:

select * from dataTable where month in 
(select max(month)   from month_table union all
 select max(month)-1 from month_table union all
 select max(month)-2 from month_table
);

CodePudding user response：

All you need to do is put a subquery in your where clause to get the max. (Optionally) create some test data based on your picture:

create or replace table dataTable as
select replace(left(dateadd('month', -1 * (row_number() over (order by 1) - 1), '2022-11-01')::string,7), '-', '')::int CALENDAR_YEAR_MONTH
from table(generator(rowcount=>100));

Now just put a subquery in the where clause to get how many months you want in your result:

select * from dataTable
where CALENDAR_YEAR_MONTH > (select max(CALENDAR_YEAR_MONTH) - 3 from dataTable)
;

CALENDAR_YEAR_MONTH
202211
202210
202209

CodePudding user response：

When using IN:

where month in 
(select max(month),max(month)-1,max(month)-2 from month_table)

you are trying to perform:

WHERE (col) IN ((val1, val2, val3))

and by definition single element cannot be equal to 3-elements tuple.

IN:

( <value_A> [, <value_B> ... ] ) [ NOT ] IN (  ( <value_1> [ , <value_2> ... ] )  [ , ( <value_3> [ , <value_4> ... ] )  ...  ]  )
value_A, value_B

The elements of a row constructor for which to search.

Ensure that each value on the right of IN (e.g. (value3, value4)) has the same number of elements as the value on the left of IN (e.g. (value_A, value_B)).

To make it work the query should be organized to match:

WHERE (col) IN ((val1), (val2), (val3))

i.e.,

WHERE month IN (SELECT max(month) FROM month_table UNION
                SELECT max(month)-1 FROM month_table UNION
                SELECT max(month)-2 FROM month_table)

or simpler as:

WHERE month BETWEEN (SELECT max(month)-2 FROM month_table) 
                AND (SELECT max(month)   FROM month_table)

CodePudding user response：

How about using variables? That way you can pass those months as a list

set (m0,m1,m2) = (select max(month),max(month)-1,max(month)-2 from month_table);

select * 
from dataTable 
where month in ($m0,$m1,$m2);