MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » December 2007

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



fixed

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.






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact