Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Using LIMIT 1 instead of TOP 1 in subquery

Hi folks,

I'm trying to do a subquery with TOP1 which isn't supported so I was reading that LIMIT 1 should work but I'm still getting similar error correlated subquery cannot have TOP or ORDER BY.

EXAMPLE:

select

field1,

field2,

(select PROFIT_CTR from "MySchema"."MyTable" where COMP_CODE = '1234' LIMIT 1) as PC)

from "MySchema"."OtherTable" where....

etc

Any suggestions?

Thanks!

-Patrick

replied

Hi Patrick

I agree with Anindya here. What you seem to want is a sort of function that provides the profit center for a given company code.

SAP HANA (at least not in your revision) doesn't support scalar functions that perform SQL, so you need to use plain SQL functionality instead.

One way is to go for the inline-query approach, which will evaluate the sub-query for every result row in the query. Even though the query rewrite will turn this into a left outer join internally, the evaluation still goes record by record - not very efficient.

Also I find it pretty hard to read .

Why not instead use a (inline) view ?

select

  a.comp_code,

  a.customer,

  pctr.profit_center as PC,

  a.name,

  a.amount

from

     AR_TABLE a

    left outer join (

                    select comp_code, max(profit_center) as profit_center

                    from GL_TABLE

                    group by comp_code

                    ) pctr

     on a.comp_code = pctr.comp_code;

As you might use the profit center resolution more often, you could even go a step further and simply create a view that encapsulates the PCTR query.

That would make the resulting query even more readable.

As a benefit of this, the aggregation to find the single profit center entry via MAX() can be pushed further down and executed with a higher parallel degree.

Filter conditions on AR_TABLE.comp_code will also be pushed down if possible.

The only case I see where this construct is less efficient from a processing point of view is when you select only a few records from AR_TABLE and the filter is not based on the join columns. In that case, the sub-query will still be fully executed and the join will be run against the full set of distinct MAX(profit_center) values. Given the nature of profit centers, I wouldn't expect too many here (probably <100.000) which will still result in a quick join.

my 0.02cts

Lars

2 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question