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:
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 Iciar Matilde Olocco y Garcia selected this answer as the correct answer
|
3 other answers
Hi, You are missing C.NAME in GROUP BY statement.
|
Are you able to run this SQL query directly against the database in your DB client? That might help narrow down if it's an SQL issue or a problem in JRS.
|
|
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.