Subquery throws error: "Wrong table name or table alias"

Question

In SAP the following request works well, but has result I obtain #5000000 lines for 3000 materials (MATNR):

SELECT  k~bldat a~matnr k~bldat e~maktx a~mtart a~brgew
   a~ntgew a~gewei a~volum a~laeda mseg~bwart k~budat
INTO CORRESPONDING FIELDS OF TABLE itab
   FROM mseg
     INNER JOIN makt AS e ON e~matnr   = mseg~matnr
      INNER JOIN mara AS a ON a~matnr = mseg~matnr
       INNER JOIN mkpf AS k ON k~mblnr = mseg~mblnr
     WHERE a~mtart in ('ZRSV','ZVTP').

But If I just want the last movement/Date for each of my MATNR, the following request return this error: Wrong table name or table alias name table alias name "(SELECT MAX( T".

SELECT k~bldat a~matnr k~bldat e~maktx a~mtart a~brgew
   a~ntgew a~gewei a~volum a~laeda mseg~bwart k~budat
INTO CORRESPONDING FIELDS OF TABLE itab
   FROM mseg
     INNER JOIN makt AS e ON e~matnr   = mseg~matnr
      INNER JOIN mara AS a ON a~matnr = mseg~matnr
       INNER JOIN mkpf AS k ON k~mblnr = mseg~mblnr
     WHERE a~mtart in ('ZRSV','ZVTP')
         AND k~bldat = (SELECT MAX( t~bldat ) FROM mkpf AS t
                WHERE t~mblnr = mesg~mblnr).

Any idea of the syntax problem? Thanks in advance.

score 1 · Accepted Answer · answered May 29 '13 at 07:54

1

You need to add spaces before the sub-SELECT-statement:

AND k~bldat = ( SELECT MAX ... = ~mblnr ).
               ^ HERE         AND HERE ^

answered May 29 '13 at 07:54

vwegert

18,371
3
37
55

Thank you for the advice! Now I can execute the request. But, the request return all of the entries. It's like the MAX() filter doesn't works... – FredM May 29 '13 at 08:30
I'm not that fluent with the MM tables - would it be possible to select the entire dataset (without the subquery), then use `SORT itab BY mblnr bldat DESCRNDING` (this leaves the most recent entry for each `mblnr` on top) and then `DELETE ADJACENT DUPLICATES FROM itab COMPARING mblnr`? – vwegert May 29 '13 at 08:44
Ok, I'll follow your advice. (even if I would have prefer to eliminate the data at the entry and not to populate the itab with a lot of data I don't need). Thank again for your help! – FredM May 29 '13 at 09:31

Subquery throws error: "Wrong table name or table alias"

1 Answers1