[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

DB2 Correlated Subquery - ERROR SQLSTATE=42906 (# -133)

Posted on 2011-04-27
5
Medium Priority
?
1,026 Views
Last Modified: 2012-05-11
I am trying to run the following query via an MS Access Passthrough query (my normal method of accessing the AS/400 server). I have recently started working with correlated subqueries, and seem to have hit a wall. When I run this query, I receive an error that includes "SQLSTATE=42906 (# -133)". On IBM's website, I found that this error means "An aggregate function in a subquery of a HAVING clause includes an expression that applies an operator to a correlated reference."

I am a bit confused by the meaning of this error, as my subquery is in the SELECT clause, not the HAVING clause. In fact, there is no HAVING clause.

If I remove the subquery completely, it runs fine. If I replace the field 'O.CHGOVR_DATE' with a literal date value ('2011-01-01'), the query runs fine. My guess is that the problem has something to do with the fact that I am using a correlated field in a calculation, but I don't know how to modify the SQL to make this work.

What I am trying to do in the subquery is to compare two dates. If the second date is more than one year prior to the first date, I want to return a value of the first date minus one year. (effectively truncating the delta between the two dates at one year). Any help would be appreciated. Thanks.

SELECT
PRODDB2.SMITEM_DPIMS.JOB_NO AS EWR,
PRODDB2.SMITEM_DPIMS.ITEM_NO AS PART_NO,
PRODDB2.SITEMX_DPIMS.ITEM_DESC AS DESCRIPTION,
PRODDB2.SITEMX_DPIMS.ITEM_STATUS AS STATUS,
PRODDB2.SITMUSG_DPIMS.ITEM_USAGE_MDL_CD AS MODEL,
PRODDB2.SITMUSG_DPIMS.LOC AS LOCATION,
PRODDB2.SMITEM_DPIMS.REPL_BY_ITEM_NO AS REPL_PART,
PRODDB2.SITEMX_DPIMS.ITEM_DESC AS REPL_DESC,
PRODDB2.SITMUSG_DPIMS.IUSG_YTD_QTY AS YTD,
PRODDB2.SITMUSG_DPIMS.IUSG_PREV_ANN_QTY AS PREV_YEAR_QTY,
PRODDB2.SEJOBHDR_DPIMS.JOB_CHGOVR_DATE,

        (SELECT

        MIN(CASE 
        WHEN DATE(I.JOB_CHGOVR_DATE) <= DATE(O.CHGOVR_DATE) - 1 YEAR
            THEN DATE(O.CHGOVR_DATE) - 1 YEAR
            ELSE DATE(I.JOB_CHGOVR_DATE)
        END)

        FROM
        PRODDB2.SRLSEHST_DPIMS,
        PRODDB2.RLSJOB,
        PRODDB2.SEJOBHDR_DPIMS AS I

        WHERE
        PRODDB2.SRLSEHST_DPIMS.RLSENO = PRODDB2.RLSJOB.RLSE_NO AND
        PRODDB2.RLSJOB.JOB_NO = I.JOB_NO)
        CREATE_DATE,


PRODDB2.SLOC_DPIMS.ACCT_NO AS ACCT,
PRODDB2.SLOC_DPIMS.MATL_COST_TOT AS MATERIAL_COST,
PRODDB2.SLOC_DPIMS.FRT_COST_TOT AS RAW_MATERIAL_COST,
PRODDB2.SLOC_DPIMS.OUT_VNDR_COST_TOT AS VENDOR_SERVICE,
PRODDB2.SLOC_DPIMS.MFG_MAN_MNS_TOT AS MFG_TIME_SUMMARY,
PRODDB2.SLOC_DPIMS.BUCKET_COST_TOT AS BUCKET_COST

FROM 
PRODDB2.SEJOBHDR_DPIMS,
PRODDB2.SMITEM_DPIMS,
PRODDB2.SITMUSG_DPIMS,
PRODDB2.SITEMX_DPIMS,
PRODDB2.SLOC_DPIMS,
PRODDB2.PWOJOB AS O

WHERE
PRODDB2.SEJOBHDR_DPIMS.JOB_NO = PRODDB2.SMITEM_DPIMS.JOB_NO AND
PRODDB2.SMITEM_DPIMS.ITEM_NO = PRODDB2.SITMUSG_DPIMS.ITEM_NO AND
PRODDB2.SMITEM_DPIMS.ITEM_NO = PRODDB2.SLOC_DPIMS.ITEM_NO AND
PRODDB2.SMITEM_DPIMS.ITEM_NO = PRODDB2.SITEMX_DPIMS.ITEM_NO AND
PRODDB2.SMITEM_DPIMS.JOB_NO  = O.JOB_NO AND

PRODDB2.SEJOBHDR_DPIMS.JOB_CHGOVR_DATE >= DATE('01/01/2011') AND
PRODDB2.SEJOBHDR_DPIMS.JOB_CHGOVR_DATE <= DATE('03/31/2011') AND
SUBSTR(PRODDB2.SMITEM_DPIMS.ITEM_NO,4,1) = '-' AND
SUBSTR(PRODDB2.SMITEM_DPIMS.ITEM_NO,10,1) <> '-' AND
(SUBSTR(PRODDB2.SMITEM_DPIMS.REPL_BY_ITEM_NO,4,1)='-' OR  PRODDB2.SMITEM_DPIMS.REPL_BY_ITEM_NO='MANY') AND
PRODDB2.SITMUSG_DPIMS.ITEM_USAGE_TYPE_CD='MDL' AND
PRODDB2.SEJOBHDR_DPIMS.PROJ_NO IN ('402','406','408','426','448','449','621') AND
PRODDB2.SLOC_DPIMS.LOC='009'

WITH UR;

Open in new window

0
Comment
Question by:dtna
  • 2
  • 2
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35480085
the reason you get the error is - db2 probably rewrites the query

i think the error is because you reference a column from table O, but the join (correlation ) is done through a different table
try to fix that and it might work
0
 
LVL 27

Expert Comment

by:tliotta
ID: 35480331
db2 probably rewrites the query

I pretty much agree. The MIN() function is an aggregate, generally run over a GROUP BY if not over the entire result set; and HAVING is a qualifier for a GROUP BY.

I can't tell if this was a typo, though, or if I'd say the opposite:

...but the join (correlation ) is done through a different table

Should that be "not done through a different table"? That is, bring PRODDB2.PWOJOB into the inner SELECT rather than trying to reference the outer one?

Tom
0
 

Author Comment

by:dtna
ID: 35495315
Well, I'm a little confused by your conflicting statements, but I'll assume it was a typo. I've made the following change to the subquery portion of the query, but I am still betting the same error...

        (SELECT

        MIN(CASE 
       WHEN DATE(I.JOB_CHGOVR_DATE) <= DATE(O.CHGOVR_DATE) - 1 YEAR
            THEN DATE(O.CHGOVR_DATE) - 1 YEAR
            ELSE DATE(I.JOB_CHGOVR_DATE)
        END)

        FROM
        PRODDB2.SRLSEHST_DPIMS,
        PRODDB2.RLSJOB,
        PRODDB2.SEJOBHDR_DPIMS AS I

        WHERE
        PRODDB2.SRLSEHST_DPIMS.RLSENO = PRODDB2.RLSJOB.RLSE_NO AND
        O.JOB_NO = I.JOB_NO)
        AS CREATE_DATE,

Open in new window

0
 
LVL 27

Accepted Solution

by:
tliotta earned 2000 total points
ID: 35509585
In this section of code:
        MIN(CASE 
       WHEN DATE(I.JOB_CHGOVR_DATE) <= DATE(O.CHGOVR_DATE) - 1 YEAR
            THEN DATE(O.CHGOVR_DATE) - 1 YEAR
            ELSE DATE(I.JOB_CHGOVR_DATE)
        END)

Open in new window

...with this FROM clause:
        FROM
        PRODDB2.SRLSEHST_DPIMS,
        PRODDB2.RLSJOB,
        PRODDB2.SEJOBHDR_DPIMS AS I

Open in new window

...there is no reference where O.CHGOVR_DATE can be retrieved. That is, you have an aggregate function -- MIN() -- that is not aggregated within any group. It is therefore aggregated within the entire result set specified by the above FROM clause and limited only by the subsequent WHERE clause.

But the "O." correlative isn't related to any of it.

SQL rewrites the query in a way that makes it possible to use an "O." reference in the inner SELECT. To do that, it has generated the GROUP BY... and HAVING... that is needed, but it includes an improper "correlated reference." You will need to resolve that "correlated reference" somehow.

In short, you can't use "O.CHGOVR_DATE" there. Not inside of a MIN() function.

Tom
0
 

Author Closing Comment

by:dtna
ID: 35514953
Thanks
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question