topic Re: having a hard time modifying this select statement... in Application Development and Automation Discussions

having a hard time modifying this select statement...

aris_hidalgo — Tue, 28 Feb 2006 13:18:36 GMT

Hello experts,

As being new to ABAP, I was tasked to modify a report. The problem is that when the user enters a material number in the selection screen the report says it didn't found any records. But when they try to enter values on other selection screen select options or parameter the report just works fine. So I debugged the report and I think that it is because of the select statement below. I think it doesn't get any material numbers from table cooi so I was tasked to replace cooi with eban table. So instead of cooi-matnr in so_matnr what I would like to do is eban-matnr in so_matnr. But my problem is how can I link all the tables? if I do a seperate select what would I get and where would I compare it to? hope you could help me guys here. Thanks and take care!

**here is the selection screen**

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETERS: pa_bukrs LIKE t001-bukrs OBLIGATORY DEFAULT 'GLOB'.

SELECT-OPTIONS: so_psphi FOR prps-psphi,

so_ebeln FOR ekko-ebeln,

so_lifnr FOR ekko-lifnr,

so_matnr FOR ekpo-matnr,

so_augdt FOR bseg-augdt.

*PARAMETERS: pa_augdt LIKE bseg-augdt.

SELECTION-SCREEN END OF BLOCK b1.

**here is the code that i think must be modified**

FORM get_data.

IF NOT so_ebeln[] IS INITIAL.

PERFORM get_header.

ENDIF.

SELECT a_{psphi b}refbn b~lifnr

FROM prps AS a INNER JOIN cooi AS b

ON a_{objnr = b}objnr

INTO CORRESPONDING FIELDS OF it_hdr

WHERE a~psphi IN so_psphi

AND a~pbukr = pa_bukrs

AND a~erdat LE v_budat

AND a~erdat IN so_augdt

AND b~refbn IN so_ebeln

AND b~lifnr IN so_lifnr

AND b~matnr IN so_matnr

AND b~rfart = 'P'.

SELECT SINGLE name1 FROM lfa1 INTO it_hdr-name1

WHERE lifnr = it_hdr-lifnr.

IF NOT so_augdt[] IS INITIAL.

CLEAR ekko.

SELECT SINGLE * FROM ekko

WHERE ebeln = it_hdr-refbn

AND bedat IN so_augdt.

IF sy-subrc EQ 0.

APPEND it_hdr.

CLEAR it_hdr.

ENDIF.

ELSE.

APPEND it_hdr.

CLEAR it_hdr.

ENDIF.

ENDSELECT.

LOOP AT t_ekpo.

it_hdr-refbn = t_ekpo-ebeln.

it_hdr-lifnr = t_ekpo-lifnr.

it_hdr-name1 = t_ekpo-name1.

APPEND it_hdr. CLEAR it_hdr.

ENDLOOP.

IF it_hdr[] IS INITIAL.

EXIT.

ENDIF.

Re: having a hard time modifying this select statement...

Former Member — Tue, 28 Feb 2006 13:25:20 GMT

use conversion routine for matnr before select so that u pass the correct format

Re: having a hard time modifying this select statement...

aris_hidalgo — Tue, 28 Feb 2006 13:26:34 GMT

Sorry but what do you mean by conversion?thanks!

Re: having a hard time modifying this select statement...

Former Member — Tue, 28 Feb 2006 13:31:18 GMT

pass ur matnr to the fn module to get the formatted matnr to be passed to select.

CALL FUNCTION 'CONVERSION_EXIT_MATN1_INPUT'

EXPORTING

INPUT = var_matnr

IMPORTING

OUTPUT = var_matnr

EXCEPTIONS
LENGTH_ERROR = 1
OTHERS = 2

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

use this matnr in select statement

Re: having a hard time modifying this select statement...

Former Member — Tue, 28 Feb 2006 13:33:02 GMT

u need to pass all the matnrs (since it is a range ) to the funaction module, may be using loop.

Re: having a hard time modifying this select statement...

vinod_gunaware2 — Tue, 28 Feb 2006 13:37:42 GMT

CONVERSION_EXIT_ALPHA_INPUT converts any number into a string fill with zeroes, with the number at the extreme right

use above function. for matnr

regard

vinod

Re: having a hard time modifying this select statement...

aris_hidalgo — Tue, 28 Feb 2006 13:46:20 GMT

The end user was typing 300275 in the so_matnr in the selection screen. So I checked the the material number and it does exists. So what you mean guys is that even though the user types the correct material number it ends up as being non-existent because it lacks digits? for example 00000300275. Is it because of this?

Re: having a hard time modifying this select statement...

Former Member — Tue, 28 Feb 2006 14:15:39 GMT

yes ?

Re: having a hard time modifying this select statement...

RichHeilman — Tue, 28 Feb 2006 14:20:29 GMT

Correct me if I'm missing something here, but since you are defining the select option like this.



so_matnr <b>FOR ekpo-matnr,</b>

The conversion routine is fired automatically. There is no need to do the conversion in your code.

Regards,

Rich Heilman

Re: having a hard time modifying this select statement...

Former Member — Tue, 28 Feb 2006 14:53:56 GMT

replace ur select hope this will meet ur requirement

SELECT a_{psphi b}refbn b~lifnr

FROM prps AS a INNER JOIN cooi AS b

ON a_{objnr = b}objnr

INNER JOIN eban AS c

ON b_{matnr = c}matnr

INTO CORRESPONDING FIELDS OF it_hdr

WHERE a~psphi IN so_psphi

AND a~pbukr = pa_bukrs

AND a~erdat LE v_budat

AND a~erdat IN so_augdt

AND b~refbn IN so_ebeln

AND b~lifnr IN so_lifnr

AND c~matnr IN so_matnr

AND b~rfart = 'P'.