select statement

[Former Member]

The select statement is not fetching data, i have a doubt whether ( ON a_{matnr EQ b}matnr) i am making inner join on mchb and mara should the data be common in both tables so that it gets data, i have commented from for all entries but even its not fetching the data.

IF i_mch1 IS NOT INITIAL.

SELECT a~matnr "Material Number.

a~werks "Plant.

a~lgort "Storage location.

a~charg "Batch number.

a~clabs "Unrestricted.

a~cinsm "Quality Inspection.

a~cspem "Blocked

b~meins "Basic unit of measure.

FROM mchb AS a INNER JOIN mara AS b

ON a_{matnr EQ b}matnr

INTO TABLE i_mchb_mara

FOR ALL ENTRIES IN i_mch1

WHERE a~matnr EQ i_mch1-matnr AND

a~werks EQ p_werks AND

a~charg IN s_charg AND

a~lgort IN (c_0001, c_0002).

IF sy-subrc <> 0.

MESSAGE e000(8i) WITH text-003.

ELSE.

MESSAGE e000(8i) WITH text-004.

ENDIF.

ENDIF.

[Former Member]

Hi,

Is p_werks mandatory in the selection-screen...Otherwise the select statement will look for blank values in those tables..

Thanks,

Naren

[Former Member]

the values are given in selection screen, even i have comented from for all entries the data is not fetching up.

[Former Member]

IF NOT i_mch1 IS INITIAL.
SELECT 
b~matnr "Material Number.
b~meins "Basic unit of measure.
a~werks "Plant.
a~lgort "Storage location.
a~charg "Batch number.
a~clabs "Unrestricted.
a~cinsm "Quality Inspection.
a~cspem "Blocked
FROM mara AS b INNER JOIN mchb AS a
ON b~matnr EQ a~matnr
INTO TABLE i_mchb_mara
FOR ALL ENTRIES IN i_mch1
WHERE b~matnr EQ i_mch1-matnr AND
b~werks EQ p_werks AND
b~charg IN s_charg AND
b~lgort IN ( c_0001, c_0002 ).

IF sy-subrc <> 0.
MESSAGE e000(8i) WITH text-003.
ELSE.
MESSAGE e000(8i) WITH text-004.
ENDIF.
ENDIF.

Ue Mara inner join mchb.

[Former Member]

Couple of things. First change your IF statement to IF i_mch1[] IS NOT INITIAL. This will make it check if the internal table has any records. In your current statement you are only checking if the header work area is initial or not.

Next make sure that your internal table has the material number and the batch numbers in internal format.

[Former Member]

Hi,

As mentioned by Srinivas Adavi..make sure the material number is internal format..

Also check the tables MCHB and MARA if there is any record exists..

Thanks,

Naren

[Former Member]

other than that, nothing else should effect unless there is no data in MCHB for your given selection criteria.

[Former Member]

if mara-matnr and mchb-matnr data is not equal will the select statement gets failed eg: ON a_{matnr EQ b}matnr - from this inner join is happening i have checked in both the tables the values are different is this the problem,

[Former Member]

S obviously it wont fetch any data.

You can fetch data only when both the table key field values are same, using joins.

[Former Member]

But that cannot be true. If you have a batch then the material has to exist in MARA. There may be some materials for which you may not have any batches, but all batch materials should be there in MARA.