topic Re: Inner join with case sensitive in Application Development and Automation Discussions

Inner join with case sensitive

Former Member — Sun, 12 Oct 2014 13:24:14 GMT

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 .

Re: Inner join with case sensitive

Former Member — Sun, 12 Oct 2014 13:55:44 GMT

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

Re: Inner join with case sensitive

Former Member — Sun, 12 Oct 2014 14:00:15 GMT

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 ?

Re: Inner join with case sensitive

Former Member — Sun, 12 Oct 2014 14:03:36 GMT

Yes. I think so.

Re: Inner join with case sensitive

Former Member — Sun, 12 Oct 2014 14:15:23 GMT

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

Re: Inner join with case sensitive

rosenberg_eitan — Sun, 12 Oct 2014 16:11:17 GMT

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.

Re: Inner join with case sensitive

ThomasZloch — Sun, 12 Oct 2014 17:05:27 GMT

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

Re: Inner join with case sensitive

Former Member — Sun, 12 Oct 2014 17:13:38 GMT

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.

Re: Inner join with case sensitive

Former Member — Sun, 12 Oct 2014 17:34:06 GMT

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

Re: Inner join with case sensitive

rosenberg_eitan — Sun, 12 Oct 2014 17:35:24 GMT

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.

Re: Inner join with case sensitive

Former Member — Sun, 12 Oct 2014 17:36:01 GMT

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.

Re: Inner join with case sensitive

Former Member — Sun, 12 Oct 2014 17:57:11 GMT

Hi,

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

But any how thanks a lot for your suggestion

Re: Inner join with case sensitive

rosenberg_eitan — Sun, 12 Oct 2014 18:40:54 GMT

You live and learn...

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

By Blaise Pascal

Regards.