【Question】

I have a query in a stored procedure like below:

select x,y from table

And the results will look like below:

 

x y

 

1 a

 

1 b

 

2 a

 

2 b

 

3 a

 

3 b

 

I need to add a blank column or zeros when the value of x changes like below:

 

x y

 

1 a

 

1 b

 

0 0

 

2 a

 

2 b

 

0 0

 

3 a

 

3 b

 

Can this be done in SQL or with BIRT since I’m using the data for BIRT reports?

Below is a reply and the asker’s response:

Sounds like you’re looking for the wrong solution to your problem. Most reporting tools should be able to handle groups of data without having to manipulate the source data in this way. You need to use a group header row or something similar.– TannerJun 9 at 10:45

Thanks, somehow I didn’t think of using group header as a blank row– Charles

 

【Answer】

It’s inconvenient to insert a row after each group in a result set without in SQL in the absence of reporting tool’s group method. You can use esProc to prepare the data source. The code is simple and easy:

 

	A
1	=orac.proc("{call   yoursp(?)}",:101:"o":table1)
2	=table1.create().record([0,0])
3	=table1.group@o(x).conj(~|A2)

A3’s result:

BIRT can access esProc through JDBC in same way it does to a database. More explanations can be found in How to Call an SPL Script in Java.