SQL model clause - row/set size limitations
In attempting to create a pivot table using the SQL model clause, it appears that there is a limitation on the size of the set being used.
I've simplified my tests to a very simple clause using the following
select asset_id, fiscal_year, period_index, depreciation
from FA_PROJ_INTERIM_8540552 -- table has 500,000 rows in total
where 1=1
-- and book_type_code='WFTC_CORPORATE'
-- and asset_id between 26909 and 26928 -- OK - 1200 rows
-- and asset_id between 25011 and 26909 -- OK - 1400 rows
-- and asset_id between 26909 and 66469 -- no rows returned, more than 4,200 rows
-- and asset_id between 50074 and 59900 -- no rows returned, more than 4,200 rows
I've simplified my tests to a very simple clause using the following
select asset_id, fiscal_year, period_index, depreciation
from FA_PROJ_INTERIM_8540552 -- table has 500,000 rows in total
where 1=1
-- and book_type_code='WFTC_CORPORATE'
-- and asset_id between 26909 and 26928 -- OK - 1200 rows
-- and asset_id between 25011 and 26909 -- OK - 1400 rows
-- and asset_id between 26909 and 66469 -- no rows returned, more than 4,200 rows
-- and asset_id between 50074 and 59900 -- no rows returned, more than 4,200 rows
0