Are multiple correlated queries unsupported in Netezza?

Question

This might be easier to explain with the code first, so I'm currently using something that looks like this:

'A' in
    (
        SELECT DISTINCT b.col1
        FROM table b
        WHERE b.ky1 = a.ky1
            AND b.ky2 = a.ky2
            AND b.group = a.group
            AND b.rowNum < a.rowNum
    )

in a where clause. Essentially I'm trying to see if A is a value at any point prior to a row specified by the outer query. This works on its own. The issue is when I add the following to the query:

OR
'S' in
    (
        SELECT DISTINCT b.col1
        FROM tableb b
        WHERE b.ky1 = a.ky1
            AND b.ky2 = a.ky2
            AND b.group = a.group
            AND b.rowNum < a.rowNum
    )

Now, Netezza produces the following error message:

ERROR:  (2) This form of correlated query is not supported - consider rewriting

I have a feeling the only way around this would be to do some sort of join, but I'm not exactly sure how to do that without producing a lot of duplicates. Any suggestions?

@jarlh: That'd prevent exact duplicates, but not partial matches (where a particular record has `'A'` and `'S'` for `b.col1` in my case). — charles, Apr 02 '15 at 12:15
There might be a better way to write this query. Consider asking another question with sample data and desired results -- describing what you really want to do. If your query is really complicated, try to simplify it to focus on this aspect. — Gordon Linoff, Apr 02 '15 at 12:45
@GordonLinoff: I could do that, but that wouldn't answer the question I asked. — charles, Apr 02 '15 at 14:44

score 1 · Accepted Answer · answered Apr 02 '15 at 17:51

Correlated subqueries in Netezza, as of version 7.2, have the following usage considerations, as documented here.

If you choose to use correlated subqueries, keep in mind the following restrictions on the form and placement of correlated subqueries:

You can use correlated subqueries in WHERE clauses.

You can use correlated subqueries in inner join conditions and with the equal join condition operator.

You can use correlated subqueries in mixed correlated expressions only in the following form:

expr(corr_columA, corr_columnB,...) = expr(local_columnX, local_columnY,...)

You cannot use correlated subqueries in SET operations (UNION, INTERSECT, EXCEPT, and MINUS).

You cannot use correlated subqueries in aggregates with GROUP BY and HAVING clauses.

You cannot use correlated subqueries in ORed clauses or in CASE/WHEN expressions. ^=== You are here

You cannot use correlated subqueries in IN lists.

You cannot use correlated subqueries in SELECT lists.

If you change the OR to an AND, you will find the query will run. Not that it will produce the results you want, of course.

You may be able to rewrite this as a JOIN, but I'd need to see the balance of the query structure before I could take a stab at what that might be.

This is sufficient to answer the question. Thanks! – charles Apr 03 '15 at 11:18 — charles, Apr 03 '15 at 11:18

Are multiple correlated queries unsupported in Netezza?

1 Answers1