7.9 Association query: align-join on ordinal numbers to shuffle field values
Join tables and shuffle positions of values in the specified field to encrypt them.
Shuffle values of a certain column in the database and encrypt them, and write them back to the database.
| ID | ORIGINAL_VALUE | SHUFFLED_VALUE |
|---|---|---|
| 1 | D | N |
| 2 | U | n |
| 3 | j | K |
| 4 | N | D |
| … | … | … |
The join() function works with @p option to perform an alignment join on ordinal numbers.
SPL script:
| A | |
|---|---|
| 1 | =connect(“demo”) |
| 2 | =A1.query@x(“select ID,ORIGINAL_VALUE from REF_VALUES”) |
| 3 | =A2.sort(rand()) |
| 4 | =join@p(A2.(ID);A3.(ORIGINAL_VALUE)) |
| 5 | =A1.update@u(A4, REF_VALUES, ID:_1, SHUFFLED_VALUE:_2; ID) |
A1 Connect to the database.
A2 Query the reference values table.
A3 Shuffle the table sequence with a random seed.
A4 Use join@p function to join the original IDs and corresponding shuffled values in alignment on ordinal numbers.
A5 Update A4’s result to the database table REF_VALUES according to the primary key ID.
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
