CODEWITHSUNDEEP
sqlsubqueryhana
djk
djk

Reputation: 973

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?

Edit:
It seems to add to the problem that the column "notes_extra" is of type NCLOB. Any alternative I try to come up with ends at some point in an aggregate function (MAX, FIRST_VALUE, ...) on this column, which results in: SAP DBTech JDBC: [264]: invalid datatype: LOB Type in aggregation func

Upvotes: 1

Views: 15309

Answers (4)

Tony Marston
Tony Marston

Reputation: 57

You can now use the FIRST_VALUE() function for this, as in:

SELECT (FIRST_VALUE(col ORDER BY col) FROM ... WHERE ...) AS ...

Upvotes: 1

Lars Br.
Lars Br.

Reputation: 10396

"I don't care" is not something databases are good at handling. If there is no option to actually decide which row to take, then the data model simply is not fit to answer this question.

 select "id", "notes_extra" from 
     (SELECT c."id", c."notes_extra" 
            , row_number () over 
               (partition by c."id" 
                ORDER BY c."id" asc , c."start" desc) as RN 
      FROM orders c ) 
  where rn =2;

can deliver the 2nd last of the order comments based on the general sorting. Note that this delivers the same default tie-breaking behaviour as your initial query and that is: arbitrary.

Upvotes: 4

user330315
user330315

Reputation:

You don't need a sub-select at all, use a window function instead:

SELECT c."id", c."key_link", 
       lag(c."notes_extra") over (partition by c."id" order by c."start" desc) as "previous_notes" 
FROM "Orders" c
ORDER BY c."id";

Upvotes: 2

Lars Br.
Lars Br.

Reputation: 10396

If you look only for the "first" column by a given sort order, you can always use the max()/min() aggregation. Not sure what problem you ran into, but that's definitively possible. For your requirement I'd however recommend to implement the logic in a SQL view (and join this with your main query) or as a table udf.

Upvotes: 0

Related Questions