Results 1 to 14 of 14

Thread: SQL Code -415

  1. #1
    Join Date
    Aug 2008
    Posts
    55

    Unanswered: SQL Code -415

    Hello guys,

    I tried to run a query using 2 unions and got a sql code of -415. This piece of code works with the first union but not with the second UNION. I googled 'sql code -415' and nothing came up. Can anyone help me please?

    Thanks in advance!


    SELECT DISTINCT A.ENTITYID,
    A.SCTYPE,
    A.SCHOOLCD,
    A.CLASSYR,
    B.DEGREE1,
    B.DEGREE2,
    B.MAJOR1CD,
    B.MAJOR2CD,
    B.MAJOR3CD,
    C.PRRECTYP
    FROM ADUADS01.ADVTB917 A,
    ADUADS01.ADVTB918 B,
    ADUADS01.ADVTB901 C
    WHERE A.ENTITYID = B.ENTITYID
    AND A.SCHOOLCD = B.SCHOOLCD
    AND A.CLASSYR = B.DEGREEYR
    AND A.ENTITYID = C.ENTITYID
    UNION
    SELECT DISTINCT A.ENTITYID,
    A.SCTYPE,
    A.SCHOOLCD,
    A.CLASSYR,
    ' ',
    ' ',
    ' ',
    ' ',
    ' ',
    C.PRRECTYP
    FROM ADUADS01.ADVTB917 A,
    ADUADS01.ADVTB901 C
    WHERE A.ENTITYID = C.ENTITYID
    AND NOT EXISTS (SELECT 1 FROM ADUADS01.ADVTB918 X
    WHERE A.ENTITYID = X.ENTITYID
    AND A.SCHOOLCD = X.SCHOOLCD
    AND A.CLASSYR = X.DEGREEYR)
    UNION
    SELECT DISTINCT C.ENTITYID,
    ' ',
    ' ',
    ' ',
    ' ',
    ' ',
    ' ',
    ' ',
    ' ',
    C.PRRECTYP
    FROM
    ADUADS01.ADVTB901 C
    WHERE
    C.PRRECTYP IN ('GP','PA')
    AND NOT EXISTS (SELECT 1 FROM ADUADS01.ADVTB917 X
    WHERE C.ENTITYID = X.ENTITYID)
    ORDER BY 1
    Last edited by KevinYC; 01-16-09 at 16:08.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by KevinYC
    I googled 'sql code -415' and nothing came up.
    DB2 V9.1 - Codes - -415 states:
    THE CORRESPONDING COLUMNS, column-number, OF THE OPERANDS OF A SET OPERATOR ARE NOT COMPATIBLE

    So apparently, the datatype of one of A.SCTYPE, A.SCHOOLCD, or A.CLASSYR is not CHAR or VARCHAR. So replace one or more of the ' ' in the third query by NULLIF(0,0) or so.

    Actually, your query can be much simplified by using outer joins, as follows:
    Code:
    SELECT DISTINCT C.ENTITYID,
                    A.SCTYPE,
                    A.SCHOOLCD,
                    A.CLASSYR,
                    B.DEGREE1,
                    B.DEGREE2,
                    B.MAJOR1CD,
                    B.MAJOR2CD,
                    B.MAJOR3CD,
                    C.PRRECTYP
        FROM              ADUADS01.ADVTB901 C
          LEFT OUTER JOIN ADUADS01.ADVTB917 A  ON A.ENTITYID = C.ENTITYID
          LEFT OUTER JOIN ADUADS01.ADVTB918 B  ON A.ENTITYID = B.ENTITYID
                                              AND A.SCHOOLCD = B.SCHOOLCD
                                              AND A.CLASSYR  = B.DEGREEYR
        WHERE C.PRRECTYP IN ('GP','PA')
           OR A.ENTITYID IS NOT NULL
      ORDER BY 1
    This will run a lot faster than your UNIONs.
    (Actually, the WHERE condition looks a bit strange and is the exact translation of your "C.PRRECTYP IN ('GP','PA')" only appearing in the third part of your union.
    Most likely you want to remove the"OR" part.
    Also, most likely, the DISTINCT could be removed, which will speed up the query once again.)
    Last edited by Peter.Vanroose; 01-16-09 at 16:44.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    C:\>db2 ? sql415


    SQL0415N The data types of corresponding columns are not
    compatible in a fullselect that includes a set operator
    or in the multiple rows of a VALUES clause of an INSERT
    or fullselect.

    Explanation:

    There are various statements where this error may occur.

    o It may occur within a SELECT or VALUES statement that
    includes set operations (UNION, INTERSECT, or EXCEPT). The
    corresponding columns of the subselects or fullselects that
    make up the SELECT or VALUES statements are not compatible.

    o It may occur within an INSERT statement that is inserting
    multiple rows. In this case, the corresponding columns of
    the rows specified in the VALUES clause are not compatible.

    o It may occur within a SELECT or VALUES statement where the
    VALUES clause is used with multiple rows. In this case, the
    corresponding columns of the rows specified in the VALUES
    clause are not compatible.



    The columns are incompatible for one of the following reasons:

    o Both columns are not characters

    o Both columns are not numbers

    o Both columns are not dates

    o Both columns are not times

    o Both columns are not timestamps

    o Both columns are not graphic

    o Both columns are not the same user-defined distinct type.



    If the data type of a column is character, date, time, or
    timestamp the corresponding column may be a character string
    constant.

    The statement cannot be processed.

    User Response:

    Correct the column names used in the SELECT statements or the
    expressions in the VALUES clause so that all corresponding
    columns are compatible types.

    sqlcode : -415

    sqlstate : 42825
    By the way, you don't need DISTINCT if you use UNION (as opposed to UNION ALL): the UNION operation eliminates duplicates.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Aug 2008
    Posts
    55
    Thank you both so much!
    I used NULLIF(0,0) and it worked.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by KevinYC
    I used NULLIF(0,0) and it worked.
    What about the much simpler OUTER JOIN approach?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by KevinYC
    I googled 'sql code -415'
    No suprise there. As you know, in google syntax this means: find all pages that contain the words "sql" and/or "code" but do not contain 415.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze
    No suprise there. As you know, in google syntax this means: find all pages that contain the words "sql" and/or "code" but do not contain 415.
    In this particular case, a better Google-style query would e.g. be:
    Code:
    SQLCODE "-415"
    (note the double quotes, to escape the special meaning of an initial minus sign)
    or actually even (what I used in this case):
    Code:
    db2 SQLCODE "-415" site:ibm.com
    DB2 for LUW uses the prefix "N" to denote negative SQLcodes, so the Google-style query for an LUW-specific SQLcode -415 could be:
    Code:
    db2 SQL0415N
    Other variants possible, of course...
    Last edited by Peter.Vanroose; 01-18-09 at 08:20.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by KevinYC
    I googled 'sql code -415' and nothing came up. Can anyone help me please?
    ORDER BY 1
    why did you google it? there is an db2 information centers for both v8 and v9.5 on internet:

    DB2 Universal Database

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    just cut and paste the code into search window and it will find it for you...
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can download the PDF manuals here:
    IBM - DB2 database product documentation
    The SQL errors are in the Message Reference Vol.2
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by MarkhamDBA
    why did you google it?
    Because it's a universal search interface. That's the power of Google! It will give you (almost) all of the pages that an IBM-local search would give, provided you come up with the appropriate keywords (but that also holds for the site-specific search engines).
    BTW, most site-specific seach engines nowadays use Google "under the covers"!
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by Peter.Vanroose
    Because it's a universal search interface. That's the power of Google! It will give you (almost) all of the pages that an IBM-local search would give, provided you come up with the appropriate keywords (but that also holds for the site-specific search engines).
    BTW, most site-specific seach engines nowadays use Google "under the covers"!
    Peter, 'the power of google' did not work in this case. Why are you promoting it?

    I noticed that pdf documentation is incorrect so I started using Infomation Center on-line (see 'create db' command in v8.2 does not have clause for page size).
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by MarkhamDBA
    Peter, 'the power of google' did not work in this case. Why are you promoting it?
    Not promoting, just trying to explain why people use it, or even prefer it.
    Personally, I'm not a heavy Google user.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by MarkhamDBA
    I noticed that pdf documentation is incorrect so I started using Infomation Center on-line (see 'create db' command in v8.2 does not have clause for page size).
    There are a few instances where they added features in a later fixpack and did not update the PDF documentation. The original doc was correct for 8.2.0.

    I can understand your concern about that, but I believe it is good to have the docs on my hard drive. Also, the format of the manuals is sometimes better than inforcenter. The manuals tend to be more narritive in nature which is good if you are not sure what you are looking for. If you know exactly what you are looking for and just need the syntax then Infocenter is fine (but the IBM search function for Infocenter stinks IMO and I much prefer the PDF search).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Aug 2008
    Posts
    55
    Quote Originally Posted by Peter.Vanroose
    What about the much simpler OUTER JOIN approach?
    Thank you guys for providing such valuable information!

    Peter,
    I didn't use OUTER JOIN because i was modifying a production query. I wanted to make as little change to it as possible. But thank you for showing me a different way of doing this. I have studied and printed your query and the rest of the page for reference.

    Thank you all again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •