Perform a SQL Query according to the Dynamic Condition
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 find records where EID is less than 5 according to the dynamic condition (dynamically composed SQL). Below is the desired result:
| 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 |
Solution
We write the following script p1.dfx in esProc:
| A |
|
| 1 |
=connect("demo") |
| 2 |
=A1.query@x("SELECT * FROM EMPLOYEE WHERE "+where) |
Explanation:
Set a script parameter where, whose value is EID<5.
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.
Refer to How to Call an SPL Script in BIRT to learn about the method of integrating the SPL script into BIRT.
https://www.eclipse.org/forums/index.php/t/1074988/
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
