Anyone having issues with own SQL code with Jazz Reporting Service ?
HI !
I try to create my own SQL queries but in some case, I've received error with try to join two tables and use COUNT function
This is my code:
SELECT
P.PROJECT_CLASS_ID,
C.NAME,
COUNT(P.PROJECT_CLASS_ID) As QProject
FROM RIODS.PROJECT P INNER JOIN RIODS.PROJECT_CLASSIFICATION C ON P.PROJECT_CLASS_ID=C.PROJECT_CLASS_ID
GROUP BY P.PROJECT_CLASS_ID
This is the error:
I try to create my own SQL queries but in some case, I've received error with try to join two tables and use COUNT function
This is my code:
SELECT
P.PROJECT_CLASS_ID,
C.NAME,
COUNT(P.PROJECT_CLASS_ID) As QProject
FROM RIODS.PROJECT P INNER JOIN RIODS.PROJECT_CLASSIFICATION C ON P.PROJECT_CLASS_ID=C.PROJECT_CLASS_ID
GROUP BY P.PROJECT_CLASS_ID
This is the error:
CRRGW5628E An com.ibm.db2.jcc.am.SqlSyntaxErrorException error occurred when validating the input SQL string, caused by DB2 SQL Error: SQLCODE=-119, SQLSTATE=42803, SQLERRMC=NAME, DRIVER=4.14.121.
An unexpected error occurred when validating the input SQL string.
Check the reporting service log file for details.
But if you check both table you can find the information.
Could you help me?
Accepted answer
Hi,
Looks like you forgot to include C.NAME in the GROUP BY clause. The query should be something like this:
SELECT
P.PROJECT_CLASS_ID,
C.NAME,
COUNT(P.PROJECT_CLASS_ID) As QProject
FROM RIODS.PROJECT P INNER JOIN RIODS.PROJECT_CLASSIFICATION C ON P.PROJECT_CLASS_ID=C.PROJECT_CLASS_ID
GROUP BY P.PROJECT_CLASS_ID, C.NAME
P.PROJECT_CLASS_ID,
C.NAME,
COUNT(P.PROJECT_CLASS_ID) As QProject
FROM RIODS.PROJECT P INNER JOIN RIODS.PROJECT_CLASSIFICATION C ON P.PROJECT_CLASS_ID=C.PROJECT_CLASS_ID
GROUP BY P.PROJECT_CLASS_ID, C.NAME