Skip to main content

Multi-Record Retrieval

Context​

Retrieve multiple records from a single table, with a list of identified-identifiers

warning

Query may fall back to full-table-scan if the size of filter exceeds a certain number (table dependant).

Will typically work for a couple hundred of values.

Please do your due diligence, and test against the table of interest for a more accurate read

1. Tokenise identifier values​

WITH raw_values AS (
  SELECT $1 AS raw
  FROM (
    VALUES (?), (?), (?), ...
  )
)
SELECT DATA_REQUESTS.SINGLETON.<table>_LOCK(raw)
FROM raw_values
  • express the values to be tokensied as a CTE, and apply the UDF to all values

2. Filter with tokenised values​

SELECT <identifier>, ...
FROM <table>
WHERE _LOCK_<identifier> IN (?, ?, ?, ...)
ORDER BY REFERENCE_PERIOD DESC
LIMIT 1
  • filter by the _LOCK_<identifier>, using IN, and passing in the tokenised values from the above SQL
warning

The following query will NOT be optimised due to usage of non-static value

WITH raw_values AS (
  SELECT $1 AS raw
  FROM (
    VALUES (?), (?), (?), ...
  )
)
SELECT <identifier>, ...
FROM <table>
WHERE _LOCK_<identifier> IN (
  SELECT DATA_REQUESTS.SINGLETON.<table>_LOCK(raw)
  FROM raw_values

)
ORDER BY REFERENCE_PERIOD DESC
LIMIT 1

Example - obtaining latest CPF contribution record for a given list of UINs​

note

Example Data Request ID: GOVTECH-2025-01-001

1. Tokenise UINs value
WITH raw_values AS (
  SELECT $1 AS raw
  FROM (
    VALUES (?), (?), (?), ...
  )
)
SELECT DATA_REQUESTS.SINGLETON.<table>_LOCK(raw)
FROM raw_values
2. Filter with tokenised UIN
SELECT
  UIN,
  CPF_SUBMISSION_NO,
  RELEVANT_CONTRIBUTION_MONTH,
  NET_CONTRIBUTION_AMT,
  DECLARED_ORDINARY_WAGE_AMT
FROM DATA_REQUESTS.GOVTECH_2025_01_001.V_DH_CPFB_WAGECON
WHERE _LOCK_UIN IN (?, ?, ?, ?)
QUALIFY ROW_NUMBER() OVER (PARTITION BY _LOCK_UIN ORDER BY RELEVANT_CONTRIBUTION_MONTH DESC) = 1;