topic Select statement in Application Development and Automation Discussions

Select statement

Former Member — Wed, 21 Feb 2007 21:41:27 GMT

Hi,

I am working the user exit which validates the entered serial number.

CHECKING THE CALLING TRANSACTION AND USERS

IF SY-TCODE = 'MB1C'.

EXIT.

ENDIF.

TO CHECK WHETHER ENTERED SERIAL NUMBER CONTAINS ANY ALPHABETS

IF SERNR CA SY-ABCDE.

MESSAGE E398(00) WITH 'ALPHA CHARACTERS NOT PERMITTED'.

ENDIF.

DATA: I_EQUI LIKE EQUI,

L_SERNR LIKE EQUI-SERNR,

L_LENGTH TYPE I.

L_SERNR = SERNR.

DELETING THE LEADING ZEROES IN THE SERIAL NUMBER

SHIFT L_SERNR LEFT DELETING LEADING '0'.

CHECKING THE LENGTH OF ENTERED SERIAL NUMBER

L_LENGTH = STRLEN( L_SERNR ).

L_LENGTH GT 9 OR L_LENGTH LT 9.

MESSAGE E398(00) WITH 'INVALID LENGTH'.

EXIT.

ENDIF.

TO CHECK THERE ARE NO DUPLICATE SERIAL EXISTS

IF SY-TCODE = 'MB31'.

SELECT SINGLE * FROM EQUI INTO I_EQUI

WHERE EQTYP = 'S'

AND MATNR = MATNR

AND SERNR = SERNR.

IF SY-SUBRC EQ 0.

MESSAGE E398(00) WITH 'SERIAL NUMBER ALREADY EXISTS'.

ELSE.

CHECKING THE FORMAT OF THE ENTERED SERIAL NUMBER COMPLIES WITH TELEFLEX STANDARDS

IF L_SERNR+0(1) = '6'.

DATA : L_MONTH(2),

: L_YEAR(2).

CLEAR: L_MONTH,

L_YEAR.

L_YEAR = SY-DATUM+2(2) - 1.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = L_YEAR

IMPORTING

OUTPUT = L_YEAR.

IF L_SERNR1(2) = SY-DATUM2(2) OR L_SERNR+1(2) = L_YEAR.

IF L_SERNR1(2) = L_YEAR AND L_SERNR3(2) NE '12'.

MESSAGE E398(00) WITH 'INVALID YEAR/MONTH'.

ENDIF.

IF ( ( SY-DATUM4(2) = '01' ) AND ( L_SERNR1(2) = SY-DATUM+2(2) ) ).

L_MONTH = '12'.

ELSE.

L_MONTH = SY-DATUM+4(2) - 1.

ENDIF.

SY-DATUM4(2) = '01' AND L_SERNR1(2) = L_YEAR.

L_MONTH = '12'.

ELSE.

L_MONTH = SY-DATUM+4(2) - 1.

ENDIF.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = L_MONTH

IMPORTING

OUTPUT = L_MONTH.

IF L_SERNR3(2) = SY-DATUM4(2) OR L_SERNR+3(2) = L_MONTH.

IF L_SERNR5(4) GT '0000' AND L_SERNR5(4) LT '9999'.

ELSE.

MESSAGE E398(00) WITH 'PLEASE ENTER RANGE WITHIN 0000 AND 9999'.

ENDIF.

ELSE.

MESSAGE E398(00) WITH 'INVALID YEAR/MONTH'.

ENDIF.

ELSE.

MESSAGE E398(00) WITH 'INVALID YEAR/MONTH'.

ENDIF.

ELSE.

MESSAGE E398(00) WITH 'INVALID MANUFACTURING SITE'.

ENDIF.

ELSE.

TO CHECK WHETHER USER ENTERS EXISTING SERIAL NUMBER.

SELECT SINGLE * FROM EQUI INTO I_EQUI

WHERE EQTYP = 'S'

AND MATNR = MATNR

AND SERNR = SERNR.

IF SY-SUBRC NE 0.

MESSAGE E398(00) WITH 'PLEASE ENTER A VALID SERIAL NUMBER'.

ENDIF.

Now I was asked to make changes to the select statements using SELECT COUNT(*).

Can someone please help in modifying the code.Thank you.

Re: Select statement

Former Member — Wed, 21 Feb 2007 21:46:45 GMT

It will be less efficient. Why do yo need to change it?

Rob

Re: Select statement

Former Member — Wed, 21 Feb 2007 21:51:06 GMT

Hi Rob,

Can you explain me in detail why is it less efficient. I was to change it on the performance issues...Waiting for your reply.

Re: Select statement

Former Member — Wed, 21 Feb 2007 21:52:56 GMT

Try this:

SELECT COUNT(*) FROM EQUI

WHERE EQTYP = 'S'

AND MATNR = MATNR

AND SERNR = SERNR.

IF SY-SUBRC EQ 0.

MESSAGE E398(00) WITH 'SERIAL NUMBER ALREADY EXISTS'.

ELSE.

ENDIF.

Thanks,

Santosh

Re: Select statement

Former Member — Wed, 21 Feb 2007 21:53:35 GMT

Using Count(*) will be less efficient... i believe u wanna make it for code efficiency as u are selecting one row and u r just checking the sy-subrc value ... as far as i can understand u r not making use of ITAB i_equi anywhere in ur code... so u can use select single mandt from DBtable into variable_name

WHERE EQTYP = 'S'

AND MATNR = MATNR

AND SERNR = SERNR.

this will ensure if there is an entry with the above seleciton criteria in the table or not.

hope it will help u

bbye tac care

Ashwani

Re: Select statement

Former Member — Wed, 21 Feb 2007 21:58:43 GMT

Aggregate functions are in general less efficient that doing the work yourself. It might be faster to SELECT ... UP TO 1 ROWS but not much faster.

Rob