Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

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:

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:

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?






0 votes

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

1 vote

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

1 vote


2 other answers

Permanent link
 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

1 vote

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.


Permanent link
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

0 votes

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

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.

Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details
× 481
× 360
× 37

Question asked: Mar 15 '16, 4:41 a.m.

Question was seen: 4,174 times

Last updated: Apr 14 '17, 3:00 p.m.

Confirmation Cancel Confirm