Skip to Content

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Mar 31, 2016 at 01:30 AM

    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


    Capture.PNG (13.0 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Mar 30, 2016 at 11:34 PM

    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,

    Add comment
    10|10000 characters needed characters exceeded