topic Re: Select Statement in Application Development and Automation Discussions

Select Statement

Former Member — Mon, 02 Apr 2007 14:09:25 GMT

Hello Friends,

I am having a problem in the select statement and I don’t see any error in the statement.

IN the below code the select statement where I am getting data into t_plaf works fine and pulls the data. But the second statement for internal table t_mara doesn’t pull data even though data exist in the data base MARA table. IT has values for the combination of material in t_Plaf and the normt in the select option. I am confused why this is happening. IS it something with the declaration or Inner join or what?

Any Suggestions will be highly appericiated.

Thanks, MAdhu.

DATA :BEGIN OF t_plaf OCCURS 0,

normt LIKE mara-normt,

matnr LIKE plaf-matnr,

pwwrk LIKE plaf-pwwrk,

paart LIKE plaf-paart,

verid LIKE plaf-verid,

END OF t_plaf.

DATA :BEGIN OF t_mara OCCURS 0,

matnr LIKE mara-matnr,

normt LIKE mara-normt,

END OF t_mara.

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

SELECT-OPTIONS : s_werks FOR zcast-werks OBLIGATORY,

s_planpl FOR mara-normt,

s_psttr FOR zcast-zefdat OBLIGATORY,

s_matnr FOR mara-matnr,

s_dispo FOR plaf-dispo.

SELECTION-SCREEN : END OF BLOCK 1.

SELECT mara_{normt plaf}matnr

plaf_{pwwrk plaf}paart plaf~verid

INTO TABLE t_plaf

FROM plaf

INNER JOIN mara

ON plaf_{matnr EQ mara}matnr

WHERE plaf~paart = 'ZX'

AND plaf~pwwrk IN s_werks

AND plaf~psttr IN s_psttr

AND plaf~dispo IN s_dispo

AND plaf~matnr IN s_matnr.

IF NOT t_plaf[] IS INITIAL.

SELECT matnr normt

INTO table t_mara

FROM mara

FOR ALL ENTRIES IN t_plaf

WHERE matnr = t_plaf-matnr

AND normt IN s_planpl.

ENDIF.

Re: Select Statement

Former Member — Mon, 02 Apr 2007 14:15:46 GMT

Hi,

whyyou are using MARA table twice in the both selects.

NORMT is in MARA table not in plaf table.

First select is enough to get the values.

no need of second MARA select since you are fetching NORMT and MATNR fields in the first select itself.

SELECT mara_{normt plaf}matnr

plaf_{pwwrk plaf}paart plaf~verid

INTO TABLE t_plaf

FROM plaf

INNER JOIN mara

ON plaf_{matnr EQ mara}matnr

WHERE plaf~paart = 'ZX'

AND plaf~pwwrk IN s_werks

AND plaf~psttr IN s_psttr

AND plaf~dispo IN s_dispo

AND plaf~matnr IN s_matnr.

reward if useful

regards,

Anji

Re: Select Statement

Former Member — Mon, 02 Apr 2007 14:18:25 GMT

Hi Madhu,

Please use the query for PLAF and MARA.

Please remove your join operation and execute it separately as below

SELECT mara~normt 
              plaf~matnr
              plaf~pwwrk 
              plaf~paart 
              plaf~verid
 INTO TABLE t_plaf
 FROM plaf
 WHERE plaf~paart = 'ZX'
 AND plaf~pwwrk IN s_werks
 AND plaf~psttr IN s_psttr
 AND plaf~dispo IN s_dispo
 AND plaf~matnr IN s_matnr.
IF NOT t_plaf[] IS INITIAL.
 SELECT matnr normt
 INTO table t_mara
 FROM mara
 FOR ALL ENTRIES IN t_plaf 
 WHERE matnr = t_plaf-matnr
 AND normt IN s_planpl.
ENDIF.

Reward Point if helpfull*****************

Re: Select Statement

ferry_lianto — Mon, 02 Apr 2007 14:19:15 GMT

Hi,

Please try this.


...

IF NOT t_plaf[] IS INITIAL.
SELECT matnr normt
INTO table t_mara
FROM mara
FOR ALL ENTRIES IN t_plaf
WHERE matnr = t_plaf-matnr
AND normt = t_plaf-normt.                  < --- change in here
ENDIF. 

...

Regards,

Ferry Lianto

Re: Select Statement

Former Member — Mon, 02 Apr 2007 14:20:50 GMT

Some times it may not match, if the data type doesnot match.

Data type may be same between plaf-matnr and mara-matnr, but also check the length, for mara-matnr it is 18, check for plaf-matnr.

Regards

Re: Select Statement

Former Member — Mon, 02 Apr 2007 14:21:12 GMT

Thanks I never realized that.

That makes sense.

However I am curious to know why I dont see any data in the second select statement if data exists in the database.

Any suggestions.

Re: Select Statement

Former Member — Mon, 02 Apr 2007 14:24:04 GMT

Ferry,

Can you explain why you suggested to make the changes. I can also use the data in the selection option to fetch the data . Am I right.

I understand I have data in t_plaf for normt but why cant I use it from selection Screen.

Thanks,

Madhu

Re: Select Statement

Former Member — Mon, 02 Apr 2007 14:25:39 GMT

Hi Madhu,

Please use this function module to convert your material number and then try it will work...

CONVERSION_EXIT_MATN2_INPUT

Thanks and Regards,

Kunjal Patel

Re: Select Statement

Former Member — Mon, 02 Apr 2007 14:31:02 GMT

Thanks Anji for your suggestion. However i changes my First select statement a bit and now the first dosent work.

SELECT mara_{normt plaf}matnr

plaf_{pwwrk plaf}paart plaf~verid

INTO TABLE t_plaf

FROM plaf

INNER JOIN mara

ON plaf_{matnr EQ mara}matnr

WHERE plaf~paart = 'ZX'

AND plaf~pwwrk IN s_werks

AND plaf~psttr IN s_psttr

AND plaf~dispo IN s_dispo

AND plaf~matnr IN s_matnr

and mara~normt IN s_planpl.

ANy suggestions.

Re: Select Statement

Former Member — Mon, 02 Apr 2007 14:35:47 GMT

REPORT YCHATEST.

SELECT MARA_{NORMT PLAF}MATNR

PLAF_{PWWRK PLAF}PAART PLAF~VERID

INTO TABLE T_PLAF

FROM PLAF

INNER JOIN MARA

ON PLAF_{MATNR EQ MARA}MATNR

WHERE PLAF~PAART = 'ZX'

AND PLAF~PWWRK IN S_WERKS

AND PLAF~PSTTR IN S_PSTTR

AND PLAF~DISPO IN S_DISPO

AND PLAF~MATNR IN S_MATNR.

LOOP AT T_PLAF INTO WA_T_PLAF.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = WA_T_PLAF-MATNR

IMPORTING

OUTPUT = WA_T_PLAF-MATNR.

MODIFY T_PLAF FROM WA_T_PLAF.

ENDLOOP.

IF NOT T_PLAF[] IS INITIAL.

SELECT MATNR NORMT

INTO TABLE T_MARA

FROM MARA

FOR ALL ENTRIES IN T_PLAF

WHERE MATNR = T_PLAF-MATNR

AND NORMT IN S_PLANPL.

ENDIF.

******************not working***************************

Re: Select Statement

Former Member — Mon, 02 Apr 2007 14:41:06 GMT

Thanks Chandreshkar.

At last I see Not working in your answer. Is it working for you or its not.

I tried dosent work.

Madhu.

Re: Select Statement

ferry_lianto — Mon, 02 Apr 2007 14:55:46 GMT

Hi Madhu,

Yes you can use selection option to fetch data. The reason I was suggesting is that you already have data in normt in internal table t_plaf (I may not see clearly first select statement).

Perhaps you can try this to filter t_mara based on s_planpl.


...

IF NOT t_plaf[] IS INITIAL.
SELECT matnr normt
INTO table t_mara
FROM mara
FOR ALL ENTRIES IN t_plaf
WHERE matnr = t_plaf-matnr
AND normt = t_plaf-normt.

DELETE t_plaf where not normt in s_planpl. 

ENDIF.

...

Regards,

Ferry Lianto

Re: Select Statement

former_member184495 — Mon, 02 Apr 2007 16:37:13 GMT

Hi MR,

in your internal table declaration

you have declared t_plaf-matnr LIKE plaf-matnr,

if you feel your statement is right :

possibilities could be

a)now MATNR is char18 so is your t_plaf-matnr storing a value with char18,

b)try to declare t_plaf-matnr LIKE mara-matnr.

Cheers.

Aditya.