How can I make JRS queries with wildcards?
I've been trying to create a simple sql query in the Jazz Reporting Service to get an overview of what the Test Plans look like in the JRS database.
SELECT * FROM RIDW.VW_TESTPLAN
SELECT * FROM RIDW.VW_TESTPLAN
If I understand sql correctly this should give me all the columns for all the TP's in the database. However, it seems like the JRS is interpresting the * character as a string value rather than as a wildcard. The response from the database is one row for each TP but each with only one column with the value *.
I'm using CLM 6.0.1.
Is there some workaround for this?
Is there some workaround for this?
Accepted answer
Hello Alex,
Seems like you're coming across this issue that has been subsequently resolved: 389103: Results don't show when SELECT * used in Advanced SQL pane in our upcoming version 6.0.3.
We could potentially backport (pending investigation) this into a subsequent iFix for 6.0.1 if you log a PMR.
Regards,
-Steve
One other answer
Hello Axel,
I think that report builder expects to have an entry in the select statement for each colums it has to display in the report.
Also it's not passing directly the queries to the database but it's filtering modifying the SQL, for example to add the project access control data filtering.
If you try to generate a very simple report in the wizard you will see the select has a structure like this:
SELECT DISTINCT T1.PROJECT_NAME,
T1.REFERENCE_ID,
T1.NAME AS URL1_title,
T1.URL AS URL1
FROM RIDW.VW_REQUEST T1
WHERE (T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL)
where there's the explicit declaration of each column that you want to have in the report output.
If you want to investigate the database structure, I suggest using an appropriate SQL client and/or the data dictionaries.
Best Regards
Francesco Chiossi
I think that report builder expects to have an entry in the select statement for each colums it has to display in the report.
Also it's not passing directly the queries to the database but it's filtering modifying the SQL, for example to add the project access control data filtering.
If you try to generate a very simple report in the wizard you will see the select has a structure like this:
SELECT DISTINCT T1.PROJECT_NAME,
T1.REFERENCE_ID,
T1.NAME AS URL1_title,
T1.URL AS URL1
FROM RIDW.VW_REQUEST T1
WHERE (T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL)
where there's the explicit declaration of each column that you want to have in the report output.
If you want to investigate the database structure, I suggest using an appropriate SQL client and/or the data dictionaries.
Best Regards
Francesco Chiossi