Display Aggregates from Two Tables Side by Side

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.

undefined

A2: Retrieve data from table2.

undefined

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

undefined

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.