S02037

Error Code

S02037

Error Message

The '[not] in' predicate cannot be followed by columns from the partitioned table. Please use a subquery instead. RefId:S02037

Probable Causes

The [not] in predicate does not allow you to specify values from a partitioned table.

For example,

dbName = "dfs://test"
if (existsDatabase(dbName)) {
    dropDatabase(dbName)
}

promotion = table(1..10 as id, 1..10 as relatedId);

db = database(dbName, VALUE, 1..10)
promotionPt = db.createPartitionedTable(promotion, `promotionPt, `id)
promotionPt.append!(promotion)

select * from promotionPt where id in relatedId
select id, id in relatedId as related from promotionPt
select first(id) from promotionPt group by id in relatedId
select * from promotionPt order by id in relatedId

// not in
select * from promotionPt where id not in relatedId

item = table(1..3 as id)
// join
select * from item inner join promotionPt on item.id=promotionPt.id where id in relatedId

Solutions

Make sure that values from the partitioned table are not used after the [not] in predicate.