topic Re: Help with query in Application Development and Automation Discussions

Help with query

Former Member — Mon, 28 Oct 2013 18:01:46 GMT

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.

Re: Help with query

Former Member — Mon, 28 Oct 2013 18:16:04 GMT

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

Neal

Re: Help with query

Former Member — Mon, 28 Oct 2013 18:21:41 GMT

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

Re: Help with query

vladimir_erakovic — Mon, 28 Oct 2013 18:54:01 GMT

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

Re: Help with query

vladimir_erakovic — Mon, 28 Oct 2013 19:01:12 GMT

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.

Re: Help with query

Former Member — Tue, 29 Oct 2013 04:22:01 GMT

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 ,

Re: Help with query

Former Member — Tue, 29 Oct 2013 14:23:30 GMT

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

I hope you can help.

Regards.

Re: Help with query

Former Member — Tue, 29 Oct 2013 14:29:15 GMT

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.

Re: Help with query

vladimir_erakovic — Tue, 29 Oct 2013 14:35:27 GMT

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.

Re: Help with query

venkat_aileni — Tue, 29 Oct 2013 14:39:04 GMT

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