【Question】

table1:

|id | name| isActive|

 

|---|-----|---------|

 

| 1 | dd | 1 |

 

| 1 | cc | 1 |

 

| 1 | zz | 1 |

 

| 1 | yy | 1 |

 

table2:

|id | name| isActive|

 

|---|----|----------|

 

| 1 | ab | 0 |

 

| 1 | cd | 0 |

 

| 1 | ef | 0 |

 

| 1 | gh | 0 |

 

| 1 | wf | 0 |

 

| 1 | gp | 0 |

 

The requirements are to get count of both tables and print them to two separate columns in BIRT Report. I have tried this:

 

SELECT

 

 COUNT(table2.`name`) Table1Count,

 

 table1.`isActive` Table1IsActive ,

 

 COUNT(table2.`name`) Table2Count,

 

 table2.`isActive` Table2IsActive

 

FROM

 

 `table1`,

 

 `table2`

 

its output is in two separate columns but with cross join

 

Table1Count isActive Table2Count Table2IsActive

 

 43316 0 3536 1

 

I cannot use any join because there is no relationship between the tables. But its output is similar to the desired output where I want two tables’ counts in separate columns.

By trying this I get two separate rows:

SELECT

 

 *

 

FROM

 

 (SELECT

 

 COUNT(*) Table1Count,

 

 table1.`isActive` Table1IsActive

 

 FROM

 

 `table1`

 

 UNION

 

 SELECT

 

 COUNT(*) Table2Count,

 

 table2.`isActive` Table2IsActive

 

 FROM

 

`table2 `) AS a

 

Its output is in two separate rows while my requirements are separate columns.

 

Table1Count Table1IsActive

 

 442 0

 

 98 1

 

【Answer】

It’s complicated to code this in BIRT script. SQL code is difficult to understand as it doesn’t support a step-by-step computation. It’s clear to do this in esProc SPL (Structured Process Language) and the script is convenient to integrate with BIRT. Below is the SPL script:

 

	A
1	=myDB1.query("select   isActive from table1")
2	=myDB1.query("select   isActive from table2")
3	=create(Table1Count,Table1IsActive,Table2Count,Table2IsActive).record([A1.count(),A1.#3,A2.count(),A2.#3])

A1: Retrieve data from table1.

A2: Retrieve data from table2.

A3: Create a table sequence of the specified format and use record() function to populate desired values to it.

BIRT can access esProc through JDBC and calls the script in same way as calling a stored procedure. Details are explained in How to Call an SPL Script in BIRT.