How can I declare a temporary table in the Jazz Reporting Service?
![]()
Hello
I'm trying to create a report in the Jazz Reporting Service (JRS). I want the report display counts of different data in different columns, in order to accomplish this I need to do a lot of joins. Because I want my query to be readable and maintainable I wanted to save a temporary table which I can join on several times. But I can't quite figure out the appropriate syntax. I've tried a number of different variants: Resulting in the Error:DECLARE @result1 TABLE (Feature_ID INT, Story_Name VARCHAR) INSERT INTO @result1 SELECT T1.REFERENCE_ID AS Feature_ID, T1.NAME AS Story_Name FROM RIDW.VW_REQUEST T1 SELECT DISTINCT Epics.REFERENCE_ID AS Epic_ID, ... CRRGW5628E An com.foundationdb.sql.parser.SQLParserException error occurred when validating the input SQL string, caused by Lexical error at line 1, column 9. Encountered: "@" (64), after : "". This does instead result in this error:DECLARE TABLE result1 (Feature_ID INTEGER, Feature_Name VARCHAR); INSERT INTO result1 SELECT T1.REFERENCE_ID AS Feature_ID, T1.NAME AS Story_Name FROM RIDW.VW_REQUEST T1 SELECT DISTINCT Epics.REFERENCE_ID AS Epic_ID, ...
CRRGW5628E An com.foundationdb.sql.parser.SQLParserException error occurred when validating the input SQL string, caused by Encountered " "table" "TABLE "" at line 1, column 9. Was expecting one of: ... ... ... .
CRRGW5628E An com.foundationdb.sql.parser.SQLParserException error occurred when validating the input SQL string, caused by Encountered " "global" "GLOBAL "" at line 1, column 9. Was expecting one of: ... ... ... . I've tried adding and removing @-signs and combining variants of these. But nothing seems to work. Does anyone know the correct syntax for doing this? |
Accepted answer
![]()
Steven Shaw (551●1●3)
| answered Aug 02 '16, 10:58 a.m.
FORUM MODERATOR / JAZZ DEVELOPER edited Aug 02 '16, 10:59 a.m.
Based on the Axel's investigation it's possible then we could accommodate these temporary tables if they are allowed with a read-only connection to the Database.
I'll start with a story that tracks this: 397543: Support temporary tables in Advanced SQL mode
Please subscribe / vote and/or add your additional comments there.
-Steve
Axel Ulmestig selected this answer as the correct answer
|
2 other answers
![]()
Hello Axel,
for security reasons the SQL you put into a report is not directly sent to the data warehouse, but filtered and encapsulated to implement project access control (if enabled in the data source). I think INSERT and other command that modify a database are not allowed, but I don't have an exact list. Best Regards, Francesco Chiossi Comments From version 6.0.2 onward, as an alternative you can look into customizing the data collection: Data Collection Customization Using DCC |
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.
Comments
Hello,
I have been having these same problems. I was wondering if you were ever able to figure out a solution? Thank you!
-Sarah
I'm having trouble getting these types of things to work. I think the JRS is using DB2, but I'm not sure. It also seems that the JRS runs some diagnostics on the SQL query that are not really based on their SQL interpreter which makes things a bit tricky to use sometimes. I hope some rational employee can clear up this stuff a little bit.