cx_sy_dynamic_osql_semantics

former_member808998 · ‎2022 Aug 19

First of all, I hope everyone had a good day.

I wrote a program. There are 10 input fields. It comes from different tables. I created an internal table by joining different tables. I made the where condition dynamic. The function works correctly, but when I search for the vbeln field, I get a dump.

It says the VBELN is ambiguous. I have an idea of where it originated, but it's not enough to kindle the fire.

I will be eagerly awaiting your feedback. Many thanks in advance for your time.

I am sharing the code below for those who want to examine it.

REPORT z4564_invoice_report_pt1.
TABLES: vbrk,
        vbrp.

TYPES : BEGIN OF gty_invoice_report,
          vbeln TYPE vbrk-vbeln,
          posnr TYPE vbrp-posnr,
          kunag TYPE vbrk-kunag,
          name1 TYPE kna1-name1,
          fkart TYPE vbrk-fkart,
          vtext TYPE tvfkt-vtext,
          matnr TYPE vbrp-matnr,
          maktx TYPE makt-maktx,
          fkimg TYPE vbrp-fkimg,
          vrkme TYPE vbap-vrkme,
        END OF gty_invoice_report.

DATA : gt_invoice_report TYPE TABLE OF gty_invoice_report,
       gt_range         TYPE STANDARD TABLE OF crmselstr,
       gt_output        TYPE STANDARD TABLE OF mcondition,
       gs_range        TYPE crmselstr.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS : s_vbeln FOR vbrk-vbeln,
                 s_matnr FOR vbrp-matnr,
                 s_kunag FOR vbrk-kunag,
                 s_fkart FOR vbrk-fkart,
                 s_vkorg FOR vbrk-vkorg OBLIGATORY.
PARAMETERS     : p_spras TYPE spras     OBLIGATORY DEFAULT 'TR'.
SELECTION-SCREEN END OF BLOCK b1.


LOOP AT s_vbeln.
  gs_range-table = 'VBRK'.
  gs_range-field = 'VBELN'.
  gs_range-sign = s_vbeln-sign.
  gs_range-option = s_vbeln-option.
  gs_range-low = s_vbeln-low.
  gs_range-high = s_vbeln-high.
  APPEND gs_range TO gt_range.
ENDLOOP.

LOOP AT s_matnr.
  CLEAR gs_range.
  gs_range-table = 'VBRP'.
  gs_range-field = 'MATNR'.
  gs_range-sign = s_matnr-sign.
  gs_range-option = s_matnr-option.
  gs_range-low = s_matnr-low.
  gs_range-high = s_matnr-high.
  APPEND gs_range TO gt_range.
ENDLOOP.

LOOP AT s_matnr.
  CLEAR gs_range.
  gs_range-table = 'VBRP'.
  gs_range-field = 'MATNR'.
  gs_range-sign = s_matnr-sign.
  gs_range-option = s_matnr-option.
  gs_range-low = s_matnr-low.
  gs_range-high = s_matnr-high.
  APPEND gs_range TO gt_range.
ENDLOOP.

LOOP AT s_kunag.
  CLEAR gs_range.
  gs_range-table = 'VBRK'.
  gs_range-field = 'KUNAG'.
  gs_range-sign = s_kunag-sign.
  gs_range-option = s_kunag-option.
  gs_range-low = s_kunag-low.
  gs_range-high = s_kunag-high.
  APPEND gs_range TO gt_range.
ENDLOOP.

LOOP AT s_fkart.
  CLEAR gs_range.
  gs_range-table = 'VBRK'.
  gs_range-field = 'FKART'.
  gs_range-sign = s_fkart-sign.
  gs_range-option = s_fkart-option.
  gs_range-low = s_fkart-low.
  gs_range-high = s_fkart-high.
  APPEND gs_range TO gt_range.
ENDLOOP.

LOOP AT s_vkorg. "Looping to get multiple (single) values
  CLEAR gs_range.
  gs_range-table = 'VBRK'.
  gs_range-field = 'VKORG'.
  gs_range-sign = s_vkorg-sign.
  gs_range-option = s_vkorg-option.
  gs_range-low = s_vkorg-low.
  gs_range-high = s_vkorg-high.
  APPEND gs_range TO gt_range.
ENDLOOP.


BREAK-POINT.

CALL FUNCTION 'CRS_CREATE_WHERE_CONDITION'
  TABLES
    ti_range      = gt_range
    to_cond       = gt_output
  EXCEPTIONS
    invalid_input = 1
    OTHERS        = 2.

      SELECT vbrk~vbeln,
             vbrp~posnr,
             vbrk~kunag,
             kna1~name1,
             vbrk~fkart,
             tvfkt~vtext,
             vbrp~matnr,
             makt~maktx,
             vbrp~fkimg,
             vbrp~vrkme FROM vbrk
                                       JOIN vbrp  ON vbrk~vbeln = vbrp~vbeln
                                  LEFT JOIN kna1  ON kna1~kunnr = vbrk~kunag
                                  left JOIN tvfkt ON vbrk~fkart = tvfkt~vtext AND tvfkt~spras = @p_spras
                                       JOIN makt  ON makt~matnr = vbrp~matnr  AND makt~spras  = @p_spras
                                                                                                         INTO CORRESPONDING FIELDS OF TABLE @gt_invoice_report
                                                                                                         WHERE (gt_output).
        BREAK-POINT.

Ryan-Crosby · ‎2022 Aug 19

Take a look at the dynamic WHERE clause that was generated, if it only contains a reference to VBELN and not VBRK~VBELN, then it becomes a problem because VBRP also contains the field VBELN.

former_member808998 · ‎2022 Aug 19

Thank you very much Ryan for your suggestion. However, when I control the debug screen, I see that the where clause provides the where condition with the vbeln field of the vbrk table. The reason for the error, as you said, is that the sql command does not know which table the vbeln field belongs to. There seems to be no problem on the debug screen, but I still keep getting dumps. Still grateful for your support

Sandra_Rossi · ‎2022 Aug 21

Only you can debug, we can't do it for you. Otherwise, you need to catch the exception and tell us the exact message and all information coming with it.

Note that you do twice "LOOP AT s_matnr".

I advise to use the function module FREE_SELECTIONS_RANGE_2_WHERE, it's "more standard" than CRS_CREATE_WHERE_CONDITION, because it's directly used by the official FREE_SELECTIONS_DIALOG / SUBMIT ... WITH FREE SELECTIONS ... etc.

Moreover, there doesn't seem to be a good reason to use dynamic select. Why don't you just use this code:

SELECT
...
WHERE vbrk~vbeln IN @s_vbeln
  AND matnr      IN @s_matnr
  AND kunag      IN @s_kunag
  AND fkart      IN @s_fkart
  AND vkorg      IN @s_vkorg.

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

cx_sy_dynamic_osql_semantics