topic Re: Join Query Issue in Application Development and Automation Discussions

Join Query Issue

Former Member — Sat, 02 Jul 2011 05:49:51 GMT

Hi,

I have issue in the following query when selecting SPART . If spart is selected , the results are not correct. Is there anything i am missing in the query.

SELECT a_{matnr a}ersda a_{laeda a}prdha a_{spart b}maktx c_{prodh c}vkorg c_{vtweg c}mvgr1 c_{mvgr2 c}mvgr3 c_{mvgr4 c}mvgr5

INTO CORRESPONDING FIELDS OF TABLE tbl_initdata

FROM ( ( mara AS a

INNER JOIN makt AS b ON a_{matnr = b}matnr )

INNer JOIN mvke AS c ON a_{matnr = c}matnr )

WHERE a~mtart EQ p_mtart AND

a~ersda IN s_ersda AND

a~laeda IN s_laeda AND

a~lvorm EQ p_lvorm AND

a~spart IN s_spart AND

c~vkorg IN s_vkorg AND

c~vtweg IN s_vtweg AND

c~lvorm EQ p_plvor.

Re: Join Query Issue

Former Member — Sat, 02 Jul 2011 07:03:49 GMT

Hello ,

.Change Select-Options s_spart for <>, to parameter : s_spart type <> and make it EQ.

its working.

SELECT a_{matnr a}ersda a_{laeda a}prdha a_{spart b}maktx c_{prodh c}vkorg c_{vtweg c}mvgr1 c_{mvgr2 c}mvgr3 c_{mvgr4 c}mvgr5

INTO CORRESPONDING FIELDS OF TABLE tbl_initdata

FROM ( ( mara AS a

INNER JOIN makt AS b ON a_{matnr = b}matnr )

INNer JOIN mvke AS c ON a_{matnr = c}matnr )

WHERE a~mtart EQ p_mtart AND

a~ersda IN s_ersda AND

a~laeda IN s_laeda AND

a~lvorm EQ p_lvorm AND

a~spart EQ s_spart AND

c~vkorg IN s_vkorg AND

c~vtweg IN s_vtweg AND

c~lvorm EQ p_plvor.

Edited by: Annasaheb More on Jul 2, 2011 9:04 AM

Re: Join Query Issue

Sandra_Rossi — Sat, 02 Jul 2011 09:56:07 GMT

Hi,

Change Select-Options s_spart for  <>, to parameter :  s_spart type <>  and make it EQ. its working.

I wonder why using select-options s_spart ... along with IN s_spart wouldn't work?

Sandra

Re: Join Query Issue

Clemenss — Sat, 02 Jul 2011 10:20:42 GMT

Hi Z,

I gave thecode a little formatting because use of AS is not required and does not give transparency.

SELECT 
  mara~matnr mara~ersda mara~laeda mara~prdha mara~spart 
  makt~maktx 
  mvke~prodh mvke~vkorg mvke~vtweg mvke~mvgr1 mvke~mvgr2 mvke~mvgr3 mvke~mvgr4 mvke~mvgr5
  INTO CORRESPONDING FIELDS OF TABLE tbl_initdata
  FROM mara
  INNER JOIN makt ON mara~matnr = makt~matnr
  INNer JOIN mvke ON mara~matnr = mvke~matnr
  WHERE 
    mara~mtart EQ p_mtart AND
    mara~ersda IN s_ersda AND
    mara~laeda IN s_laeda AND
    mara~lvorm EQ p_lvorm AND
    mara~spart IN s_spart AND
    mvke~vkorg IN s_vkorg AND
    mvke~vtweg IN s_vtweg AND
    mvke~lvorm EQ p_plvor.

1. You did not say anything about the type of error. "results are not correct" does not mean anything.

2. there is no condition for MAKT-SPRAS. All material with text maintained in more that one language will result in multiple records.

3. You dis not say what the purpose of the selection is.

4. MARA-SPART is not necessarily related to MVKE data.

Regards,

Clemens

Re: Join Query Issue

deepak_dhamat — Sat, 02 Jul 2011 10:23:30 GMT

Hi ,

change according to Below query

even your query should also work

There is no problem of s_spart condition with IN .

You should check values such as p_lvorm and p_plvor because if those values are blank then your query will return no values as EQ is used

SELECT a~matnr a~ersda a~laeda a~prdha a~spart b~maktx c~prodh c~vkorg c~vtweg c~mvgr1 c~mvgr2 c~mvgr3 c~mvgr4 c~mvgr5
INTO CORRESPONDING FIELDS OF TABLE tbl_initdata
FROM  mara AS a
INNER JOIN makt AS b ON  ( a~matnr = b~matnr )
INNer JOIN mvke AS c ON  ( a~matnr = c~matnr )
WHERE a~mtart EQ p_mtart AND
a~ersda IN s_ersda AND
a~laeda IN s_laeda AND
a~lvorm EQ p_lvorm AND
a~spart IN s_spart AND
c~vkorg IN s_vkorg AND
c~vtweg IN s_vtweg AND
c~lvorm EQ p_plvor.

regards

Deepak.

Re: Join Query Issue

Former Member — Sat, 02 Jul 2011 15:27:46 GMT

Dear Friends,

The query is not working when we use SELECT-OPTIONS for SPART. When using paramater its working fine.. For your more analysis, i am giving the Selection screen declarations and my Test Procedure.

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-h01.

PARAMETERS: p_mtart TYPE mtart MEMORY ID mta OBLIGATORY

DEFAULT 'FERT'. "Material Type

SELECT-OPTIONS: s_ersda FOR mara-ersda , "Created on

s_laeda FOR mara-laeda , "Changed on

s_vkorg FOR mvke-vkorg, "Sales Organization

s_vtweg FOR mvke-vtweg, "Distribution Channel

s_spart for mara-spart. "Division

SELECTION-SCREEN END OF BLOCK b1.

SELECTION-SCREEN BEGIN OF BLOCK b2 WITH FRAME TITLE text-h02.

PARAMETERS: p_lvorm AS CHECKBOX DEFAULT space, "Marked for Deletion at Client Level

p_plvor AS CHECKBOX DEFAULT space. "Marked for Deletion at Plant Level

SELECTION-SCREEN END OF BLOCK b2.

HERE is the Select Query,

SELECT a_{matnr a}ersda a_{laeda a}prdha a_{spart b}maktx c_{prodh c}vkorg c_{vtweg c}mvgr1 c_{mvgr2 c}mvgr3 c_{mvgr4 c}mvgr5

INTO CORRESPONDING FIELDS OF TABLE tbl_initdata

FROM mara AS a

INNER JOIN makt AS b ON ( a_{matnr = b}matnr )

INNer JOIN mvke AS c ON ( a_{matnr = c}matnr )

WHERE a~mtart EQ p_mtart AND

a~ersda IN s_ersda AND

a~laeda IN s_laeda AND

a~lvorm EQ p_lvorm AND

a~spart IN s_spart AND

c~vkorg IN s_vkorg AND

c~vtweg IN s_vtweg AND

c~lvorm EQ p_plvor.

TEST PROCEDURE ...

I am just selecting Material Type 'FERT', VKORG ,VTWEG and SPART.... Its returning a fixed value of 20 entries even when there are 1000+ entries in the Database.

Please put this code in your SAP environment and help me out...You can see this strange issue!!!

BTW, Thanks a lot for all SAP folks who are helping me out.

Re: Join Query Issue

Former Member — Sat, 02 Jul 2011 15:50:54 GMT

Hi,

As advised by Deepak ,it could be because of the conditional statements with Equal conditions for fields p_lvorm and p_plvor.

Or it could be because of the inner join , as inner join only returns matching entries for the material in all the three tables . Try using outer join , depending on your requirement.

Regards,

Pawan

Re: Join Query Issue

Former Member — Mon, 04 Jul 2011 05:46:06 GMT

hello,

I see no reason that this query shouldn't work even stranger that it is returning only 20 rows.

I believe the only possibility can be that YOU are missing something while checking in database and there must be only 20 relevant rows. Please re-check again with someone's help, you might be missing some silly thing.

Also, it is good to add language condition for MAKT. Please note carefully that your relevant SPART materials have sales views on them (MVKE entries).

Regards,

Diwakar