I'm getting an error from this:

* select * from points p
* where not exists ( select *
* from spent_points sp
* where p.offender_nr = sp.offender_nr
* and p.offence = sp.offence
* and p.offence_date = sp.offence_date )
* and date('today')-p.points_effective_from >= '1 year'+
* (select sum(pr.stop_effective_until-
ps.stop_effective_from)
* from point_stops ps, point_restarts pr
* where ps.offender_nr = p.offender_nr
* and ps.offender_nr = pr.offender_nr
* and ps.stop_effective_from >= p.points_effective_from)
* \g
Executing . . .

E_OP0280 consistency check
- scoping rules for variables used in query were violated
(Thu Nov 18 11:34:37 2010)

continue
*

I don't see any variables. And if there are, what are the scoping
rules? Ingres version II 10.1.0 (int.w32/105).

Art

What you've stumbled upon appears to be a bug in scalar subqueries. The
"variables" in the error message are probably referring to the
correlation variables in the scalar subquery.

Interestingly, I was able to get your query to execute by switching the
*-+ '1 year'-* expression to a *-- '1 year'-* expression, as shown
below:


Code:
--------------------

select * from points p
where not exists ( select *
from spent_points sp
where p.offender_nr = sp.offender_nr
and p.offence = sp.offence
and p.offence_date = sp.offence_date )
and date('today')-p.points_effective_from - '1 year' >=
(select sum(pr.stop_effective_until-
ps.stop_effective_from)
from point_stops ps, point_restarts pr
where ps.offender_nr = p.offender_nr
and ps.offender_nr = pr.offender_nr
and ps.stop_effective_from >= p.points_effective_from)
\g

--------------------


--
zelaine

Note that I've logged ServiceDesk issue 147921 for this problem.


--
zelaine

Wow thank you. Completely random but heh, if it works why not? Why
did you try that?

On Nov 18, 5:16 pm, Ingres Forums <info-
ing... (AT) kettleriverconsulting (DOT) com> wrote:

Artemus Ward;34943 Wrote:
Lucky guess . Actually, I was trying to narrow down the query to the
minimal query that would reproduce the problem and in doing so, I
noticed that when I took out the "+ '1 year'" expression, the problem
went away. So, I wondered if rewriting the query to avoid that
expression would work, and oddly enough it did.


--
zelaine

The application I work on is considering upgrading from Ingres 9.2 to
Ingres 10. I have a test Ingres 10 installation that I've been playing
around with and I'm experiencing this problem.

Zelaine, you said that you raised a "Service Desk issue147921". How,
does one track these sorts of problems to find out when they are fixed?
I understand that maybe by restructuring my query that I may be able to
work around the problem. However, the query that is having this issue is
a rather large query that would take quite some time to restructure.

Sorry for the noobish questions. I generally keep my head in Java code
only. :P


--
Kragoth235