[SOLVED] Operation Not Supported on Non-User Columns?

Operation Not Supported on Non-User Columns?

I have the following SQL code:

# does not work
select count(*), count(distinct p) from 
(select *, rowid as p from elephants)zzz

But I get the following error:

Operation Not Supported on Non-User Columns

The strange thing is that part of this code seems to work just fine:

# works without errors
select *, rowid as p from elephants

Does anyone know what I can do to fix this error? I tried to use a CAST statement to change the type to integer or varchar ... I also tried to create a CTE and then rename the column to "fool" the computer into thinking that this is a user column ... but I keep getting the same error.

What can I do to fix this problem?

Thanks!

References:

https://www.ibm.com/docs/en/psfa/7.2.1?topic=tables-row-ids-in

Solution

This should trick the optimizer ... so that P no longer refers to ROWID specifically. Just add +0 to the ROWID value. A no-op.

select count(*), count(distinct p) from 
(select *, rowid+0 as p from elephants)zzz