How can I use SUBSELECT with JOIN?

Question

I want to build an OpenSQL query to return only the max stat value for all objnr.
This works:

SELECT O.OBJNR
  FROM JCDS AS O
  WHERE O.OBJNR = 'Obj12345'
  AND STAT = ( SELECT MAX(STAT) 
                 FROM JCDS AS I 
                 WHERE I.OBJNR = O.OBJNR )

However, if I try to use a join, and not provide objnr directly, I get an error message:

SELECT O.OBJNR, O.STAT
  FROM JCDS AS O
  INNER JOIN AFVC 
      ON AFVB.OBJNR = O.OBJNR
  WHERE "AUFPL" = 'Aufpl12345' 
  AND O.STAT = ( SELECT MAX(STAT) 
                   FROM JCDS AS I 
                   WHERE I.OBJNR = O.OBJNR )

The error message is meaningless; "SQL error"

---

EDIT 2023: the original question was tagged sql hana (along with opensql), and contained . after the aliases (not OpenSQL), hence the confusion in the answers. The code which was said "to work" had an error too. The author later revised the question to replace all . with ~, so the question was about OpenSQL for sure. hana was probably mistaken with s4hana (contains the tables JCDS and AFVC). Based on OP's answer (the only problem was a typo of AFVB instead of AFVC), I edited the question so that the original one is kept to comply with the original answers, and the below part is added to comply with the OP question and answer. I hope this edit brings clarity, and doesn't betray what the OP meant to say. I checked the code "this works" below, using ABAP/OpenSQL 7.52 (it was not the OP version for sure) and S/4HANA 1709.

I want to build an OpenSQL query in S/4HANA system to return only the max stat value for all objnr.

This works:

    SELECT O~OBJNR
      FROM JCDS AS O
      WHERE O~OBJNR = 'Obj12345'
      AND STAT = ( SELECT MAX( STAT ) 
                     FROM JCDS AS I 
                     WHERE I~OBJNR = O~OBJNR )
      INTO TABLE @DATA(result).

However, if I try to use a join, and not provide objnr directly, I get an error message:

    SELECT O~OBJNR, O~STAT
      FROM JCDS AS O
      INNER JOIN AFVC 
          ON AFVB~OBJNR = O~OBJNR
      WHERE AUFPL = 'Aufpl12345' 
      AND O~STAT = ( SELECT MAX( STAT ) 
                       FROM JCDS AS I 
                       WHERE I~OBJNR = O~OBJNR )
      INTO TABLE @DATA(result).

The error message is "AFVB" is unknown in the current FROM clause (obscured by an alias name or unknown in this position) and the cursor points on AFVB.

Answer 1

you could simplify your query like this by grouping by the objectnumbers:

SELECT O.OBJNR, MAX(O.STAT)
FROM JCDS AS O
GROUP BY O.OBJNR

Depending on which columns you need to display from you joined tables you could either extend the group by:

SELECT O.OBJNR, A.SOMECOL, MAX(O.STAT)
FROM JCDS AS O
INNER JOIN AFVC AS A
ON AFVB.OBJNR = O.OBJNR
GROUP BY O.OBJNR, A.SOMECOL

or use a subquery:

SELECT MAXSTAT.OBJNR, MAXSTAT.STAT, A.SOMECOL
FROM (
SELECT O.OBJNR, MAX(O.STAT) STAT
FROM JCDS AS O
GROUP BY O.OBJNR ) MAXSTAT
INNER JOIN AFVC AS A
ON AFVB.OBJNR = MAXSTAT.OBJNR

Answer 2

To answer the question after EDIT 2023 (question about OpenSQL / ABAP SQL), in short, it's a typo: instead of AFVB~OBJNR, it should be AFVC~OBJNR. This code compiles in S/4HANA 1709 ABAP 7.52:

SELECT O~OBJNR, O~STAT
  FROM JCDS AS O 
  INNER JOIN AFVC 
    ON afvC~OBJNR = O~OBJNR 
  WHERE AUFPL = '0123456789' 
    AND O~STAT = ( SELECT MAX( STAT ) 
                     FROM JCDS AS I 
                     WHERE I~OBJNR = O~OBJNR )
  INTO TABLE @DATA(result).

NB:

• The spaces are mandatory in MAX( STAT )
• INTO ... is mandatory
• The double quotes in "AUFPL" = '0123456789' are invalid in ABAP SQL (double quotes are used for defining comments in ABAP)

Answer 3

Are you looking for the record with the highest stat per objnr? You can use a window function for this:

select *
from
(
  select
    jcds.*,
    max(stat) over (partition by objnr) as max_stat
  from jcds
) data
where stat = max_stat;

Answer 4

The subselect works flawlessly with a JOIN, if I do not mistype the tablename:

    SELECT O~OBJNR, O~STAT
      FROM JCDS AS O
      INNER JOIN AFVC 
    --    ON afvB~OBJNR = O~OBJNR
    --          |
          ON afvC~OBJNR = O~OBJNR
      WHERE AUFPL = '0123456789' 
      AND O~STAT = ( SELECT MAX(STAT) 
                       FROM JCDS AS I 
                       WHERE I~OBJNR = O~OBJNR )