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:
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 : "".
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?
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
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
2 other answers
Francesco is correct - but it's not our project access control code that restricts this, it's our connection to the JDBC driver which is restricted to read-only access to the Database only.
I'm not sure what the alternative is other then adding a table in the DW though your DB admin and then using DCC Extensibility as Francesco suggests. You can find a topic discussing this here: https://jazz.net/wiki/bin/view/Main/DataCollectionCustomizationUsingDCC
-Steve
Comments
Hi Steven, thanks for you reply.
I've made a quick google on the access rights required to create temporary tables.
It does seem that this access is granted to users with basic read only access in both SQL Server and Oracle. I couldn't find anything on DB2 but I would guess it's behaviour is similar.
The thing is that I don't want actually alter the database. I just want to have a temporary variable in my script that I can refer to.
It does seem that this access is granted to users with basic read only access in both SQL Server and Oracle. I couldn't find anything on DB2 but I would guess it's behaviour is similar.
The thing is that I don't want actually alter the database. I just want to have a temporary variable in my script that I can refer to.
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
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
Comments
Sarah Byrum
Apr 14 '17, 3:00 p.m.Hello,
I have been having these same problems. I was wondering if you were ever able to figure out a solution? Thank you!
-Sarah
1 vote
Axel Ulmestig
Aug 02 '16, 1:01 a.m.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.