topic Re: Help with excluding material types in a select statement. in Application Development and Automation Discussions

Help with excluding material types in a select statement.

Former Member — Wed, 03 Jan 2007 14:25:18 GMT

I have the following code and I need to exclude material types "WERT" and "PROD" (mara-mtart). I have a table cmara (mara) but I can't figure out how to exclude them. I added the code that is in bold (2nd bold group) but I assume where I really need to add this code is what I have in the first bold group. This code checks to ensure that the material has a correct alternative unit of measure. If V_FOUND then it does have an alternative unit of measure. Can somebody tell me if what I did will work (I haven't tested it yet) or better yet if there is a better spot to exclude such material types.

I appreciate any help you can give me.

----

INCLUDE ZXMG0U02 *
Dummy change to transport again 1/3/07 as *

----

CHANGE TO CREATE TRANSPORT 12/11/2006 SR

TABLES: ZBW_REP_INFO, MARM.

DATA: BEGIN OF I_INFO_TAB OCCURS 0.

INCLUDE STRUCTURE ZBW_REP_INFO.

DATA END OF I_INFO_TAB.

DATA: BEGIN OF I_AUOM_TAB OCCURS 0.

INCLUDE STRUCTURE MARM.

DATA END OF I_AUOM_TAB.

DATA: V_FOUND(1) TYPE C.

* I think I should be able to add a condition on the following line to exclude certain material types.

SELECT * FROM ZBW_REP_INFO INTO TABLE I_INFO_TAB.

if zbw_rep_info-matkl = cmara-matkl and
zbw_rep_info-meinh = cmara-meins.

exit.
endif.

CLEAR V_FOUND.

LOOP AT I_INFO_TAB.

IF I_INFO_TAB-MATKL = CMARA-MATKL AND

I_INFO_TAB-MEINH = CMARA-MEINS.

V_FOUND = 'X'.

EXIT.

ENDIF.

* AS - 1/3/07

Added to exclude material types "WERT" and "PROD"

IF I_INFO_TAB-MATKL = CMARA-MATKL AND I_INFO_TAB-MEINH = CMARA-MEINS AND CMARA-MTART = 'WERT'.

V_FOUND = 'X'.

EXIT.

ELSEIF I_INFO_TAB-MATKL = CMARA-MATKL AND I_INFO_TAB-MEINH = CMARA-MEINS AND CMARA-MTART = 'PROD'.

V_FOUND = 'X'.

EXIT.

ENDIF.

End of change on 1/3/07

ENDLOOP.

IF V_FOUND IS INITIAL.

select * from marm into table i_auom_tab
where matnr = cmara-matnr.
clear v_found.
loop at i_auom_tab.
if i_auom_tab-matnr = cmara-matnr and
i_auom_tab-meinh = cmara-meins.
v_found = 'X'.
exit.
endif.
endloop.

SELECT SINGLE * FROM ZBW_REP_INFO WHERE MATKL = CMARA-MATKL.

LOOP AT WMEINH.

IF WMEINH-MEINH = ZBW_REP_INFO-MEINH.

V_FOUND = 'X'.

EXIT.

ENDIF.

ENDLOOP.

IF V_FOUND IS INITIAL.

MESSAGE E001(Z1).

ENDIF.

Re: Help with excluding material types in a select statement.

RichHeilman — Wed, 03 Jan 2007 14:40:06 GMT

SELECT * FROM ZBW_REP_INFO INTO TABLE I_INFO_TAB
               where MTART <> 'PROD' 
                   and MTART <> 'WERT'.

Regards,

Rich Heilman

Re: Help with excluding material types in a select statement.

Former Member — Wed, 03 Jan 2007 14:42:04 GMT

Will this work even though ZBW_REP_INFO does not have MTART as a field? It only has MANDT, MATKL, and MEINH.

Thanks,

Aaron

Re: Help with excluding material types in a select statement.

Former Member — Wed, 03 Jan 2007 14:43:37 GMT

Hi shover,

CHECK out this code:the functionality can be acheived with two selects or with one select.

oneselect : using join

select * INTO TABLE I_INFO_TAB from ZBW_REP_INFO as A join Mara as B

on A_{MATKL = B}-MATKL AND A_{MEINH = B}MEINS where mtart <> 'WERT' and 'PROD'.

two selects:

select * from mara into table cmara where mtart <> 'WERT' and 'PROD'

SELECT * FROM ZBW_REP_INFO INTO TABLE I_INFO_TAB

FOR ALL ENTRIES IN CMARA WHERE

MATKL = CMARA-MATKL AND MEINH = CMARA-MEINS .

plese check out.

sateesh.

Re: Help with excluding material types in a select statement.

RichHeilman — Wed, 03 Jan 2007 14:56:14 GMT

If there is no MTART in your custom table, then you will need to join to MARA, bu where is MATNR?

REgards,

RIch Heilman

Re: Help with excluding material types in a select statement.

Former Member — Wed, 03 Jan 2007 15:16:07 GMT

matnr is not being used. I am seeing this user exit for the first time so I'm not sure why but it was used then commented out.

Re: Help with excluding material types in a select statement.

Former Member — Wed, 03 Jan 2007 15:22:20 GMT

sateesh, thanks for your reply. It seems like what you posted, using the join, will work but I get an error when I do a syntax check on it. I get an error saying that "The work area I_INFO_TAB is not long enough." I was going to try the solution with two selects but I assume that it would be better to use one instead of two. Followed is the code that I have working but I assume that it would be better to exclude them from the select, which is why I would like to better understand your join example.

Thanks,

Aaron

----

INCLUDE ZXMG0U02 *
Dummy change to transport again 1/3/07 as *

----

CHANGE TO CREATE TRANSPORT 12/11/2006 SR

TABLES: ZBW_REP_INFO, MARM.

DATA: BEGIN OF I_INFO_TAB OCCURS 0.

INCLUDE STRUCTURE ZBW_REP_INFO.

DATA END OF I_INFO_TAB.

DATA: BEGIN OF I_AUOM_TAB OCCURS 0.

INCLUDE STRUCTURE MARM.

DATA END OF I_AUOM_TAB.

DATA: V_FOUND(1) TYPE C.

SELECT * FROM ZBW_REP_INFO INTO TABLE I_INFO_TAB.

if zbw_rep_info-matkl = cmara-matkl and
zbw_rep_info-meinh = cmara-meins.

exit.
endif.

CLEAR V_FOUND.

LOOP AT I_INFO_TAB.

IF I_INFO_TAB-MATKL = CMARA-MATKL AND

I_INFO_TAB-MEINH = CMARA-MEINS.

V_FOUND = 'X'.

EXIT.

ENDIF.

* AS - 1/3/07

Added to exclude material types "WERT" and "PROD"

IF CMARA-MTART = 'WERT'.

V_FOUND = 'X'.

EXIT.

ELSEIF CMARA-MTART = 'PROD'.

V_FOUND = 'X'.

EXIT.

ENDIF.

End of change on 1/3/07

ENDLOOP.

IF V_FOUND IS INITIAL.

select * from marm into table i_auom_tab
where matnr = cmara-matnr.
clear v_found.
loop at i_auom_tab.
if i_auom_tab-matnr = cmara-matnr and
i_auom_tab-meinh = cmara-meins.
v_found = 'X'.
exit.
endif.
endloop.

SELECT SINGLE * FROM ZBW_REP_INFO WHERE MATKL = CMARA-MATKL.

LOOP AT WMEINH.

IF WMEINH-MEINH = ZBW_REP_INFO-MEINH.

V_FOUND = 'X'.

EXIT.

ENDIF.

ENDLOOP.

IF V_FOUND IS INITIAL.

MESSAGE E001(Z1).

ENDIF.

Re: Help with excluding material types in a select statement.

Former Member — Wed, 03 Jan 2007 15:33:09 GMT

Shover,

In the select with join i mentioned * . You define an internal table I_INFO_TAB with required fields and give the same fields in sequence in the select statement instead of '*'.

check out ..

sateesh.

Re: Help with excluding material types in a select statement.

Former Member — Wed, 03 Jan 2007 15:42:04 GMT

Like this?

SELECT "mandt" "matkl" "meinh" INTO TABLE I_INFO_TAB FROM ZBW_REP_INFO AS A JOIN MARA AS B

ON A_{mandt = B}mandt and A_{MATKL = B}MATKL AND A_{MEINH = B}MEINS WHERE MTART <> 'WERT' AND 'PROD'.

I'm sorry, I a very beginner ABAPer so this may be elementary.

Thanks,

Aaron

Re: Help with excluding material types in a select statement.

RichHeilman — Wed, 03 Jan 2007 15:43:50 GMT

The problem here is that MATKL is not part of the key and therefore will give inconsistant results.

Regards,

Rich Heilman

Re: Help with excluding material types in a select statement.

Former Member — Wed, 03 Jan 2007 15:49:26 GMT

Oh, that makes sense. As I said I have it working but I am not satisfied with the way I got it to work. However, I'm not sure if using a select statement would work in this case. I guess I will keep it as is unless somebody gives me a better suggestion. Thanks to all for your help!

Re: Help with excluding material types in a select statement.

Former Member — Wed, 03 Jan 2007 15:50:47 GMT

I have it working but I am not satisfied with the way I got it to work. However, I'm not sure if using a select statement would work in this case. I guess I will keep it as is unless somebody gives me a better suggestion. Thanks to all for your help!