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

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:
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.)

By the way, you don't need DISTINCT if you use UNION (as opposed to UNION ALL): the UNION operation eliminates duplicates.

Thank you both so much!
I used NULLIF(0,0) and it worked.

What about the much simpler OUTER JOIN approach?

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:(note the double quotes, to escape the special meaning of an initial minus sign)
or actually even (what I used in this case): DB2 for LUW uses the prefix "N" to denote negative SQLcodes, so the Google-style query for an LUW-specific SQLcode -415 could be:Other variants possible, of course...

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...

You can download the PDF manuals here:
IBM - DB2 database product documentation
The SQL errors are in the Message Reference Vol.2

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).

Not promoting, just trying to explain why people use it, or even prefer it.
Personally, I'm not a heavy Google user.

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).

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!