topic Re: case sensitivity select statement without using native sql in Application Development and Automation Discussions

case sensitivity select statement without using native sql

Former Member — Sun, 18 May 2014 22:59:38 GMT

please help me ,

i have a selection screen as select-options : s_vbeln, s_kunnr(mandt),s_matnr and

p_name as a parameter (type name1) which is case sensitive in sap abap database and i want to get

output from these fields in an alv.[vbeln,kunnr,name1,audat,auart,matnr,kwmeng,netwr]

How to do it? Can any one give me solution please.[i have to handle case sensitivity plz give me solution]

Re: case sensitivity select statement without using native sql

Former Member — Mon, 19 May 2014 06:03:35 GMT

hi sharan,

change p_name to upper or lower case to avoid problems in case sensitive.

TRANSLATE p_name TO UPPER CASE.

TRANSLATE p_name TO LOWERCASE.

Re: case sensitivity select statement without using native sql

RaymondGiuseppi — Mon, 19 May 2014 06:12:08 GMT

use KNA1-MCOD1 and not KNA1-NAME1 for F4 selection.

(Check also 1795444 - Customer / Vendor does not appear in search results)

Regards,

Raymond

Re: case sensitivity select statement without using native sql

jayanthi_jayaraman — Mon, 19 May 2014 07:06:44 GMT

Hi,

ANother way is you can select everything from database and then select from internal table based on 'CS'.

The below statement will identify both upper and lower case.

data itab type STANDARD TABLE OF pa0002.

data wa type pa0002.

select * from pa0002 into table itab.

Loop at itab into wa.

if wa-nachn cs 'xyz'.

write 'success'.

endif.

endloop.

Re: case sensitivity select statement without using native sql

Former Member — Mon, 19 May 2014 07:13:48 GMT

Hi Teja,

As in DB all CHAR type values get stored in Upper case only. Its good practice to convert user input from selection screen into Uppercase.

Use TRANSLATE P_Name TO UPPER CASE. before passing the values in select query.

Regards,

Mohit

Re: case sensitivity select statement without using native sql

Former Member — Mon, 19 May 2014 07:17:10 GMT

Kindly check the performance issue.

Re: case sensitivity select statement without using native sql

jayanthi_jayaraman — Mon, 19 May 2014 07:20:40 GMT

Hi Mohit,

The solution will be applicable only if the selection for the field alone is not the key field in filtering records.

Re: case sensitivity select statement without using native sql

Former Member — Mon, 19 May 2014 08:03:47 GMT

this is my code : but its not working as i expected plz tell me where did i went wrong...

type-pools slis.

tables : vbak , vbap.

select-options : s_vbeln for vbak-vbeln,

s_kunnr for vbak-kunnr,

s_matnr for vbap-matnr.

parameters p_name1 type NAME1_GP.

ranges r_name for kna1-name1.

types : begin of ty_vbak,

vbeln type vbeln_va,

audat type audat,

auart type auart,

kunnr type kunnr,

end of ty_vbak,

begin of ty_vbap,

vbeln type vbeln_va,

matnr type matnr,

netwr type netwr_ap,

kwmeng type kwmeng,

end of ty_vbap,

begin of ty_kna1,

kunnr type kunnr,

name1 type name1,

end of ty_kna1,

begin of ty_final,

vbeln type vbeln_va,

audat type audat,

auart type auart,

matnr type matnr,

netwr type netwr_ap,

kwmeng type kwmeng,

kunnr type kunnr,

name1 type name1,

end of ty_final.

data : t_vbak type standard table of ty_vbak initial size 1,

t_vbap type standard table of ty_vbap initial size 1,

t_kna1 type standard table of ty_kna1 initial size 1.

data : t_final type standard table of ty_final initial size 1,

w_final type ty_final,

w_vbap type ty_vbap,

w_kna1 type ty_kna1,

w_vbak type ty_vbak.

data : t_fcat type slis_t_fieldcat_alv,

w_fcat type slis_fieldcat_alv.

DATA T_FINAL2 TYPE STANDARD TABLE OF TY_FINAL INITIAL SIZE 1.

data v_name1 type name1.

if p_name1 is not initial.

r_name-sign = 'I'.

r_name-option = 'EQ'.

if p_name1 ca '*'.

r_name-option = 'CP'.

endif.

r_name-low = p_name1.

append r_name.

endif.

start-of-selection.

select vbeln audat auart kunnr from vbak

into table t_vbak

where vbeln in s_vbeln and

kunnr in s_kunnr.

if sy-subrc <> 0.

message 'plz enter a valid entry' type 'I'.

leave list-processing.

endif.

if t_vbak[] is not initial.

select vbeln matnr netwr kwmeng from vbap

into table t_vbap

for all entries in t_vbak

where vbeln = t_vbak-vbeln and

matnr in s_matnr.

if sy-subrc <> 0.

message 'no rec found' type 'I'.

leave list-processing.

endif.

select kunnr name1 from kna1

into table t_kna1

for all entries in t_vbak

where kunnr = t_vbak-kunnr.

if sy-subrc = 0.

sort t_kna1 by kunnr.

endif.

end-of-selection.

loop at t_vbap into w_vbap.

read table t_vbak into w_vbak with key vbeln = w_vbap-vbeln binary search.

read table t_kna1 into w_kna1 with key kunnr = w_vbak-kunnr binary search.

w_final-vbeln = w_vbak-vbeln.

w_final-audat = w_vbak-audat.

w_final-auart = w_vbak-auart.

w_final-matnr = w_vbap-matnr.

w_final-netwr = w_vbap-netwr.

w_final-kwmeng = w_vbap-kwmeng.

w_final-kunnr = w_kna1-kunnr.

w_final-name1 = w_kna1-name1.

append w_final to t_final.

endloop.

delete t_final where name1 not in r_name.

perform sub_fill_fcat using 'VBELN' 'SALE ORDER NUM'.

perform sub_fill_fcat using 'KUNNR' 'CUSTOMER NUMBER'.

perform sub_fill_fcat using 'NAME1' 'NAME OF A CUST'.

perform sub_fill_fcat using 'AUDAT' 'SALE ORDER DATE'.

perform sub_fill_fcat using 'AUART' 'SALE DOC TYPE'.

perform sub_fill_fcat using 'MATNR' 'MATERIAL NUM'.

perform sub_fill_fcat using 'KWMENG' 'QUANTITY'.

perform sub_fill_fcat using 'NETWR' 'PRICE'.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

IT_FIELDCAT = T_FCAT

TABLES

T_OUTTAB = T_FINAL

EXCEPTIONS

PROGRAM_ERROR = 1

OTHERS = 2

IF SY-SUBRC <> 0.

* Implement suitable error handling here

ENDIF.

*&---------------------------------------------------------------------*

*& Form SUB_FILL_FCAT

*&---------------------------------------------------------------------*

* text

*----------------------------------------------------------------------*

* -->P_0389 text

* -->P_0390 text

*----------------------------------------------------------------------*

FORM SUB_FILL_FCAT USING P_FNAM P_TEXT.

clear w_fcat.

w_fcat-fieldname = p_fnam.

w_fcat-seltext_m = p_text.

append w_fcat to t_fcat.

ENDFORM.

Re: case sensitivity select statement without using native sql

jayanthi_jayaraman — Mon, 19 May 2014 08:30:46 GMT

Hi,

You just need to mention delete t_final where name1 ns p_name1.

Re: case sensitivity select statement without using native sql

Former Member — Mon, 19 May 2014 08:50:42 GMT

hi teja,

try like below code ,

tables : vbak , vbap,kna1.

select-options : s_vbeln for vbak-vbeln,

s_kunnr for vbak-kunnr,

s_matnr for vbap-matnr,

s_name1 for kna1-name1.

select a~vbeln a~audat a~auart a~kunnr

b~matnr b~netwr b~kwmeng

c~name1

from vbak AS a

INNER JOIN vbap AS b ON a~vbeln = b~vbeln

INNER JOIN kna1 AS c ON a~kunnr = c~kunnr

INTO CORRESPONDING FIELDS OF TABLE it_final

WHERE a~vbeln IN s_vbeln AND a~kunnr IN s_kunnr AND b~matnr IN s_matnr AND c~name1 IN s_name1.

Re: case sensitivity select statement without using native sql

Former Member — Mon, 19 May 2014 08:57:17 GMT

Hi,

please insert two green code lines into your code:

if p_name1 is not initial.
r_name-sign = 'I'.
r_name-option = 'EQ'.

if p_name1 ca '*'.
r_name-option = 'CP'.
endif.

r_name-low = p_name1.
translate r_name-low to upper case.
append r_name.
endif.

...
w_final-name1 = w_kna1-name1.
translate w_final-name1 to upper case.
append w_final to t_final.

This will give you the right data selection. If you neen the original value of name1, then you need an additional fiield in table w_final:

w_final-name1 = w_kna1-name1.

w_final-name1uc = w_kna1-name1.

translate w_final-name1uc to upper case.

append w_final to t_final.

and you have to change the DELETE from

delete t_final where name1 not in r_name.

delete t_final where name1uc not in r_name.

Regards,

Klaus

Re: case sensitivity select statement without using native sql

Former Member — Mon, 19 May 2014 09:25:39 GMT

hi teja,

If your customer name is like say 'Test' then your uppercase and lowercase will not work .

Re: case sensitivity select statement without using native sql

Former Member — Mon, 19 May 2014 09:32:08 GMT

Hi sharan,

there are already threads about this subject but since you've gotten so many different answers I'll clarify a bit...

The only problematic field you have is kna1-name1, the case sensitive one. There is one kna1-mcod1 that might have that value in uppercase, not always because of different field sizes, so your search could use mcod1 instead (this is what standard does).

Check first with business to know if you can do it like this (add other fields):

select-options: s_name1 for kna1-mcod1.

and:

select * from kna1 into table it_customers where mcod1 in s_name1.

If you can't you'll have to swipe all records and translate everything to uppercase. Like:

select-options: s_name1 for something char35 in uppercase only.

and then:

select * from kna1.

translate kna1-name1 to uppercase.

if kna1-name1 in s_name1.

append kna1 to it_customers.

endif.

endselect.

The first option is always the preferred one.

regards,

Edgar

Oh, I used select-options instead of parameters because it's makes more sense, convert your parameter to a range if required.