Problems with dynamic where clause

[Former Member]

I have an SQL query to access table MARD. I have 3 fields in the WHERE clause: MATNR, WERKS and LGORT.

The types of these 3 fields are the following:

-MATNR like BAPIMATVP-MATNR

-WERKS like BAPIMATVP-WERKS

-LGORT like BAPICM61V-LGORT

I am using structure TWHERE to build the where clause by concateneting the 3 fields as it follows:

concatenate 'matnr = ' p_matnr into w_where
separated by space.
append w_where to t_where.

The query is constructed as follows:

SELECT werks matnr lgort labst
INTO TABLE ti_mard
FROM mard
WHERE (ti_where).

The problem is that the query is not working and there is data on the table for the values I am looking for.

I have run ST05 to check what the problem is and this is what I got:

SELECT WHERE "MANDT" = '300' AND "MATNR" = ' 110302826'

AND "WERKS" = '1000' AND "LGORT" = '1392'

As you can see, the 0s uo front are being trimmed. I have tried everything (pack, unpack, shift, etc) but no matter what I do, the 0s keep getting trimmed.

If I run the query the "usual" way, like this:

select werks matnr lgort labst
into table ti_mard
from mard
where matnr = matnr and
werks = werks and
lgort = lgort.

This is what I get in ST05:

SELECT WHERE "MANDT" = '300' AND "MATNR" = '000000000110302826'

AND "WERKS" = '1000' AND "LGORT" = '1392'

Any ideas on how to solve this issue?

Thanks in advance!

Best Regards

Ezequiel

[Former Member]

pass p_matnr to a variable which is declared as numc(18) and in the select use that variable then the zeros are not trimmed. Try this and award points if it helps.

Message was edited by:

Vinni

[Former Member]

I have also tried that but didn' work.

Thanks anyway for your time.

Ezequiel

[Former Member]

Hi,

Add zeroes infront of P_MATNR and concatenate..

Use the FM CONVERSION_EXIT_ALPHA_INPUT to put zeroes infront of P_MATNR.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = P_MATNR

IMPORTING

OUTPUT = P_MATNR.

Then use the concatenate..

Thanks

Naren

[Former Member]

I have tried that but didn't worked.

Thanks anyway for your time.

Ezequiel

[Former Member]

Hi,

Declare a variable of type <b>N</b> and length <b>18</b> and use it instead of using p_matnr. i.e.

DATA : v_matnr(18) TYPE n.

v_matnr = p_matnr.

concatenate 'matnr = ' v_matnr into w_where
separated by space.
append w_where to t_where.

Reward points if the answer is helpful.

Regards,

Mukul

[Former Member]

I have also tried that but didn' work.

Thanks anyway for your time.

Ezequiel

[RichHeilman]

One problem is that the material number is not converted to internal format as suggestion above. so make those changes first. Another is that there may be a problem with the way that you build the WHERE clause. You need to wrap the P_MATNR value with quotes in your WHERE clause.

data: matnr_String type string.
data: p_matnr type mara-matnr value '000000000012345678'.

concatenate '''' p_matnr '''' into matnr_string.

concatenate 'matnr = ' matnr_string into w_where
separated by space.
append w_where to t_where.

Regards,

Rich Heilman

[Former Member]

Thanks Rich,

Your solution has solved my problem.

I had already converted the material into an internal format.

Best Regards.

Ezequiel