Put Records with Specific Values at the Beginning During the Sort
Problem description & analysis
There is an EMPLOYEE table in the database. The data is as follows:
| EID |
NAME |
SURNAME |
GENDER |
STATE |
BIRTHDAY |
HIREDATE |
DEPT |
| 1 |
Rebecca |
Moore |
F |
California |
1974-11-20 |
2005-03-11 |
R&D |
| 2 |
Ashley |
Wilson |
F |
New York |
1980-07-19 |
2008-03-16 |
Finance |
| 3 |
Rachel |
Johnson |
F |
New Mexico |
1970-12-17 |
2010-12-01 |
Sales |
| 4 |
Emily |
Smith |
F |
Texas |
1985-03-07 |
2006-08-15 |
HR |
| 5 |
Ashley |
Smith |
F |
Texas |
1975-05-13 |
2004-07-30 |
R&D |
| 6 |
Matthew |
Johnson |
M |
California |
1984-07-07 |
2005-07-07 |
Sales |
| 7 |
Alexis |
Smith |
F |
Illinois |
1972-08-16 |
2002-08-16 |
Sales |
| 8 |
Megan |
Wilson |
F |
California |
1979-04-19 |
1984-04-19 |
Marketing |
| 9 |
Victoria |
Davis |
F |
Texas |
1983-12-07 |
2009-12-07 |
HR |
| 10 |
Ryan |
Johnson |
M |
Pennsylvania |
1976-03-12 |
2006-03-12 |
R&D |
We are trying to sort records in the table by EID in a specific order, which is [9,7,5,3, descending order for the rest]. The first four records corresponding to 9,7,5,3 will be passed in as parameters. Below is the desired result:
| EID |
NAME |
SURNAME |
GENDER |
STATE |
BIRTHDAY |
HIREDATE |
DEPT |
| 9 |
Victoria |
Davis |
F |
Texas |
1983-12-07 |
2009-12-07 |
HR |
| 7 |
Alexis |
Smith |
F |
Illinois |
1972-08-16 |
2002-08-16 |
Sales |
| 5 |
Ashley |
Smith |
F |
Texas |
1975-05-13 |
2004-07-30 |
R&D |
| 3 |
Rachel |
Johnson |
F |
New Mexico |
1970-12-17 |
2010-12-01 |
Sales |
| 1 |
Rebecca |
Moore |
F |
California |
1974-11-20 |
2005-03-11 |
R&D |
| 2 |
Ashley |
Wilson |
F |
New York |
1980-07-19 |
2008-03-16 |
Finance |
| 4 |
Emily |
Smith |
F |
Texas |
1985-03-07 |
2006-08-15 |
HR |
| 6 |
Matthew |
Johnson |
M |
California |
1984-07-07 |
2005-07-07 |
Sales |
| 8 |
Megan |
Wilson |
F |
California |
1979-04-19 |
1984-04-19 |
Marketing |
| 10 |
Ryan |
Johnson |
M |
Pennsylvania |
1976-03-12 |
2006-03-12 |
R&D |
Solution
We write the following script p1.dfx in esProc:
| A |
|
| 1 |
=connect("demo") |
| 2 |
=A1.query@x("SELECT * FROM EMPLOYEE").sort(EID).align@as(eid.split@pc(),EID) |
Explanation:
Set a script parameter eid, whose value is 9,7,5,3.
A1 Connect to the database named demo.
A2 Return query result as a table sequence and auto-close database connection when the query is finished. Sort the table sequence fist by EID then by the comma-separated sequence of numbers defined through parameter eid. Put the non-matching members at the end during the second sort.
Refer to How to Call an SPL Script in BIRT to learn about the method of integrating the SPL script with BIRT.
https://www.eclipse.org/forums/index.php/t/1087439/
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
