AW: RANK/ROWNUMBER - Function not supported for query?

Hi,

Even with a CTE the query will be rerouted to the CQE because there are
logical files with select/omit clauses built over the physical file you use.

The problem are the logical files with select/omit. The optimizer tries to
estimate ALL available access path in either DDS desrcibed logical files and
SQL indexes. The SQE is made for SQL and SQL only knows views (logical files
without keys) and indexes which consist only of key fields, that means it
cannot analyze DDS described logical files that do not fit this schema, but
the CQE can. In this way the query must be executed by the CQE, as long as
you won't allow the query optimizer to ignore access path that cannot be
analyzed by the SQE.

Specifying INGNORE_DERIVED_INDEX *YES in the QAQQINI-file, allows the
optimizer to ignore access paths stored in DDS described logical files with
select/omit clauses. That means the SQE can execute the query.

Just try the query with a new file (or an other file without any logical
files with select/omit clauses).

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Ron Adams
Gesendet: Thursday, December 13, 2007 17:28
An: Midrange Systems Technical Discussion
Betreff: Re: RANK/ROWNUMBER - Function not supported for query?


DOPORDM0 is a DDS physical. There are DDS logicals with Select/Omits. So, I
tried using a CTE, but still the same error, " Function not supported for
query"

with cte1 as
(select
ORDNO, CSTNO, ORDAMT
FROM ccsdtatest.dopordm0
where ORDNO like 'CU%')
SELECT RANK() OVER (ORDER BY ORDAMT DESC) as NRANK,
ORDNO, CSTNO, ORDAMT from CTE1

I'm going to assume at this point that I'm missing a few PTFs. I'm a bit
behind on loading these.