Query of queries disallows SQL right() function

[kejones]

We're attempting to do a query of queries using the SQL right() function like this:

select *
from getresults
where right([key],charindex('\',reverse([key]),1)-1) not in (#quotedvaluelist(getexcluded.file_name)#)

We've even replaced that where clause with a much more simple

where right([key])='m'

just to make sure that it wasn't the nesting functions that were causing the problem.

In either case, we get the error:

Query of Queries syntax error.
Encountered "right" at line 0, column 0. Incorrect conditional expression,
Expected one of [like|null|between|in|comparison] condition,

What SQL functions are disallowed from query of queries?

Thanks,
Kris

[JMGibson3]

Nasty stuff huh. Just happened to discover myself today that Left doesn't work. I'd suspect that Aggregate functions are the ONLY ones that will work. It would have been nice if they'd at least allowed CF vs DB functions in their own "database" language.

BTW, also discovered that Count() returns Null rather than 0 when there aren't any per your WHERE clause.

[CF_Oracle]

It also does not support CASE, DECODE, joins between more than 2 tables, LEFT and RIGHT joints.