Using LIMIT 1 instead of TOP 1 in subquery
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.
(select PROFIT_CTR from "MySchema"."MyTable" where COMP_CODE = '1234' LIMIT 1) as PC)
from "MySchema"."OtherTable" where....
Lars Breddemann replied
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 ?
pctr.profit_center as PC,
left outer join (
select comp_code, max(profit_center) as profit_center
group by comp_code
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.