topic Re: Help me - Select statement - urgent in Application Development and Automation Discussions

Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 19:45:08 GMT

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.

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 19:49:51 GMT

Use like this in your selection screen

parameters: l_maktx like makt-maktx lower case.

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 19:50:54 GMT

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

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 19:52:31 GMT

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.

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 19:55:17 GMT

Viswanath,

Oops sorry , I misunderstood.

Message was edited by:

Pavan Kothapalli

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 19:58:58 GMT

Satish,

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

Vishnu.

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 20:00:35 GMT

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

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 20:01:10 GMT

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

Re: Help me - Select statement - urgent

naimesh_patel — Fri, 30 Nov 2007 20:03:54 GMT

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

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 20:04:12 GMT

Yes i agree with Max

this is the solution

you need to use MAKTG.

Re: Help me - Select statement - urgent

former_member195698 — Fri, 30 Nov 2007 20:05:42 GMT

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-8ee0a29e0fc7

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 20:06:17 GMT

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

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 20:06:27 GMT

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

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 20:06:45 GMT

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

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 20:08:35 GMT

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.

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 20:10:36 GMT

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.

Re: Help me - Select statement - urgent

Former Member — Fri, 30 Nov 2007 20:23:22 GMT

Hi Max,

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

Regards,

Vishnu.

Re: Help me - Select statement - urgent

Former Member — Mon, 03 Dec 2007 10:32:07 GMT

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