help regarding native sql

[Former Member]

Hi friends,

i need to fetch data from kna1, kunnr and name1, as name1 is maintained in case sensitive format (Xyz is different than xYz), i need to fecth name1 in case insensitive format, i know there is a field named MCOD1 which saves the name1 into uppercase, but it is just 25 char's and name1 is 35 characters, so there is definitely data loss beyond 25 chars.

suppose there is a name1 with value 'Microsoft Corporation Private Limited', mcod1 is saving 'MICROSOFT CORPORATION PRI'

my code is

types:begin of ty_kna1,

kunnr type kna1-kunnr,

name1 type kna1-name1,

end of ty_kna1

parameter p_name1 type kna1-name1.

data: it_kna1 type table of ty_kna1.

data string type string.

concatenate '%' p_name '%' into string.

select kunnr name1

from kna1

into table it_kna1

where name1 like string.

now i am giving P_NAME1 Cor

then it will give as Microsoft Corporation Private Limited

but when i give as VATE it wont get that record

my company uses mssql database as backend for SAP, can anybody provide me with a native sql code for this solution where is can directly fetch value as UPPERCASE and not worry about other things.

Thanks & Regards

Shahbaaz Rasheed

[Former Member]

hi shahbaaz,

instead of using native sql why don't you try like below:

Select kunnr name1

from kna1

into wa_kna1. "work area

  TRANSLATE wa_kna1-name1 TO UPPER CASE.

   IF wa_kna1-name1 EQ string.

     append wa_kna1 to it_kna1.

  ENDIF.

ENDSELECT.

[Former Member]

i need to use like operator, when i search for a value like PRIVATE , it should return all the records which contain the word PRIVATE in that customer name, in my case MICROSOFT CORPORATION PRIVATE LIMITED. that PRIVATE im concatenating to string as %PRIVATE% and passing to LIKE operator,

what you said will work if we type the value exactly else it will not return the value.

types:begin of ty_kna1,

kunnr type kna1-kunnr,

name1 type kna1-name1,

end of ty_kna1.

parameter p_name1 type kna1-name1.

data: it_kna1 type STANDARD table of ty_kna1 ,

       wa_kna1 type ty_kna1,

       it_kna11 type STANDARD table of ty_kna1 ,

       wa_kna11 type ty_kna1.

data string type string.

TRANSLATE p_name1 TO UPPER CASE.

concatenate '%' p_name1 '%' into string.

   Select kunnr name1

from kna1

into TABLE it_kna1. "work area

   LOOP AT it_kna1 INTO WA_KNA1.

TRANSLATE wa_kna1-name1 TO UPPER CASE.

APPEND wa_kna1 to it_kna11.

delete  it_kna11 where name1 NE p_name1.

   ENDLOOP.

now if i give p_name1 as MICROSOFT CORPORATION PRIVATE LIMITED then only my second internal table will have the value.

[former_member201275]

It works like this:

   TYPES:BEGIN OF ty_kna1,
kunnr TYPE kna1-kunnr,
name1 TYPE kna1-name1,
END OF ty_kna1.

PARAMETER p_name1 TYPE kna1-name1.

DATA: it_kna1 TYPE TABLE OF ty_kna1 WITH HEADER LINE.
DATA string TYPE string.

TRANSLATE p_name1 TO UPPER CASE.
CONCATENATE '%' p_name1 '%' INTO string.

TRANSLATE p_name1 USING '+_'.
EXEC SQL PERFORMING append_kna1.
  SELECT * FROM kna1 INTO :it_kna1
WHERE upper(name1) LIKE :string ENDEXEC.

LOOP AT it_kna1.
  WRITE:/ it_kna1.
ENDLOOP.

*&-------------------------------------------------
*&      Form  append_kna1
*&-------------------------------------------------
FORM append_kna1.
  APPEND it_kna1.
ENDFORM.                    "append_kna1

[Former Member]

it is going to dump, saying table does not exist in database, is it MSSQL native code or some other database specific code. thanks for the reply

[rosenberg_eitan]

Hi,

please look at

that use package SDB_ADBC .

package SDB_ADBC deals with native SQL access .

regards.

[Former Member]

Hi,

My suggestion is not to go with native SQL.

You can get the 40 char length name from ADRC table. Just fetch the address number from KNA1 table and pass it to ADRC to get the Name1.

Thank you.

Regards,

Gayathri

[Former Member]

my problem is not with name1 field in kna1, im doing an RFC and will be getting the input data only in capital letters, name1 some customer names are maintained in case sensitive format, there is a field MCOD1 in kna1 which store name1 values in UPPERCASE, but it is limited to 25 chars, so if customer name is 30 chars then MCOD1 is showing only 25 chars.

my original requirment is

select kunnr name1

from kna1

into table it_kna1

where name1 like '%PRIVATE%' .

so it will show all the values which contain the word private in customer name

[Former Member]

what is the problem if you change your query to this:

Select kunnr name1

from kna1

into wa_kna1. "work area

  TRANSLATE wa_kna1-name1 TO UPPER CASE.

  TRANSLATE string TO UPPER CASE.

   IF string CS wa_kna1-name1.

     append wa_kna1 to it_kna1.

  ENDIF.

ENDSELECT.

in the end you'll get internal table which has successfully compared the strings irrespective of the case

[Former Member]

i am able to do that functionality using this code.

Thank you everyone for helping me out

types:begin of ty_kna1,

kunnr type kna1-kunnr,

name1 type kna1-name1,

end of ty_kna1.

parameter p_name1 type kna1-name1.

data: it_kna1 type STANDARD table of ty_kna1 ,

      wa_kna1 type ty_kna1,

      it_kna11 type STANDARD table of ty_kna1 ,

      wa_kna11 type ty_kna1.

data: string type string,

      string1 type string.

TRANSLATE p_name1 TO UPPER CASE.

string = p_name1.

  Select kunnr name1

from kna1

into TABLE it_kna1.

  LOOP AT it_kna1 INTO WA_KNA1.

TRANSLATE wa_kna1-name1 TO UPPER CASE.

if wa_kna1-name1 cs string.

APPEND wa_kna1 to it_kna11.

ENDIF.

  ENDLOOP.