Problem while selecting material with *

[Former Member]

Hi,

We have made a RFC to search the material now my requiremen is if there is any * in the matnr filed system should select all the material like matnr the code is below

*************************

FUNCTION ZMS_MATERIAL_SEARCH.

*"----

---

""Local Interface:

*" IMPORTING

*" VALUE(MATNR) TYPE MATNR OPTIONAL

*" VALUE(MAKTG) TYPE MAKTG OPTIONAL

*" VALUE(MATKL) TYPE MATKL OPTIONAL

*" VALUE(WERKS) TYPE WERKS_D OPTIONAL

*" VALUE(MTART) TYPE MTART OPTIONAL

*" VALUE(DISMM) TYPE DISMM OPTIONAL

*" VALUE(MVGR2) TYPE MVGR2 OPTIONAL

*" TABLES

*" FINAL STRUCTURE ZMATERIAL_SEARCH OPTIONAL

*"----

---

IF MATNR IS INITIAL.

MATNR = '*'.

ENDIF.

IF MAKTG IS INITIAL.

MAKTG = '*'.

ENDIF.

IF MATKL IS INITIAL.

MATKL = '*'.

ENDIF.

IF MTART IS INITIAL.

MTART = '*'.

ENDIF.

IF DISMM IS INITIAL.

DISMM = '*'.

ENDIF.

IF MVGR2 IS INITIAL.

MVGR2 = '*'.

ENDIF.

IF MATNR CA '*'.

REPLACE ALL OCCURRENCES OF '*' IN MATNR WITH '%' .

TRANSLATE MATNR TO UPPER CASE.

ENDIF.

IF MAKTG CA '*'.

REPLACE ALL OCCURRENCES OF '*' IN MAKTG WITH '%' .

TRANSLATE MAKTG TO UPPER CASE.

ENDIF.

IF MATKL CA '*'.

REPLACE ALL OCCURRENCES OF '*' IN MATKL WITH '%' .

TRANSLATE MATKL TO UPPER CASE.

ENDIF.

IF MTART CA '*'.

REPLACE ALL OCCURRENCES OF '*' IN MTART WITH '%' .

TRANSLATE MTART TO UPPER CASE.

ENDIF.

IF DISMM CA '*'.

REPLACE ALL OCCURRENCES OF '*' IN DISMM WITH '%' .

TRANSLATE DISMM TO UPPER CASE.

ENDIF.

IF MVGR2 CA '*'.

REPLACE ALL OCCURRENCES OF '*' IN MVGR2 WITH '%' .

TRANSLATE MVGR2 TO UPPER CASE.

ENDIF.

SELECT A_{MATNR A}MTART A_{MATKL B}MAKTG C_{DISMM C}WERKS D~MVGR2

FROM MARA AS A INNER JOIN MAKT AS B ON A_{MATNR = B}MATNR

INNER JOIN MARC AS C ON A_{MATNR = C}MATNR

INNER JOIN MVKE AS D ON A_{MATNR = D}MATNR INTO CORRESPONDING FIELDS OF TABLE FINAL

WHERE A~MATNR LIKE MATNR

AND A~MATKL LIKE MATKL

AND A~MTART LIKE MTART

AND B~MAKTG LIKE MAKTG

AND C~WERKS EQ WERKS

AND C~DISMM LIKE DISMM

AND D~VKORG EQ WERKS

AND D~MVGR2 LIKE MVGR2.

ENDFUNCTION.

*************

now my problem is I have Two material 4110 and 4110V1 when i search material with 4110* in output it is giving only 4110V1 as material, my requirement is in output it should give 4110 and 4110V1 for the input as 4110*

so what modification i need in my program

regards,

zafar

[SuhaSaha]

You have to populate ranges and pass the material number. I think this should be best way of handling situations like this:

DATA: R_MATNR TYPE RANGE OF MATNR,

WA_MATNR LIKE LINE OF R_MATNR.

WA_MATNR-SIGN = 'I'.
WA_MATNR-OPTION = 'CP'.
WA_MATNR-LOW = '4110*'.
APPEND WA_MATNR TO R_MATNR.

Use R_MATNR in your SELECT statement.

BR,

Suhas

[Former Member]

Hi Suhas,

Hi I have try with given details but stil I am getting only one entry as i mention above.

regards,

zafar

[bbalci]

Hi Zafar,

CP operator is the right method for this,

I think your join statement is restricting some records,

Can you please try to write a select statement for only table MARA first,

than you 'll see it will read both records

than add your joins step by step and see which join is restricting your record.

I hope it helps.

Bulent.

Edited by: Bulent Balci on Jul 24, 2010 3:51 PM

[Former Member]

Apart from Bulent's good answer there's one more obvious thing you should do (and I'm assuming that the odd-looking selection D~VKORG EQ WERKS actually makes sense due to the functional usage of those organizational units in your system)...

I'd say the best candidate for checking is how your material numbers are stored. Based on your results I'd bet that your system is configured to store numeric material numbers with leading zeros (you can check that easily via customizing or via SE16 with switching off all conversion exits). In that case you're numeric only material number 4110 would actually be stored on the database as 000000000000004110 and your selection with 4110% is obviously not matching that material number.

So if that's the case in your system, then you're in for some ugly searching, whenever a numeric material number pattern is specified. If your numeric material numbers have different lengths (and looking at the 4 digits you're giving I'm assuming this), then you'd basically need to create quite a few patterns (probably best generated/stored in a select-option), where you'd prefix all possible numbers of zeros in front of your pattern. So in your case you'd need the following patterns (substitute the % with a * if you use select-options):

4110%
04110%
004110%
0004110%
\[..\]
00000000000004110%
000000000000004110

The approach to use a more generic pattern like %4110% and then use some additional ABAP coding to remove the false matches is most likely more inefficient. I.e. even if your result set would be small, the database selection would still require a full index (or table) scan to figure out the matching material numbers.

If all of that sounds unfamiliar to you, I strongly recommend reading up on [conversion exits|http://help.sap.com/abapdocu_70/en/ABENCONVERSION_EXITS.htm], which is ABAP 101...

Cheers, harald

p.s.: I leave it as an exercise to the reader if my answer contains any wrong material number (pattern), where I accidentally didn't count the number of zeros properly. I'm too lazy to check that.

[Former Member]

OK

[Former Member]

OK???

Is that all you have to say to all those people who has taken the time out of their busy schedules to help you with a detailed explanation?

pk