It's all about the answers!

Ask a question

How can I make JRS queries with wildcards?


Axel Ulmestig (3419) | asked Jul 07 '16, 3:30 a.m.
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?

Accepted answer


permanent link
Steven Shaw (55113) | answered Jul 07 '16, 7:37 a.m.
FORUM MODERATOR / JAZZ DEVELOPER
 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


Axel Ulmestig selected this answer as the correct answer

Comments
1
Steven Shaw commented Jul 07 '16, 9:38 a.m.
FORUM MODERATOR / JAZZ DEVELOPER

 apologies - mean Axel, not "Alex"

One other answer



permanent link
Francesco Chiossi (5.7k1719) | answered Jul 07 '16, 4:15 a.m.
edited Jul 07 '16, 4:16 a.m.
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

Your answer


Register or to post your answer.