SQL Except error - IBM AS400

This is a discussion on SQL Except error - IBM AS400 ; I was trying out the SQL EXCEPT statement added in v5r3 and used the following SQL directly from the IBM site. http://publib.boulder.ibm.com/infoce...tm#rbafyexcept When I run it (or any other SQL that includes EXCEPT) I get the error below. Any ideas ...

+ Reply to Thread
Results 1 to 8 of 8

Thread: SQL Except error

  1. SQL Except error

    I was trying out the SQL EXCEPT statement added in v5r3 and used the
    following SQL directly from the IBM site.

    http://publib.boulder.ibm.com/infoce...tm#rbafyexcept

    When I run it (or any other SQL that includes EXCEPT) I get the error
    below. Any ideas as to what is going wrong?

    - Mike LaFountain

    SELECT EMPNO
    FROM CORPDATA.EMPLOYEE
    WHERE WORKDEPT = 'D11'
    EXCEPT
    SELECT EMPNO
    FROM CORPDATA.EMPPROJACT
    WHERE PROJNO = 'MA2112' OR
    PROJNO = 'MA2113' OR
    PROJNO = 'AD3111'
    ORDER BY EMPNO


    Additional Message
    Information

    Message ID . . . . . . : SQL0255 Severity . . . . . . . :
    30
    Message type . . . . . :
    Diagnostic

    Message . . . . : DB2 Multisystem query
    error.
    Cause . . . . . : The reason code is
    3:
    -- Code 1 -- Scalar subselects and lateral correlation from a
    nested table
    expression are not allowed with distributed
    files.
    -- Code 2 -- Error occurred while using a temporary distributed
    file.
    -- Code 3 -- EXCEPT or INTERSECT not supported for this
    query.
    -- Code 4 -- Using a sequence is not supported with distributed
    files.
    Recovery . . . : A list of corrective actions
    follow:
    -- If code 1, change the query so it does not use scalar
    subselects or
    correlation from a nested table
    expression.
    -- If code 2, see the previous messages for more
    information.
    -- If code 3, remove EXCEPT or INTERSECT from the
    query.
    -- If code 4, remove the sequence from the
    query.

    Bottom
    Press Enter to
    continue.

    F3=Exit F6=Print F9=Display message
    details
    F10=Display messages in job log F12=Cancel F21=Select assistance
    level


  2. Re: SQL Except error

    On Sat, 04 Aug 2007 11:10:47 -0700, Mike LaFountain wrote:
    > I was trying out the SQL EXCEPT statement added in v5r3 and used the
    > following SQL directly from the IBM site.
    >
    > http://publib.boulder.ibm.com/infoce...tm#rbafyexcept
    >
    > When I run it (or any other SQL that includes EXCEPT) I get the error
    > below. Any ideas as to what is going wrong?
    >
    > - Mike LaFountain
    >
    > SELECT EMPNO
    > FROM CORPDATA.EMPLOYEE
    > WHERE WORKDEPT = 'D11'
    > EXCEPT
    > SELECT EMPNO
    > FROM CORPDATA.EMPPROJACT
    > WHERE PROJNO = 'MA2112' OR
    > PROJNO = 'MA2113' OR
    > PROJNO = 'AD3111'
    > ORDER BY EMPNO
    >
    >
    > Additional Message
    > Information
    >
    > Message ID . . . . . . : SQL0255 Severity . . . . . . . :
    > 30
    > Message type . . . . . :
    > Diagnostic
    >
    > Message . . . . : DB2 Multisystem query
    > error.
    > Cause . . . . . : The reason code is
    > 3:
    > -- Code 1 -- Scalar subselects and lateral correlation from a
    > nested table
    > expression are not allowed with distributed
    > files.
    > -- Code 2 -- Error occurred while using a temporary distributed
    > file.
    > -- Code 3 -- EXCEPT or INTERSECT not supported for this
    > query.
    > -- Code 4 -- Using a sequence is not supported with distributed
    > files.
    > Recovery . . . : A list of corrective actions
    > follow:
    > -- If code 1, change the query so it does not use scalar
    > subselects or
    > correlation from a nested table
    > expression.
    > -- If code 2, see the previous messages for more
    > information.
    > -- If code 3, remove EXCEPT or INTERSECT from the
    > query.
    > -- If code 4, remove the sequence from the
    > query.
    >


    http://www-1.ibm.com/support/docview...257076004d1efb

    perhaps?

    --
    6. I will not gloat over my enemies' predicament before killing them.
    --Peter Anspach's list of things to do as an Evil Overlord

  3. Re: SQL Except error

    Just 2 Questions:
    1. Is one of the 2 files (or both) you use in your SQL statement a DDS
    described logical file?
    2. Are there DDS described logical files with select/omit clauses
    built over one of these files (or both)?

    If so the SQL-statement must be executed by the classic query engine
    (CQE), that cannot handle EXCEPT. All SQL Statements with EXCEPT,
    INTERSECT or all the enhancements in Release V5R4 must be executed by
    the SQL query engine (SQE).

    If your answer for question 1 is yes, just use the physical file.

    If you have to answer question 2 with yes, you have to change the
    Option IGNORE_DERIVED_INDEX in the QAQQINI file to *YES:

    1. Use CRTDUPOBJ (or iSeries Navigator) to create a duplicate of this
    file from QSYS into a library of your choice.
    2. Change the option IGNORE_DERIVED_INDEX in the copied file.
    3. Activate the copied QAQQINI file with CHGQRYA in your job.

    Birgitta


  4. Re: SQL Except error

    On Aug 5, 12:40 am, "Hau...@sss-software.de"
    wrote:
    > Just 2 Questions:
    > 1. Is one of the 2 files (or both) you use in your SQL statement a DDS
    > described logical file?


    No


    > 2. Are there DDS described logical files with select/omit clauses
    > built over one of these files (or both)?
    >


    No

    These files are straight from the test environment that you create
    from the back of one of the SQL manuals. Likewise, the SQL statement
    is directly from the manual. The files have never been altered in
    anyway and are all created directly via SQL.



  5. Re: SQL Except error

    Possibly an obvious question, but are you up to date on PTFs?

    Sam



  6. Re: SQL Except error

    On Aug 5, 1:13 pm, "Saml" wrote:
    > Possibly an obvious question, but are you up to date on PTFs?
    >
    > Sam


    I might be a month or two behind, but that is about all. I guess I'll
    need to talk to someone & get the latest.


  7. Re: SQL Except error

    EXCEPT is only supported by the SQL Query Engine (SQE) - it appears there's
    something in your environment is forcing the Classic Query Engine (CQE) to be
    used. Are you running the SQL statement from an interface using a sort sequence?


    Mike LaFountain wrote:
    > On Aug 5, 1:13 pm, "Saml" wrote:
    >> Possibly an obvious question, but are you up to date on PTFs?
    >>
    >> Sam

    >
    > I might be a month or two behind, but that is about all. I guess I'll
    > need to talk to someone & get the latest.
    >


    --
    Kent Milligan
    ISV Enablement - System i
    kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
    >>> ibm.com/iseries/db2

    (opinions stated are not necessarily those of my employer)

  8. Re: SQL Except error

    On Aug 7, 3:29 pm, Kent Milligan wrote:
    > EXCEPT is only supported by the SQL Query Engine (SQE) - it appears there's
    > something in your environment is forcing the Classic Query Engine (CQE) to be
    > used. Are you running the SQL statement from an interface using a sort sequence?
    >



    Thanks Kent, it's working now. I had a SORT SEQUENCE of *LANGIDSHR in
    my session attributes.

    -Mike


+ Reply to Thread