Inner join with case sensitive

[Former Member]

Hi All,

I have a requirement to get vendor data from LFA1,LFBK,ADRNR based on vendor name LFA1-name1 .

Name1 field might be CAPS , SMALL, MIXED value .

I have checked ADRC_QUERY program also but no use.

Please help me .

[Former Member]

You can convert to upper case in the internal tables and then can join all the internal tables. isn't?

[Former Member]

Hi Abdul,

you mean , I have to get all the records and convert name1 value into caps for all records in internal table and the join rt ?

[Former Member]

Yes. I think so.

[Former Member]

reading entire table and performing joins wont be performance issue ?..

[rosenberg_eitan]

Hi,

Can you share the reason for this request ?

Also the problem of number of spaces between words .

Perhaps native SQL can help using functions like SOUNDEX,UPPER etc.

I never tried those functions myself.

see here          about native SQL (We have Oracle )

Regards.

[Former Member]

Hi,

My Requirement is to write a select query using inner join for 3 tables as I mentioned above for vendor details based on name1 field.

In XK03 T.code F4 help for vendor search based on name is working for any case(upper/lower/mixed) , i have tried to get the functionality of F4 help , but no use.

my Inner join is as like below :

data : lv_name1 type lfa1-name1.

lv_name1 = %tEest%..

  select  a~lifnr a~name1 a~name2 a~name4 a~adrnr a~ktokk a~stcd1 a~stcd3

    b~sort1 b~sort2 b~street b~city1

    c~bukrs

    d~ekorg

      into corresponding fields of table results

      from lfa1 as a

      inner join adrc as b on b~addrnumber = a~adrnr

      inner join lfb1 as c on c~lifnr = a~lifnr

      inner join lfm1 as d on d~lifnr = a~lifnr

      where name1 like lv_name1.

Here i need to get result for records from LFA1 for all case of name with value test.

[rosenberg_eitan]

Hi,

My friend you title is VERY misleading "Inner join with case sensitive "

when you really want is a selection ignoring case .

And I can see that I am not the only one being misled .

Next time just post your code .

Regards.

[Former Member]

Hi,

Sorry for my mistake my dear, from next time onwards i will be carefull..,

But any how thanks a lot for your suggestion

[rosenberg_eitan]

You live and learn...

“The last thing one discovers in composing a work is what to put first.”

By Blaise Pascal

Regards.

[ThomasZloch]

For search helps on case sensitive fields, SAP uses additional fields that store the information converted to upper case, look at LFA1-MCOD1 in your example. By changing your search string also to upper case, you can perform a non case sensitive search.

Thomas

[Former Member]

Thanks a lot Thomas, It works for me..,

[Former Member]

for sure shot you can try below:

REPORT  ZR_UPPER_CASE.

TYPES: BEGIN OF TY,
          LIFNR TYPE LIFNR,
          NAME1 TYPE NAME1_GP,
        END OF TY.

DATA : WA TYPE TY,
        ITAB1 TYPE STANDARD TABLE OF TY,
        ITAB2 TYPE STANDARD TABLE OF TY.

DATA NAME2 TYPE NAME1_GP.

PARAMETERS: name TYPE NAME1_GP.


        SELECT LIFNR NAME1 FROM LFA1 INTO TABLE ITAB1.

       
        LOOP AT ITAB1 INTO WA.
          TRANSLATE WA-NAME1 TO UPPER CASE.
          MODIFY ITAB1 FROM WA-NAME1.
          CONCATENATE '*' NAME '*' INTO NAME2.
          TRANSLATE NAME2 TO UPPER CASE.
          IF WA-NAME1 CP NAME2.
            APPEND WA TO ITAB2.
          ENDIF.