Search code examples
abapopensql

Query for apha field using string literal

How can I use string literal to query tables if the raw value in the table is different from the value I use in the string literal?

I made a Z table that uses MATNR and 2 other fields as primary keys. In a dummy programm I filled the table with 4 rows using this code

delete from z_test_db.
data ls_tab type z_test_db.
data now type timestamp.

get time stamp field now.
ls_tab = value #( matnr = '4711' vorne = 0010 terminal = 'AAA' etkpgm = 'XXX' drucker = 'MO-ED-01' voretkm = 1 etkcopy = 1 eruser = sy-uname ertime = now aeuser = '' aetime = 0 kzaktv = 'X' kzaaeu = '' kzaaet = 0 ).
insert z_test_db from ls_tab.

get time stamp field now.
ls_tab = value #( matnr = '4712' vorne = 0010 terminal = 'AAA' etkpgm = 'XXX' drucker = 'MO-ED-01' voretkm = 1 etkcopy = 1 eruser = sy-uname ertime = now aeuser = '' aetime = 0 kzaktv = 'X' kzaaeu = '' kzaaet = 0 ).
insert z_test_db from ls_tab.

get time stamp field now.
ls_tab = value #( matnr = '4711' vorne = 0010 terminal = 'BBB' etkpgm = 'XXX' drucker = 'MO-ED-01' voretkm = 1 etkcopy = 1 eruser = sy-uname ertime = now aeuser = '' aetime = 0 kzaktv = 'X' kzaaeu = '' kzaaet = 0 ).
insert z_test_db from ls_tab.

get time stamp field now.
ls_tab = value #( matnr = '4712' vorne = 0010 terminal = 'BBB' etkpgm = 'XXX' drucker = 'MO-ED-01' voretkm = 1 etkcopy = 1 eruser = sy-uname ertime = now aeuser = '' aetime = 0 kzaktv = 'X' kzaaeu = '' kzaaet = 0 ).
insert z_test_db from ls_tab.

to create some dummy rows.

When I query that table for MATNR = '4711' it works but as soon as I use that value to query MARA there are no rows returned. I found out this is due to how MARA stores the value internally. If I open MARA in SE16N I can see that the value looks like '000000000000004711' in raw but gets displayed as '4711' to the user.

Solution

  • The domain MATNR uses the conversion routine MATN1 and it works like this:

    Input value from screen to DB

    If you input the field from a screen (eg: MM01) the function CONVERSION_EXIT_MATN1_INPUT is automatically applied before writing in DB converting 4711 into 000000000000004711

    Output value from DB to screen

    When the MATNR field is read from DB and displayed on the screen, the function CONVERSION_EXIT_MATN1_OUTPUT is called automatically, showing the value 4711 instead of the internal format 000000000000004711

    In short: MATNR is shown as 4711 on screen but the DB table is written with 000000000000004711

    In your case

    you're inserting in DB the value 4711 without using the MATN1 routine. For this reason you are not finding the value in MARA: values 4711 in Z_TEST_DB and 000000000000004711 in MARA don't match.

    Try fix the code with:

    data lv_matnr type matnr.

ls_tab = value #( matnr = '4711' vorne = 0010 terminal = 'AAA' etkpgm = 'XXX' drucker = 'MO-ED-01' voretkm = 1 etkcopy = 1 eruser = sy-uname ertime = now aeuser = '' aetime = 0 kzaktv = 'X' kzaaeu = '' kzaaet = 0 ).

call function 'CONVERSION_EXIT_MATN1_INPUT'
  exporting
    input        = ls_tab-matnr
  importing
    output       = ls_tab-matnr
  exceptions
    length_error = 1
    others       = 2.

    This converts the MATNR into 000000000000004711 and you'll find the link with MARA.