Help me - Select statement - urgent

[Former Member]

Hi Friends,

In a report I have to get all the material based on the material descriptions .

I am inputting the material description as a range.

Here my problem is if I give the input 'material description' as 'Mat' , I am getting only the materials with description as 'MAT' ( All caps ) , but not getting any others like materials with description 'mat' ( all lower case ) , 'mAt', etc.

What I found is when I am entering the value 'Mat' in the selection-screen, when I execute , the input is converting into upper case .Hence I am getting only those values ( whose materials with description as 'MAT' ).

Hope u got my problem.Please let me know the solution to get the materials with descriptions with any case ( If we enter the input value 'mat', we shud get the materials with 'MAT', 'Mat', 'mAt' etc').

Thanks in advance,

Vishnu.

[Former Member]

I don't think its possible in select query. If there is some condition by which you can restrict the no of entries, then fetch all the enties from MARA Join MAKT into an internal table.

Then convert the description to UPPER case and then you can search the internal table for your text

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/48cfaa90-0201-0010-cebb-8ee...

[Former Member]

Use like this in your selection screen

parameters: l_maktx like makt-maktx lower case.

[Former Member]

No it won't solve my problem as I will get only the materials with description in lower case.I have materials with lower case as descrption, First letter caps and all letters caps as below:

MATNR MAKTX

000001 Mat1

000002 mat2

000003 mAt3

I have to get all the above materials if I enter mat in my selection-screen.

Regards,

Vishnu.

[Former Member]

Viswanath,

Oops sorry , I misunderstood.

Message was edited by:

Pavan Kothapalli

[Former Member]

Hi

U should use the view M_MAT1W (or table MAKT) in order to select the material by description, here you can use the field MAKTG, in this field the description is written always in UPPER CASE.

This is the field used by material search help std.

Max

[Former Member]

Yes i agree with Max

this is the solution

you need to use MAKTG.

[Former Member]

Hi Vishnu,

If you are fetching information from MAKT table then probably you can use MAKTG field instead of MAKTX. MAKTG will always have material description in upper case. so, you need to convert your selection parameter in upper case and use that for further selection.

Hope this will help you.

Regards.,

Pranav

[Former Member]

Hi Max,

That's a very good solution.So can go ahead and use the filed MAKTG instead of MAKTX.I mean is it an ideal way to do?

Regards,

Vishnu.

[Former Member]

Hi Max,

It is working .Thanks a lot for ur quick help.Thanks everyone for your contribution.

Regards,

Vishnu.

[Former Member]

Hi

It's only an old trick: every search help based on the description uses alway a field where description is translated in upper case.

For example: Search Help for Vendor or Customer doesn't use the field NAME1 but MCOD1.

So the next time you need to search something by description check which field is used in the search help.

Max

[Former Member]

Vishwa,

I think this is a big process. If suppose you have multiple words in the description, How do you manage to get the materials?

Regards,

Satish

[Former Member]

Satish,

Is there any way where we can ignore the case in the description and select all the data?

Vishnu.

[Former Member]

I don't think you will get materials based on the description in a single select query.

[naimesh_patel]

The best possible way, I can think is..

Get all the material description,

translate to Upper case

translate your input to upper case

delete the entries from the interal table

Try with this code:

REPORT  ZTEST_NP.

TABLES: MAKT.

TYPES: BEGIN OF TY_MAKT,
       MATNR TYPE MATNR,
       MAKTX TYPE MAKTX,
       DESC  TYPE MAKTX,
       END   OF TY_MAKT.

DATA: IT_MAKT TYPE STANDARD TABLE OF TY_MAKT,
      WA_MAKT TYPE TY_MAKT.

SELECT-OPTIONS: S_MAKTX FOR MAKT-MAKTX VISIBLE LENGTH 15.

START-OF-SELECTION.
  SELECT MATNR MAKTX
         INTO  TABLE IT_MAKT
         FROM  MAKT.
  LOOP AT IT_MAKT INTO WA_MAKT.
    WA_MAKT-DESC = WA_MAKT-MAKTX.
    TRANSLATE WA_MAKT-DESC TO UPPER CASE.
    MODIFY IT_MAKT FROM WA_MAKT.
    CLEAR  WA_MAKT.
  ENDLOOP.

  LOOP AT S_MAKTX.
    TRANSLATE S_MAKTX TO UPPER CASE.
    s_maktx-option = 'CP'.
    concatenate '*' s_maktx-low  '*' into s_maktx-low.
    if not s_maktx-high is initial.
    concatenate '*' s_maktx-high '*' into s_maktx-high.
    endif.
    MODIFY S_MAKTX.
    CLEAR  S_MAKTX.
  ENDLOOP.

  DELETE IT_MAKT WHERE NOT DESC IN S_MAKTX.

  WRITE: 'Material contains entered pattern'.
  LOOP AT IT_MAKT INTO WA_MAKT.
    WRITE: / WA_MAKT-MATNR,
             WA_MAKT-MAKTX.
  ENDLOOP.

Regards,

Naimesh Patel

[former_member195698]

I don't think its possible in select query. If there is some condition by which you can restrict the no of entries, then fetch all the enties from MARA Join MAKT into an internal table.

Then convert the description to UPPER case and then you can search the internal table for your text

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/48cfaa90-0201-0010-cebb-8ee...

[Former Member]

Your best solution is to use MAKT-MAKTG. this is an all uppercase version of MAKT-MAKTX.

Best of all, theres an index in this table on this field.

Otherwise, it's native SQL.

Rob

Message was edited by:

Rob Burbank

[Former Member]

Vishwanath,

Use select options instead of parameterrs.

TABLES : MAKT.

SELECTION-SCREEN : BEGIN OF BLOCK 1 WITH FRAME TITLE TEXT-001.

SELECT-OPTIONS : P_MAKTX FOR MAKT-MAKTX NO INTERVALS.

*PARAMETERS : P_MAKTX LIKE MAKT-MAKTX.

SELECTION-SCREEN : END OF BLOCK 1.

SELECT SINGLE * FROM MAKT INTO MAKT WHERE MAKTX = P_MAKTX.

WRITE : 'SHREE'.

Hope this helps.

Shreekant

[Former Member]

as per my answer above, replace the '=' by 'IN' in the select query.

SELECT SINGLE * FROM MAKT INTO MAKT WHERE MAKTX [bIN</b> P_MAKTX.