topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Thu, 08 Feb 2007 06:58:00 GMT

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.

Re: select statement

Former Member — Thu, 08 Feb 2007 06:59:34 GMT

Hi,

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

Thanks,

Naren

Re: select statement

Former Member — Thu, 08 Feb 2007 07:00:51 GMT

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.

Re: select statement

Former Member — Thu, 08 Feb 2007 07:03:31 GMT

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.

Re: select statement

Former Member — Thu, 08 Feb 2007 07:06:36 GMT

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

Re: select statement

Former Member — Thu, 08 Feb 2007 07:08:47 GMT

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

Re: select statement

Former Member — Thu, 08 Feb 2007 07:21:14 GMT

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

Re: select statement

Former Member — Thu, 08 Feb 2007 07:25:10 GMT

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,

Re: select statement

Former Member — Thu, 08 Feb 2007 07:46:39 GMT

S obviously it wont fetch any data.

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

Re: select statement

Former Member — Thu, 08 Feb 2007 21:20:41 GMT

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.