Skip to main content

Single Record Retrieval

Context​

Retrieve data from a single table, by an identified-identifier

1. Tokenise identifier value​

SELECT DATA_REQUESTS.SINGLETON.<table>_LOCK(:raw)
  • obtain the tokenised value to filter against.
  • The same raw value will have different tokenised values for different tables

2. Filter with tokenised value​

SELECT <identifier>, ...
FROM <table>
WHERE _LOCK_<identifier> = :tokenised
ORDER BY REFERENCE_PERIOD DESC
LIMIT 1
  • filter by the _LOCK_<identifier>, and project the <identifier> column.
warning

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

SELECT <identifier>, ...
FROM <table>
WHERE _LOCK_<identifier> IN (SELECT DATA_REQUESTS.SINGLETON.<table>_LOCK(:raw))
ORDER BY REFERENCE_PERIOD DESC
LIMIT 1

Example - obtaining latest CPF contribution record for a given UIN​

note

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

1. Tokenise UIN value
SELECT DATA_REQUESTS.SINGLETON.V_DH_CPFB_WAGECON_LOCK(:uin)
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 = :tokenised_uin
ORDER BY RELEVANT_CONTRIBUTION_MONTH DESC
LIMIT 1