SQL Query Translation for Handling Records

SQL Translation of the Code Snippet

The provided code snippet is a complex SQL query that retrieves records while applying window functions and aggregates. Below is the translation of the original code snippet into SQL while preserving its logic and behavior.

Translated SQL Code

SELECT
  t.*,
  (
    SELECT STRING_AGG(CAST(c."REG_NUM" AS VARCHAR), ',')
    FROM dbo."CLAIM" AS c
    WHERE c.isn_handling = t.isn_handling
  ) AS reg_num
FROM (
  SELECT
    hri.*,
    h.ins_date,
    ROW_NUMBER() OVER (
      PARTITION BY (
        CAST(CASE
          WHEN hri."TITLE" IS NOT NULL THEN hri."TITLE"
          ELSE ''
        END AS VARCHAR) || 
        CAST(CASE
          WHEN hri."DOC_CAPTION" IS NOT NULL THEN hri."DOC_CAPTION"
          ELSE ''
        END AS VARCHAR) || 
        CAST(CASE
          WHEN hri."DOC_SERIES" IS NOT NULL THEN hri."DOC_SERIES"
          ELSE ''
        END AS VARCHAR) || 
        CAST(CASE
          WHEN hri."DOC_NUMBER" IS NOT NULL THEN hri."DOC_NUMBER"
          ELSE ''
        END AS VARCHAR)
      )
      ORDER BY h.ins_date DESC
    ) AS row_num
  FROM dbo.claim_declarant AS cd
  JOIN dbo.handling_declarant AS hd
    ON cd.isn_handling_declarant = hd.isn_handling_declarant
  JOIN dbo.handling AS h
    ON hd.isn_handling = h.isn_handling
  JOIN dbo."CITIZEN_CL" AS cit
    ON cit."ISN_NODE" = hd.isn_citizen
  JOIN dbo.handling_receipt_item AS hri
    ON hd.isn_iss_doc = hri.isn_handling_receipt_item
  WHERE (
    hri."FILE_NAME" IS NOT NULL
    AND cd.isn_parent_declarant IS NULL
    AND cit.citizen_id = '1296b5ee-03a8-488f-8a5a-9503a0e59006'
  )
) AS t
-- WHERE t.row_num = 2

Key Components

• Aggregated Column: The subquery in the SELECT statement uses STRING_AGG to concatenate REG_NUM values for corresponding isn_handling.
• Window Function: ROW_NUMBER() is used to number the rows within each partition based on the specified criteria, ordered by the ins_date.
• JOIN Clauses: Multiple tables are joined to gather the necessary information, ensuring the relationships through their respective keys.
• Filtering: The primary filtering logic is maintained in the WHERE clause, targeting specific criteria, including checking for NULLs and matching specific citizen IDs.

Note

• The commented-out line -- WHERE t.row_num = 2 is retained in the translation to indicate the optional filtering, in case you wish to apply it later.

This SQL code is ready for immediate use in a database context, retaining the original logic and structure of the provided code snippet.