cancel
Showing results for 
Search instead for 
Did you mean: 

Using LIMIT 1 instead of TOP 1 in subquery

patrickbachmann
Active Contributor
0 Kudos
11,787

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

Accepted Solutions (1)

Accepted Solutions (1)

anindya_bose
Active Contributor
0 Kudos

Hi Patrick

This works for me , I am on SPS 10.

Even MAX or MIN function works .  You might have some syntax error may be ?  In your post, I see one extra parenthesis

Regards

Anindya

anindya_bose
Active Contributor
0 Kudos

LIMIT also works 

SELECT CUSTOMER ,CITY,( SELECT  PROFIT_CTR FROM "<SCHEMA>"."/BI0/PPROFIT_CTR" LIMIT 1) AS PC  FROM "<SCHEMA>"."/BI0/PCUSTOMER"

WHERE CUSTOMER LIKE '05%' AND CITY != ''

I used WHERE CLAUSE just to fetch some good records from Dev system .

Regards

Anindya

suresh_devarajan
Explorer
0 Kudos

I think the issue is when using correlated subquery. I get the error for both TOP 1 & LIMIT 1 on the SPS 11 system, when trying the example provided in the OSS note.


Regards,

Suresh

patrickbachmann
Active Contributor
0 Kudos

Thanks guys, I will try both your suggestions shortly today.  I'm using SPS8 rev85 but I do have an SPS10 system I can also try. 

-Patrick

patrickbachmann
Active Contributor
0 Kudos

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

anindya_bose
Active Contributor
0 Kudos

I tried the sub-query with WHERE clause , that worked.    What I used is like  "WHERE PROFIT_CTR LIKE 'something' . 

Can you please share what actually you are trying to do ?  May be a join with Dummy column can also help ?

Regards

Anindya

patrickbachmann
Active Contributor
0 Kudos

Something like this;

select

a.comp_code,

a.customer,

(select top1 profit_center from GL_TABLE b where a.comp_code = b.comp_code) as PC,

a.name,

a.amount

from AR_TABLE a

See how I'm correlating table a with table b? 

Thanks

-Patrick

anindya_bose
Active Contributor
0 Kudos

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

lbreddemann
Active Contributor
0 Kudos

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

patrickbachmann
Active Contributor
0 Kudos

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

Answers (1)

Answers (1)

suresh_devarajan
Explorer
0 Kudos

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,