【Question】

I am writing an oracle 10g query for creating Jasper reports. This is the query:

 

SELECT essay_id,

 LTRIM (

 MAX (SYS\_CONNECT\_BY\_PATH (full\_name, ','))

 KEEP (DENSE_RANK LAST ORDER BY curr),

 ',')

 AS full_name

FROM (SELECT essay_id,

 full_name,

 ROW\_NUMBER ()OVER (PARTITION BY essay\_id ORDER BY full_name)

 AS curr,

 ROW_NUMBER ()

 OVER (PARTITION BY essay\_id ORDER BY full\_name)

 \- 1

 AS prev

 FROM (SELECT a.id AS essay_id,

 CASE NVL (firstname, 'NULL FIRSTNAME')

 WHEN 'NULL FIRSTNAME' THEN username

 ELSE (firstname || ' ' || lastname)

 END

 AS full_name

 FROM essay_table a

 INNER JOIN essay\_writer\_join ej ON a.id = ej.essay_id

 INNER JOIN writer_table u ON ej.user_id = u.id))

GROUP BY essay_id

CONNECT BY prev = PRIOR curr AND essay\_id = PRIOR essay\_id

START WITH curr = 1

 

The essays are unique but can have multiple writers (essay_writer_join). The query gives me essays with writers separated by comma.

The problem is that I need to add one more column called “manager” that will show the manager of the writer. The manager information is in the WRITER_TABLE with column name “manager_name”. The essay_table has the writer’s first name, last name and username. The tricky part is that two writers can have two different managers. For example, for essay ‘123’ the writers are ‘abc’ and ‘xyz’ and the managers for them are ‘lmo’ and ‘pqr’ respectively then the records should indicate in the following format:

 

essay id    writer       manager

 

123         abc, xyz     lmo, pqr

 

456         abc, def     lmo

 

Is this possible in oracle 10g SQL? I tried to search for similar situation but cannot find any related solutions.

 

【Answer】

It’s complicated to handle order-based calculations like this in SQL. We can prepare data source for a reporting tool in SPL (Structured Process Language). The code is simple and easy to understand:

	A
1	$select  CASE  NVL(u.firstname,   'NULL FIRSTNAME') WHEN 'NULL FIRSTNAME' THEN u.username ELSE (u.firstname +   u.lastname) END AS full_name, u.manager   manager,a.id essay_id from  writer_table u join  essay_writer_join ej on  u.id=ej.user_id join  essay_table a on  ej.essay_id=a.id
2	=A1.group(essay_id  ;~.(full_name).concat@c():writer,~.id(manager).concat@c():manager)

 

A1: JOIN the three tables.

A2: Group the joining result by essay_id and connect members in each group with comma; ~ represents each group of records, and concat@c function joins up members with comma.

The final result:

esProc can be integrated into the JasperReport as a plugin. For more details, see How to Call an SPL Script in JasperReport.