DB2 subquery not working using IN statement SQLCODE 115

Question

I'm trying to execute a query in DB2. But it throws following error:

Error: DB2 SQL Error: SQLCODE=-115, SQLSTATE=42601, SQLERRMC=IN, DRIVER=4.8.86
SQLState:  42601
ErrorCode: -115
Error: DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURSH200C1; STMT0001, DRIVER=4.8.86
SQLState:  26501
ErrorCode: -514

My query:

SELECT ROW_NUMBER() OVER() AS ID,
CONCAT(TRIM(TB1.ROW1),CONCAT('_',TRIM(TB1.ROW2))) AS CODE_DESCRIPTION,
CASE
    WHEN TRIM(TB1.ROW1) IN (SELECT T1.ROW1 FROM DB1.TABLE1 T1  WHERE T1.ROW3 = 'TEST')
    THEN 'Valid'
    ELSE 'Invalid'
END,
TB1.* FROM DB1.TABLE1 TB1
WHERE TB1.ROW3 = 'CLASS1';

SQLCode 115 means Comparison is invalid. Which is not?

Following is the part of the content.

**Row3**    **Row1**                            **Row2**
KSASPREM    SRQ                         0   0   Auto Carry                                                                                                                                                                                                                                                                                          SRQ                 
KSASPREM    SCG                         0   0   BRT Buses                                                                                                                                                                                                                                                                                           SCG                 
KSASPREM    SCE                         0   0   Buses                                                                                                                                                                                                                                                                                               SCE                 
KSASPREM    SRR                         0   0   Buses                                                                                                                                                                                                                                                                                               SRR                 
KSASPREM    SDC                         0   0   Domestic All Risks                                                                                                                                                                                                                                                                                  SDC                 
KSASPREM    SDA                         0   0   Domestic Buildings                                                                                                                                                                                                                                                                                  SDA    

Task to accomplish:

1. Retrieve all the values from Table1 where Row3 is KSASPREM.
2. The result should contain one extra column 'Valid' value Yes/No if value of Row1 is not in the Values retrieved from Table1 where Row3 is 'TEST'.

Answer 1

As with so many things, a JOIN (here, LEFT JOIN) is the answer. Specifically, we need to put the (slightly modified) subquery as the table reference:

LEFT JOIN (SELECT DISTINCT row1, 'Valid' as valid
           FROM Table1
           WHERE row3 = 'TEST') AS Test
       ON Test.row1 = TB1.row1

• LEFT JOIN tells the query engine that "rows in this other table aren't required".
• DISTINCT says, "for all value combinations in these columns, give me just one row"
• Using a constant value - 'Valid' - returns that constant value.

... so this gets us a (virtual, temp) table containing unique row1 entries where row3 = 'test'.

Here's the full query:

SELECT ROW_NUMBER() OVER(ORDER BY TB1.row1) AS ID,
       TRIM(TB1.ROW1) || '_' || TRIM(TB1.ROW2) AS CODE_DESCRIPTION,
       COALESCE(Test.valid, 'Invalid') AS valid,
       TB1.row3, TB1.row1, TB1.row2
FROM Table1 TB1
LEFT JOIN (SELECT DISTINCT row1, 'Valid' as valid
           FROM Table1
           WHERE row3 = 'TEST') Test
       ON Test.row1 = TB1 .row1
WHERE TB1.ROW3 = 'KSASPREM'

SQL Fiddle Example

COALESCE(...) returns the first non-null value encountered in the value list. Since, if there is no Test row, Test.valid will be null, this outputs 'Invalid' for TB1 rows without a corresponding Test row. (Internally it's calling CASE, I believe, this just makes it prettier)

Note that:

• I've put an ORDER BY into the OVER clause, to return (mostly) consistent results. If you only ever plan on running this once it doesn't matter, but if you need to run it multiple times and get consistent IDs, you'll need to use something that won't be shuffled.
• DB2 (and apparently PostgreSQL) support || as a concat operator. It makes reading statements so much easier to understand.
• Never use SELECT *, it isn't safe for several reasons. Always specify which columns you want.