on 03-30-2016 10:31 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ok Anindya, I think your example works because the subquery does not include a where condition that correlates it to the primary query which I think was Suresh point. I tried a more simple example like yours where the subquery didn't have a where condition and it was successful. But i need my subquery to tie to the primary query.
Now I'm tinkering with rank function with no luck just yet but still playing.
-Patrick
You might try MAX function, that seems to be working for me.
(I assumed, one Comp Code to Profit Center is 1:1 in your case and that is why you want to have TOP 1 ; if that is the case, MAX would get you same result )
SELECT
A.COMP_CODE ,
A.DEBITOR ,
( SELECT MAX( B.PROFIT_CTR) FROM "SCHEMA"."<GL_TABLE>" B WHERE B.COMP_CODE = A.COMP_CODE ) AS PROFIT_CTR
FROM "SCHEMA"."<AR_TABLE>" A
Is there a reason why you want to avoid join ?
Regards
Anindya
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
Guys thanks for your feedback. Actually I have cases with multiple profit centers per company code and lots of other strange scenarios I'm muddling through. But yes I actually did go down a different path at least for now so don't need the top1 at moment but I still want to tinker with this and will close thread soon.
Thanks again!
-Patrick
Appears like both TOP & LIMIT are not supported.
You can try using the RANK or ROW_NUMBER option shown in the SAP Note 1885029.
Something like,
select
field1,
field2,
(select PROFIT_CTR (select PROFIT_CTR, rank() over (order by COMP_CODE) rnk from "MySchema"."MyTable" where COMP_CODE = '1234') WHERE rnk <= 1 as PC)
from "MySchema"."OtherTable" where....
etc
Thanks,
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.