SAP HANA - how to use LEFT LATERAL JOIN

Question

SAP Business One | HANA Version: 2.0 SPS05

I'm attempting to get a query of all vendors and the DocNum and DocDate of their last purchase order.

With a CTE and window function:

WITH Last_PO AS (
 SELECT 
  "CardCode", "DocNum", "DocDate"
  ,ROW_NUMBER() OVER (PARTITION BY "CardCode" ORDER BY "DocEntry" DESC) row_num
 FROM OPOR
 WHERE "CANCELED" = 'N'
)

SELECT
 OCRD."CardCode"
 ,OCRD."CardName"
 ,Last_PO."DocNum" AS "Last PO"
 ,Last_PO."DocDate" AS "Last PO Date"
FROM
 OCRD LEFT JOIN Last_PO on Last_PO.row_num = 1 AND Last_PO."CardCode" = OCRD."CardCode"
WHERE
 OCRD."CardType" = 'S'
ORDER BY
 OCRD."CardCode"

When attempting to use a LATERAL join, I get the error incorrect syntax near "WHERE":

SELECT
 OCRD."CardCode"
 ,OCRD."CardName"
 ,Last_PO."DocNum" AS "Last PO"
 ,Last_PO."DocDate" AS "Last PO Date"
FROM
 OCRD LEFT JOIN LATERAL (
  SELECT "DocNum", "DocDate"
  FROM OPOR 
  WHERE "CANCELED" = 'N' AND "CardCode" = OCRD."CardCode"
  ORDER BY "DocEntry" DESC
  LIMIT 1
) Last_PO ON TRUE
WHERE
 OCRD."CardType" = 'S'
ORDER BY
 OCRD."CardCode"

I found this comment, so maybe this feature never worked correctly:

The link to the docs takes you to the latest version (2.0 SP07 in 2023) but the description of lateral_table_expression hasn't appeared since the SPS 05 version. You can change version to see it. The docs state it should work with a LEFT OUTER JOIN but I couldn't get anything other than cross-join syntax accepted. I wonder if it's a feature that is going to quietly disappear.

Answer 1

I got this to work changing the ON condition (ON TRUE doesn't seem to work on HANA), but it's ultra slow compared to the CTE version. I only have access to the B1 app itself, so I have no way to profile it, but it takes 3-5 seconds to execute while the CTE version is instant.

SELECT
 OCRD."CardCode"
 ,OCRD."CardName"
 ,Last_PO."DocNum" AS "Last PO"
 ,Last_PO."DocDate" AS "Last PO Date"
FROM
 OCRD LEFT JOIN LATERAL (
  SELECT "CardCode", "DocNum", "DocDate"
  FROM OPOR 
  WHERE "CANCELED" = 'N' AND "CardCode" = OCRD."CardCode"
  ORDER BY "DocEntry" DESC
  LIMIT 1
) Last_PO ON Last_PO."CardCode" = OCRD."CardCode" -- or 'ON 1 = 1'
WHERE
 OCRD."CardType" = 'S'
ORDER BY
 OCRD."CardCode"