topic Re: Problems with dynamic where clause in Application Development and Automation Discussions

Problems with dynamic where clause

Former Member — Wed, 14 Feb 2007 20:18:32 GMT

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

Re: Problems with dynamic where clause

Former Member — Wed, 14 Feb 2007 20:23:46 GMT

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

Re: Problems with dynamic where clause

Former Member — Wed, 14 Feb 2007 20:27:31 GMT

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

Re: Problems with dynamic where clause

Former Member — Wed, 14 Feb 2007 20:31:25 GMT

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

Re: Problems with dynamic where clause

Former Member — Wed, 14 Feb 2007 20:35:06 GMT

I have tried that but didn't worked.

Thanks anyway for your time.

Ezequiel

Re: Problems with dynamic where clause

Former Member — Wed, 14 Feb 2007 20:38:43 GMT

I have also tried that but didn' work.

Thanks anyway for your time.

Ezequiel

Re: Problems with dynamic where clause

Former Member — Wed, 14 Feb 2007 20:39:06 GMT

I have also tried that but didn' work.

Thanks anyway for your time.

Ezequiel

Re: Problems with dynamic where clause

RichHeilman — Wed, 14 Feb 2007 20:40:39 GMT

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

Re: Problems with dynamic where clause

Former Member — Wed, 14 Feb 2007 21:00:00 GMT

Thanks Rich,

Your solution has solved my problem.

I had already converted the material into an internal format.

Best Regards.

Ezequiel