OPEN-SQL query doesn't work at abap 4 EXEC SQL

Question

I'm new in ABAP (i'm working with ABAP4 on R/3) and there is something about my code that i don't understand.

My code is used to show the prices that are different between the tickets that enter through an external database, and the prices of the invoices within sap, to know the economic losses and in which tickets the price of the product has been misapplied. The fact is that in the ALV when I filter with a specific invoice number it filters me well, and if the date range is low as well. But the program keeps thinking inifinitely, .

First of all, what I do is a query to the internal database and save the values ​​that fall within the selection range of the alv in an itab. Then I make a loop in the itab itself and for each iteration I put the data of the query that I show you below in another table via:

   IF itab-importe_total <> itab-kzwi5.
     APPEND itab TO result_itab.
   ENDIF.

This is the code inside the connection:

    EXEC SQL.

      OPEN dbcur for

      SELECT C.COD_ALBARAN, P.LINEA, P.CODART, P.CANTIDAD, P.DESCUENTO, P.PRECIO, P.PRECIO_TOTAL, P.IMPORTE, P.IMPORTE_TOTAL, P.FECHA
      FROM [cm].[D_CLIE_ALBARANES_CAB_TBL] AS C
      LEFT JOIN [cm].[D_CLIE_ALBARANES_DET_TBL] AS P ON C.ID_CLIE_ALBARAN = P.ID_CLIE_ALBARAN
      WHERE (C.ID_TIPO_DOCUMENTO = :itab-tipo_documento) AND (C.COD_ALBARAN = :itab-num_tiquet) AND (P.LINEA = :posicio)

    ENDEXEC.

    DO.
      EXEC SQL.
        FETCH NEXT dbcur INTO   :wa_con-codalb,
                                :wa_con-linia,
                                :wa_con-codart,
                                :wa_con-cantid,
                                :wa_con-desc,
                                :wa_con-precio,
                                :wa_con-precio_total,
                                :wa_con-importe,
                                :wa_con-importe_total

      ENDEXEC.

      IF sy-subrc <> 0.
        EXIT.
      ELSE.
        itab-linia = wa_con-linia.
        itab-codart = wa_con-codart.
        itab-cantid = wa_con-cantid.
        itab-desc = wa_con-desc.
        itab-precio = wa_con-precio.
        itab-precio_total = wa_con-precio_total.
        itab-importe = wa_con-importe.
        itab-importe_total = wa_con-importe_total.

        MODIFY itab .

      ENDIF.

    ENDDO.

    EXEC SQL.
      CLOSE dbcur
    ENDEXEC.

If I execute this query at server changing the where values (because there are internal variables that change per iteration)

SELECT C.COD_ALBARAN, P.LINEA, P.CODART, P.CANTIDAD, P.DESCUENTO, P.PRECIO, P.PRECIO_TOTAL, P.IMPORTE, P.IMPORTE_TOTAL, P.FECHA
FROM [cm].[D_CLIE_ALBARANES_CAB_TBL] AS C
LEFT JOIN [cm].[D_CLIE_ALBARANES_DET_TBL] AS P ON C.ID_CLIE_ALBARAN = P.ID_CLIE_ALBARAN
WHERE (C.ID_TIPO_DOCUMENTO = '1') AND (C.COD_ALBARAN = '2020/13GI10/00009742') AND (P.LINEA = '1')

I get this output:

COD_ALBARAN LINEA   CODART  CANTIDAD    DESCUENTO   PRECIO  PRECIO_TOTAL    IMPORTE IMPORTE_TOTAL   FECHA
2020/13GI10/00009742    1   769109000   1.000   -57.55  5.7438  6.9500  9.050   10.950  2020-08-26 00:00:00.000

To prevent any date error conversion i removed the date condition

 AND (CONVERT(date, P.FECHA) >=  BETWEEN :s_data-low AND s_data-high ))

This is the declaration of table in which I fetch the data:

DATA: BEGIN OF wa_con,
    codalb(20),                         " Ex: 2020/13gi10/00000439
    linia LIKE vbrp-posnr,              " Linia - ex: 1, 2,[3]
    codart LIKE vbrp-matnr,             " Codi d'article per CMZ
    cantid LIKE vbrp-fkimg,             " Quantitat
    precio LIKE vbrp-netwr,             " Preu
    precio_total LIKE vbrp-netwr,       " Preu final
    desc LIKE vbrp-netwr,               " Descompte
    importe LIKE vbrp-netwr,            " Import
    importe_total LIKE vbrp-netwr,      " Import total
    fecha LIKE vbrk-fkdat,              " Data
END OF wa_con.

And this my itab:

DATA: BEGIN OF itab OCCURS 0,
    vbeln LIKE vbrk-vbeln,        " Número de factura
    fkdat LIKE vbrk-fkdat,        " Data
    spart LIKE vbrk-spart,        " Sector
    posnr LIKE vbrp-posnr,        " Posició de la factura
    matnr LIKE vbrp-matnr,        " Referencia / codi del material
    kunrg LIKE vbrk-kunrg,        " Client
    arktx LIKE vbrp-arktx,        " Descripció de la gestió / Per defecte nom material
    fkimg LIKE vbrp-fkimg,        " Quantitat unitaria facturada
    bukrs LIKE vbrk-bukrs,        " Organització - Ha de ser 10 13 o 16 per brico
    netwr LIKE vbrp-netwr,        " Valor net de la factura
    kzwi1 LIKE vbrp-kzwi1,        " Subtotal
    kzwi5 LIKE vbrp-kzwi5,        " Subtotal / Preu final
    kzwi6 LIKE vbrp-kzwi6,        " Subtotal
    vkorg LIKE vbrk-vkorg,        " Organització form 13gi
    mwsbp LIKE vbrp-mwsbp,        " IVA
    xblnr(20),                    " Referencia tiquet
    tipo_documento(1),            " tipo document cmz  1= carrec / 3= abonament
    num_tiquet(20) TYPE c,        " referencia tiquet cmz
    linia LIKE vbrp-posnr,        " CM posició
    codart LIKE vbrp-matnr,       " CM codi article
    cantid LIKE vbrp-fkimg,       " CM quantitat facturada
    desc LIKE vbrp-netwr,         " CM descompte
    precio LIKE vbrp-netwr,       " CM preu
    precio_total LIKE vbrp-netwr, " CM preu total
    importe LIKE vbrp-netwr,      " CM import
    importe_total LIKE vbrp-netwr," CM import total
 END OF itab.

Any tip?

Answer 1

As I said, the problem I had was that the program never finished its execution was because it executed a query of 18k records as many times as records were obtained in the first SELECT.

First of all I have taken out of the initial loop (which I do not show in the description of the problem, but it is where the connection to the external db is hosted) the select steament to the external database, filtering only by the range of date s_data-low s_data-high (and an arbitrarium common value that its repeated on the values that i need codalm) that I ask for the alv, and that result I put inside a separate table:

  DATA con_name LIKE dbcon-con_name.

  CLEAR wa_sap.

  IF sy-sysid = 'FEP'.
    con_name = 'CMZ'.
  ELSE.
    con_name = 'CMZTEST'.
  ENDIF.

  EXEC SQL.
    connect to :con_name
  ENDEXEC.

** Select a la db de cmz per imputar pre cada iteració del loop les dades de cada referència a la seva línia
  EXEC SQL.

    OPEN dbcur for

    SELECT C.COD_ALBARAN, P.LINEA, P.CODART, P.CANTIDAD, P.DESCUENTO, P.PRECIO, P.PRECIO_TOTAL, P.IMPORTE, P.IMPORTE_TOTAL, C.ID_TIPO_DOCUMENTO
    FROM [cmz].[D_CLIE_ALBARANES_CAB_TBL] AS C
    INNER JOIN [cmz].[D_CLIE_ALBARANES_DET_TBL] AS P ON C.ID_CLIE_ALBARAN = P.ID_CLIE_ALBARAN
    WHERE (P.FECHA BETWEEN :s_data-low AND :s_data-high) AND (C.CODALM = :codalm)

  ENDEXEC.


  DO.

    EXEC SQL.
      FETCH NEXT dbcur INTO     :wa_cmz-codalb,
                                :wa_cmz-linia,
                                :wa_cmz-codart,
                                :wa_cmz-cantid,
                                :wa_cmz-desc,
                                :wa_cmz-precio,
                                :wa_cmz-precio_total,
                                :wa_cmz-importe,
                                :wa_cmz-importe_total,
                                :wa_cmz-tipo_documento
    ENDEXEC.

    IF sy-subrc <> 0.
      EXIT.
    ELSE.
      APPEND wa_cmz TO it_cmz.

    ENDIF.

  ENDDO.

  EXEC SQL.
    CLOSE dbcur
  ENDEXEC.

Then inside the loop, all I need is the creation of the delivery number that will serve me later in the loop itself, and the conditional between the fields, appending all the row to the new table that i'll show.

  LOOP AT it_sap INTO wa_sap.

    wa_sap-tipo_documento = wa_sap-xblnr+2(1). " Linea

    tiquet = wa_sap-xblnr+9(7).
    anycreacio = wa_sap-fkdat+0(4).

    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT' " 
      EXPORTING
        input  = tiquet
      IMPORTING
        output = tiquet.

    CONCATENATE anycreacio '/' wa_sap-vkorg wa_sap-xblnr+7(2) '/' tiquet INTO wa_sap-codalb. " Creació del tiquet

    "CLEAR posicio.
    CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
      EXPORTING
        input  = wa_sap-posnr
      IMPORTING
        output = posicio.

    READ TABLE it_cmz INTO wa_cmz
    WITH KEY codalb = wa_sap-codalb
             tipo_documento = wa_sap-tipo_documento.

    IF sy-subrc = 0.
      wa_sap-importe_total = wa_cmz-importe_total.
      wa_sap-importe = wa_cmz-importe.
      wa_sap-precio = wa_cmz-precio.
      wa_sap-precio_total = wa_cmz-precio_total.
      wa_sap-desc = wa_cmz-desc.
      wa_sap-cantid = wa_cmz-cantid.

      MODIFY it_sap FROM wa_sap.

      IF wa_sap-importe_total <> wa_sap-kzwi5. 
        APPEND wa_sap TO it_resultats.
      ENDIF.

    ENDIF.

  ENDLOOP.

Thanks for the interest and appologies for the bad initial explanation. It is a somewhat limited language and it's difficult to find trainings (and the ones that exist are very expensive).