How can I declare a temporary table in the Jazz Reporting Service?

Axel Ulmestig (34●6●12) | asked Mar 15 '16, 4:41 a.m.
edited Mar 15 '16, 11:20 a.m.

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:

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,
	...

Resulting in the Error:
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 : "".

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,
        ...

This does instead result in this error:

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: ... ... ... . 09:35:31

DECLARE GLOBAL TEMPORARY TABLE result1
	(Feature_ID    INTEGER,
	Feature_Name    INTEGER);
	ON COMMIT PRESERVE ROWS;
		
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,
	...

This gives the error:
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?

Comments

Sarah Byrum commented Aug 01 '16, 8:45 a.m. | edited 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

Axel Ulmestig commented 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.

Accepted answer

permanent link

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

Most liked answers ↑|Newest answers|Oldest answers

permanent link

Steven Shaw (551●1●3) | answered Aug 02 '16, 9:16 a.m.
FORUM MODERATOR / JAZZ DEVELOPER

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

Axel Ulmestig commented Aug 02 '16, 10:43 a.m.

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.

permanent link

Francesco Chiossi (5.7k●1●11●19) | answered Aug 02 '16, 4:16 a.m.

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

Francesco Chiossi commented Aug 02 '16, 4:18 a.m.

From version 6.0.2 onward, as an alternative you can look into customizing the data collection: Data Collection Customization Using DCC

Your answer

Register or log in to post 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.

It's all about the answers!

Related questions

How can I declare a temporary table in the Jazz Reporting Service?

Accepted answer

2 other answers

Your answer