topic Re: help in select statement... in Application Development and Automation Discussions

help in select statement...

aris_hidalgo — Thu, 02 Mar 2006 01:05:24 GMT

Hello experts,

I am currently having a hard time modifying a select statement. I was tasked to change the select statement because when a user types in a material number in the selection screen and execute it the report says no records found. But when I checked in cooi-matnr the material number exists. So my boss said that instead of using cooi I must eban. But my problem is how can I join eban with the existing select statement and also I am thinking if I would seperate this select statement. Also, in the existing select statement, instead of comparing cooi-refbn in so_ebeln and cooi-matnr in so_matnr I would use eban instead. Is this right? Help would really be appreciated. Thanks guys 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 select statement**

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'.

Re: help in select statement...

former_member186741 — Thu, 02 Mar 2006 01:14:00 GMT

To make it more readable I would recode your sql like this:

SELECT prps_{psphi cooi}refbn cooi~lifnr

FROM prps INNER JOIN cooi

ON prps_{objnr = cooi}objnr

INTO CORRESPONDING FIELDS OF it_hdr

WHERE prps~psphi IN so_psphi

AND prps~pbukr = pa_bukrs

AND prps~erdat LE v_budat

AND prps~erdat in so_augdt

AND cooi~refbn IN so_ebeln

AND cooi~lifnr IN so_lifnr

AND cooi~matnr IN so_matnr

AND cooi~rfart = 'P'.

...........................

1. you say the record is in cooi, what objnr does it have and does that match the objnr in prps? If it doesn't match that's why no records are found.

2. check all the other criteria too, eg psphi, bukrs etc. If your select is not finding a record there must be a reason for that.

Re: help in select statement...

hymavathi_oruganti — Thu, 02 Mar 2006 02:44:14 GMT

1.If there is matnr found in COOI, then y the need to shift to eban?

2.check whether OBJNR matches in both the tables.

3. check