12.12 Compare with all values in the result of subquery
Based on data in a certain table, get records through comparing values of a specified field with all corresponding values in the result of the subquery. According to Employee table, find employees who get salaries higher than those of all employees in Sales department.
| ID | NAME | DEPT | SALARY |
|---|---|---|---|
| 1 | Rebecca | R&D | 7000 |
| 2 | Ashley | Finance | 11000 |
| 3 | Rachel | Sales 9000 | |
| 4 | Emily | HR | 7000 |
| 5 | Ashley | R&D | 16000 |
| … | … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from Employee”) |
| 3 | =A2.select(DEPT:“Sales”).max(SALARY) |
| 4 | =A2.select(SALARY>A3) |
A1 Connect to the database.
A2 Query Employee table.
A3 Get the highest salary among employees of Sales department.
A4 Get records of employees whose salaries are higher than those of all employees in Sales department.
Execution result:
| ID | NAME | DEPT | SALARY |
|---|---|---|---|
| 5 | Ashley | R&D | 16000 |
| 20 | Alexis | Administration | 16000 |
| 22 | Jacob | R&D | 18000 |
| 47 | Elizabeth | Marketing | 17000 |
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
