2013年1月31日 星期四

[Code] How to use subquery to get correct record


Suppose ZSD001 is filled as below:
MATNR         DATAB           APPOINTFEE
----------------------------------------
MA12345       2008/12/01      500
MA12345       2009/12/01      1,000
MA12345       2009/12/15      2,000
Example Code:
SELECT SINGLE APPOINTFEE INTO GT_ITAB-APPOINTFEE
    FROM ZSD001
   WHERE MATNR = 'MA12345'
     AND DATAB = ( SELECT MAX( DATAB )
                       FROM ZSD001
                      WHERE MATNR EQ 'MA12345'
                        AND DATAB LE '20091202'
                 ).

* GT_ITAB-APPOINTFEE == 1,000

Another example: (paste from RVAUFSTA)
SELECT OBJNR FROM JSTO AS APPENDING CORRESPONDING FIELDS OF
                     TABLE ONR_TAB WHERE A~OBJNR LIKE SDCON-OBJNR_VB AND
                                         A~OBJNR IN R_S_OBJNR AND
                                         A~OBTYP EQ SDCON-HEADER AND
                                         A~STSMA NE STSMA_INI AND
             EXISTS SELECT OBJNR FROM JEST WHERE OBJNR EQ A~OBJNR AND
                     STAT LIKE 'E%' AND INACT EQ SPACE ORDER BY OBJNR.

沒有留言:

張貼留言