DB2 ‘SQL0901 SQL System Error.’ when trying to SELECT from view containing UNION

Loading...

DB2 ‘SQL0901 SQL System Error.’ when trying to SELECT from view containing UNION

I am using a AS400 DB2 Database. In that Database I got 3 Views.
All Views have the exact same Columnnames and Datatypes.
View 1 & 2 each consist of a Select Statement with aggregate Functions on the same source Table. Just the WHERE Conditions and the aggregate Functions are a little different.
View 3 simply is a UNION SELECT on both VIEW 1 & 2 merging the results.
So here is my Question.
SELECT 2 AS CLIENT, COLUMN1, COLUMN2, COLUMN3, COLUMN4, VALUE
FROM SOME_DATABASE.VIEW3
WHERE COLUMN1 = @COLUMN1 AND COLUMN2 = @COLUMN2 ORDER BY COLUMN3

I can execute parameterized SELECT Statements (like the one above) from my c# Program on VIEW 1 & 2 without problems, but when i try to use a parameterized SELECT on VIEW 3 I get a ‘SQL0901 SQL System Error.’ However, when i dont use Parameters it just works fine.
Can anyone explain this behaviour to me?
AS400 DB2 Version is v5r2m0
The program is written in c# on .NET 4.0 in VS 2012
using ‘IBM.Data.DB2.iSeries.dll’ for accessing the Database
Definition View 1:
SELECT COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
SUM(CAST((TRIM(SUBSTR(COLUMN5,104,1))||TRIM(SUBSTR(COLUMN5,94,3))||TRIM(SUBSTR(COLUMN5,98,3))||’.’||TRIM(SUBSTR(COLUMN5,102,2))) AS DECIMAL(10,2))) AS VALUE
FROM SOME_DATABASE.SOME_TABLE
WHERE TRIM(SUBSTR(COLUMN5,105,3)) <> ”
AND SUBSTR(COLUMN5,103,5) <> ‘ EUR’
AND COLUMN4 IN (‘ $NEHHZ’,’$NEHHZVE’,’$NEHWMVE’,’ $NEHWW’,’$NEHWWVE’,’$NEHWWSK’,’ $NEHKW1′,’ $NEHKW2′,’ $NEHSK1′,’ $NEHSK2′,’ $NEHNV’,’ $NEHKZ1′,’ $NEHLZ1′,’ $NEHSZ1′)
GROUP BY COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4

Definition View 2:
SELECT COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
SUM(CAST(TRIM(SUBSTR(COLUMN5,109,1))||TRANSLATE(TRIM(SUBSTR(COLUMN5,99,10)),’.’,’,’) AS DECIMAL(10,2))) AS VALUE
FROM SOME_DATABASE.SOME_TABLE
WHERE COLUMN6 != 0
AND COLUMN4 IN (‘ SUM$HKV’,’ SUM$KWZ’,’ SUM$KZ’,’ SUM$LZ’,’ SUM$SZ’,’ SUM$WMZ’,’ SUM$WWZ’)
GROUP BY COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4

Definition View 3:
SELECT COLUMN1, COLUMN2, COLUMN3, COLUMN4, VALUE
FROM SOME_DATABASE.VIEW1
UNION
SELECT COLUMN1, COLUMN2, COLUMN3, COLUMN4, VALUE
FROM SOME_DATABASE.VIEW2

Solutions/Answers:

Solution 1:

It looks like parameterized SELECT Statements are not supported with views on views. I just replaced the 3rd View with a statement containing the definition of view 1 UNION view 2 like:

SELECT  COLUMN1,  
        COLUMN2,  
        COLUMN3,  
        COLUMN4,  
        SUM(CAST((TRIM(SUBSTR(COLUMN5,104,1))||TRIM(SUBSTR(COLUMN5,94,3))||TRIM(SUBSTR(COLUMN5,98,3))||'.'||TRIM(SUBSTR(COLUMN5,102,2))) AS DECIMAL(10,2))) AS VALUE
FROM    SOME_DATABASE.SOME_TABLE  
WHERE   TRIM(SUBSTR(COLUMN5,105,3)) <> ''
AND     SUBSTR(COLUMN5,103,5) <> '  EUR'  
AND     COLUMN4 IN ('  $NEHHZ','$NEHHZVE','$NEHWMVE','  $NEHWW','$NEHWWVE','$NEHWWSK',' $NEHKW1',' $NEHKW2',' $NEHSK1',' $NEHSK2','  $NEHNV',' $NEHKZ1',' $NEHLZ1',' $NEHSZ1')  
GROUP BY  COLUMN1,  
        COLUMN2,  
        COLUMN3,  
        COLUMN4

UNION

SELECT  COLUMN1,  
        COLUMN2,  
        COLUMN3,  
        COLUMN4,  
        SUM(CAST(TRIM(SUBSTR(COLUMN5,109,1))||TRANSLATE(TRIM(SUBSTR(COLUMN5,99,10)),'.',',') AS DECIMAL(10,2))) AS VALUE  
FROM    SOME_DATABASE.SOME_TABLE  
WHERE   COLUMN6 != 0  
AND     COLUMN4 IN (' SUM$HKV',' SUM$KWZ',' SUM$KZ',' SUM$LZ',' SUM$SZ',' SUM$WMZ',' SUM$WWZ')  
GROUP BY COLUMN1,  
        COLUMN2,  
        COLUMN3,  
        COLUMN4 

References

Loading...