jeudi 7 juillet 2016

Equivalent to subquery without TOP or ORDER BY in HANA SQL

The SAP SQL Converter gave me this as output:

SELECT c."id", c."key_link", 
    (SELECT TOP 1 "notes_extra" 
    FROM "Orders" c2 
    WHERE c2."id" = c."id" AND c2."start" < c."start" 
    ORDER BY c2."start" DESC) AS "previous_notes" 
FROM "Orders" c
ORDER BY c."id";

Unfortunately, SAP HANA SPS 10 does not accept it:
SAP DBTech JDBC: [309]: correlated subquery cannot have TOP or ORDER BY

I am struggeling converting the query into something without TOP and ORDER BY. Tried a few things with WHERE "start" = (SELECT MAX("start") but since "start" can have duplicate values, I end up with: SAP DBTech JDBC: [305]: single-row query returns more than one row

So what's the correct equivalent in HANA SQL?

1 commentaire: