Help with query

[Former Member]

Good day experts, i have created a report which filter order dates and show the materials, but also to display components used. In the table stpo there is a field idnrk which is the component, but I have not been able to implement the query please can you help me. I send you the code that i am using:

Report zped.

TABLES:
     vbak, vbap, mast, stpo.

*---------------------------------------------*
*      S E L E C T I O N   S C R E E N        *
*---------------------------------------------*

SELECTION-SCREEN: BEGIN OF BLOCK block01 WITH FRAME TITLE title01.

SELECT-OPTIONS: so_vdatu  FOR vbak-vdatu.

SELECTION-SCREEN: END OF BLOCK block01.

*---------------------------------------------*
*      I N I T I A L I Z A T I O N            *
*---------------------------------------------*

INITIALIZATION.

   SELECT SINGLE ddtext FROM dd02t INTO title01
     WHERE tabname = 'VBAK' AND ddlanguage = sy-langu.       "#EC *

*---------------------------------------------*
*       T Y P E   D E F I N I T I O N S       *
*---------------------------------------------*

   TYPES: BEGIN OF gty_result ,
           vbeln TYPE vbap-vbeln,
           werks TYPE vbap-werks,
           posnr TYPE vbap-posnr,
           matnr TYPE vbap-matnr,
           makt LIKE makt-maktx,
           meins TYPE mara-meins,
           stlnr TYPE stpo-stlnr,
           idnrk TYPE stpo-idnrk,

          END OF gty_result.

   TYPES: BEGIN OF gty_result1 ,
          vbeln TYPE vbap-vbeln,
          werks TYPE vbap-werks,
          posnr TYPE vbap-posnr,
          matnr TYPE mast-matnr,
          makt LIKE makt-maktx,
          meins TYPE mara-meins,
          stlnr TYPE stpo-stlnr,
          idnrk TYPE stpo-idnrk,
         END OF gty_result1.

*---------------------------------------------*
*       D A T A   D E C L A R A T I O N       *
*---------------------------------------------*

   DATA: gt_result  TYPE STANDARD TABLE OF gty_result,
         gs_result  LIKE LINE OF gt_result,
         gt_result1  TYPE STANDARD TABLE OF gty_result1,
  
         gt_maktx TYPE makt-maktx,
         gs_meins TYPE mara-meins,
         gt_stpo  TYPE STANDARD TABLE OF stpo WITH HEADER LINE,
         gs_idnrk TYPE stpo-idnrk,

         go_alv           TYPE REF TO cl_gui_alv_grid,
         gs_fcat          TYPE lvc_s_fcat,
         gt_fcat          TYPE lvc_t_fcat.

   FIELD-SYMBOLS: <fs_datos> TYPE gty_result,
                  <fs_st>  TYPE stpo.

*---------------------------------------------*
*       D A T A   S E L E C T I O N           *
*---------------------------------------------*
START-OF-SELECTION.

   SELECT vbak~vdatu
          vbap~vbeln
          vbap~werks
          vbap~posnr
          vbap~matnr "A estos materiales se les consultarán las listas de material
     INTO CORRESPONDING FIELDS OF TABLE gt_result
     FROM vbap INNER JOIN vbak ON vbap~vbeln = vbak~vbeln
     WHERE vdatu IN so_vdatu.

*BREAK-POINT.

   LOOP AT gt_result ASSIGNING <fs_datos>.

     CLEAR: gt_maktx.
     SELECT SINGLE maktx INTO gt_maktx FROM makt
       WHERE matnr EQ <fs_datos>-matnr AND spras EQ sy-langu.
       <fs_datos>-makt = gt_maktx .

     CLEAR: gs_meins.
     SELECT SINGLE meins INTO gs_meins FROM mara
       WHERE matnr EQ <fs_datos>-matnr.
       <fs_datos>-meins = gs_meins .

     CLEAR: gs_idnrk.
      SELECT SINGLE idnrk INTO gs_idnrk FROM stpo
        WHERE stlnr EQ <fs_datos>-stlnr.
        <fs_datos>-idnrk = gs_idnrk.

     APPEND <fs_datos> TO gt_result1.
   ENDLOOP.

*---------------------------------------------*
*        A L V   D I S P L A Y                *
*---------------------------------------------*

* main program
   PERFORM main_prog.

*&---------------------------------------------------------------------*
*&      FORM MAIN_PROG
*&---------------------------------------------------------------------*
*         The main program
*----------------------------------------------------------------------*
FORM main_prog.

* build field catalog for the ALV grid
   PERFORM create_fcat.

* display list
   CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY_LVC'
     EXPORTING
       it_fieldcat_lvc = gt_fcat
     TABLES
       t_outtab        = gt_result1
     EXCEPTIONS
       program_error   = 1
       OTHERS          = 2.

   IF sy-subrc <> 0.
     MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
       WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
   ENDIF.

ENDFORM.                 " MAIN_PROG.

*&---------------------------------------------------------------------*
*&      FORM CREATE_FCAT
*&---------------------------------------------------------------------*
*         Generate field catalog for the ALV
*----------------------------------------------------------------------*
FORM create_fcat.

   CLEAR gs_fcat.
   gs_fcat-col_pos   = 1  .
   gs_fcat-key       = 'X'.
   gs_fcat-fieldname = 'VBELN'.
   gs_fcat-tabname   = 1.
   gs_fcat-datatype  = 'CHAR'.
   gs_fcat-inttype   = 'C'.
   gs_fcat-domname   = 'VBELN'.
   gs_fcat-intlen    = '000020'.
   gs_fcat-coltext   = 'Doc.V'.
   gs_fcat-outputlen = gs_fcat-intlen.
   APPEND gs_fcat TO gt_fcat.

   CLEAR gs_fcat.
   gs_fcat-col_pos   = 2  .
   gs_fcat-fieldname = 'POSNR'.
   gs_fcat-tabname   = 1.
   gs_fcat-datatype  = 'CHAR'.
   gs_fcat-inttype   = 'C'.
   gs_fcat-domname   = 'MATNR'.
   gs_fcat-intlen    = '000012'.
   gs_fcat-coltext   = 'Pos.'.
   gs_fcat-outputlen = gs_fcat-intlen.
   APPEND gs_fcat TO gt_fcat.

   CLEAR gs_fcat.
   gs_fcat-col_pos   = 3  .
   gs_fcat-fieldname = 'WERKS'.
   gs_fcat-tabname   = 1.
   gs_fcat-datatype  = 'CHAR'.
   gs_fcat-inttype   = 'C'.
   gs_fcat-domname   = 'WERKS'.
   gs_fcat-intlen    = '000008'.
   gs_fcat-coltext   = 'Ce.'.
   gs_fcat-outputlen = gs_fcat-intlen.
   APPEND gs_fcat TO gt_fcat.

   CLEAR gs_fcat.
   gs_fcat-col_pos   = 4  .
   gs_fcat-fieldname = 'MATNR'.
   gs_fcat-tabname   = 1.
   gs_fcat-datatype  = 'CHAR'.
   gs_fcat-inttype   = 'C'.
   gs_fcat-domname   = 'MATNR'.
   gs_fcat-intlen    = '000036'.
   gs_fcat-coltext   = 'Material'.
   gs_fcat-outputlen = gs_fcat-intlen.
   APPEND gs_fcat TO gt_fcat.

   CLEAR gs_fcat.
   gs_fcat-col_pos   = 5  .
   gs_fcat-fieldname = 'MAKT'.
   gs_fcat-tabname   = 1.
   gs_fcat-datatype  = 'CHAR'.
   gs_fcat-inttype   = 'C'.
   gs_fcat-domname   = 'MATNR'.
   gs_fcat-intlen    = '000045'.
   gs_fcat-coltext   = 'T.C.'.
   gs_fcat-outputlen = gs_fcat-intlen.
   APPEND gs_fcat TO gt_fcat.

   CLEAR gs_fcat.
   gs_fcat-col_pos   = 6  .
   gs_fcat-fieldname = 'MEINS'.
   gs_fcat-tabname   = 1.
   gs_fcat-datatype  = 'CHAR'.
   gs_fcat-inttype   = 'C'.
   gs_fcat-domname   = 'MEINS'.
   gs_fcat-intlen    = '000006'.
   gs_fcat-coltext   = 'UMB'.
   gs_fcat-outputlen = gs_fcat-intlen.
   APPEND gs_fcat TO gt_fcat.

   CLEAR gs_fcat.
   gs_fcat-col_pos   = 7  .
   gs_fcat-fieldname = 'IDNRK'.
   gs_fcat-tabname   = 1.
   gs_fcat-datatype  = 'CHAR'.
   gs_fcat-inttype   = 'C'.
   gs_fcat-domname   = 'MATNR'.
   gs_fcat-intlen    = '000036'.
   gs_fcat-coltext   = 'Componente'.
   gs_fcat-outputlen = gs_fcat-intlen.
   APPEND gs_fcat TO gt_fcat.

ENDFORM.                 " CREATE_FCAT.

Thank you for your valuable time.

Regards.

[Former Member]

You really need to describe the nature of your problem in some kind of detail!

Neal

[Former Member]

Thanks for replying, the problem is that I can not display the field idnrk of the table stpo.

I hope you can help.

Regards.

[Former Member]

Hi,

Why are you using 2 tables...gt_result and gt_result1, both are the same...

No need, just in the first select use gt_result and then

loop gt_result into wa_result.

do ur logic.

modify gt_result from wa_result.

endloop.

no need to create tables twice...

Regards

Miguel

[vladimir_erakovic]

Hi Brujo,

Put like this:

LOOP AT gt_result ASSIGNING <fs_datos>.

      CLEAR: gt_maktx.

      SELECT SINGLE maktx INTO gt_maktx FROM makt

        WHERE matnr EQ <fs_datos>-matnr AND spras EQ sy-langu.

        <fs_datos>-makt = gt_maktx .

      CLEAR: gs_meins.

      SELECT SINGLE meins INTO gs_meins FROM mara

        WHERE matnr EQ <fs_datos>-matnr.

        <fs_datos>-meins = gs_meins .

      CLEAR: gs_idnrk.

       SELECT SINGLE idnrk INTO gs_idnrk FROM stpo

         WHERE stlnr EQ <fs_datos>-stlnr.

         <fs_datos>-idnrk = gs_idnrk.

    ENDLOOP.

and put gt_result in reuse_alv_grid_display_lvc  and it will work fine.

Regards,

Vladimir

[vladimir_erakovic]

But it also works as you did. So my only guess is that you don't have data on the client on which you test it.

[Former Member]

Hi Brujo ,

SELECT vbak~vdatu

          vbap~vbeln

          vbap~werks

          vbap~posnr

          vbap~matnr "A estos materiales se les consultarán las listas de material

     INTO CORRESPONDING FIELDS OF TABLE gt_result

     FROM vbap INNER JOIN vbak ON vbap~vbeln = vbak~vbeln

     WHERE vdatu IN so_vdatu.

In the above select query you are filling the table gt_result . but i can see no where u are fetching the field stlnr .

so you can't expect the fieldsymbol <fs_datos>-stlnr will have values to compare in where condition of your next select query .

SELECT SINGLE idnrk INTO gs_idnrk FROM stpo

        WHERE stlnr EQ <fs_datos>-stlnr.

        <fs_datos>-idnrk = gs_idnrk.

what you have to do is :

SELECT vbak~vdatu

              vbap~vbeln

               vbap~werks

               vbap~posnr

               vbap~matnr

               vbap~stlnr

              INTO CORRESPONDING FIELDS OF TABLE gt_result

             FROM vbap INNER JOIN vbak ON vbap~vbeln = vbak~vbeln

              WHERE vdatu IN so_vdatu .

then your next select query will work fine .

Thanks & Regards

Shiv ,

[Former Member]

Thank you all for responding, I am entering the date of delivery and shows me his position, his materials, short text, but the problem is that I can not display the field idnrk, this in the table STPO. Try what I said but without success.

Experts hope you can guide me with this query.

Regars.

[vladimir_erakovic]

Just correct your select:

   SELECT vbak~vdatu

          vbap~vbeln

          vbap~werks

          vbap~posnr

          vbap~matnr "A estos materiales se les consultarán las listas de material

          vbap~stlnr

     INTO CORRESPONDING FIELDS OF TABLE gt_result

     FROM vbap INNER JOIN vbak ON vbap~vbeln = vbak~vbeln

     WHERE vdatu IN so_vdatu.

And you will get idnrk from loop.

[venkat_aileni]

Hi-

At first have you checked whether there are any entries maintained in VBAP for the field STLNR? If this is not maintained at your table obviously your query cannot fetch field idnrk from STPO table.

Thanks,

Venkat