Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Problem with JOIN, repat data

Former Member
0 Kudos
232

Hello,

I have query below:


SELECT a~j_3gblart "Clase documento

          b~j_3gbelnri  "Número de documento PBE

          b~j_3gposnri " Número de posición PBE

          b~j_3guposnr "Subposición

          b~erdat      "Creado el:

          b~ernam      "Creado por

          b~pernr      "Nº personal

          b~j_3gemppsp "Elemento PEP

          b~j_3gemppro "Nº proyecto

          b~j_3gempkos  "Cemtro de coste

          b~j_3gbelsta  "Stat.doc.interno

          b~j_3gbelste  "Stat.doc.externo

          b~j_3gempfae  "Destinatario

          b~j_3glvart     "Clase Actividad

          b~matnr       "Material

          b~j_3gdatlab  "Fe.prest.actividad

          b~j_3gmenge   "Cantidad

          b~j_3gmeins   "Unidad de medida

          c~name1

          c~name2

          d~eqart  " Categoria del Equipo

          d~vbeln  " Documento Venta

          d~posnr  "Posicion

          d~netwr  "Valor Neto

          d~waerk  "Moneda

          e~vbeln AS vbeln2 "vbeln de la VBRP

       FROM j_3gbelk AS a

     INNER JOIN j_3gbelp AS b

     ON a~mandt EQ b~mandt

     AND a~j_3gbelnri  EQ b~j_3gbelnri

     INNER JOIN vbap AS d ON d~j_3gbelnri EQ b~j_3gbelnri

     LEFT JOIN vbrp AS e ON d~vbeln EQ e~aubel

     INNER JOIN kna1 AS c ON c~kunnr = b~j_3gempfae

     INTO CORRESPONDING FIELDS OF TABLE it_alv

     WHERE b~j_3gbelnri IN p_etm

     AND   b~j_3guposnr IS NOT NULL

     AND   j_3gdatlab IN p_factiv

     AND   j_3gblart IN p_cdoc

     AND   b~equnr IN p_equipo

     AND   b~pernr IN p_person

     AND   b~j_3gempfae IN p_desti

     AND   eqart IN p_catequ.

When I run my ALV report with this document: J_3GBELNRI =  2000003987 and I get repeat data:

If I go to the table, I get only 12 rows, this is the correct result, and I want to have this result in my ALV Report, Could anyone can help me?

7 REPLIES 7

former_member195402
Active Contributor
0 Kudos
176

Hi,

please remove VBRP (e) from the JOIN and check the result. VBRP might give several matches, where one will be fine.

Regards,

Klaus.

0 Kudos
176

Hello,

I have removed the VBRP, and now print less data, but, Its continues much data,I have three records for eachone

0 Kudos
176

HI.

INNER Join means 1 on 1 .. Left Join mean 1 on every null value (no references) it matches. Well you should study better a limit range of data and corrispondences between tables.

That's a standard behaviour for a sql operation

Hope to help

Bye.

fcorodriguezl
Contributor
0 Kudos
176

Hi Luis,

I think is not a good practice this query join. I recommend you have no more than 2 tables in join, and you can use others query by separately.

In your loop you will need READ for your other transparents tables.

I hope help my advice.

Regards.

Former Member
0 Kudos
176

Hello gurus, now I have identify the problem, in my first table (J_3GBELP) I have 12 rows if with this key: J_3GBELNRI = 2000003987, in VBAP I have 3 rows with this key, "but"  in VBAP exist VBELN, this value, is not equal, then, this value repeat my query,  12 * 3 = 36 rows. Please see image below:

Anyone can help me?

0 Kudos
176

Hi.

You could use advanced SQL command selection, as nested select in the where clause but this gives a low performance selection.

IF I understood your needs you want to know only if your J_3GBELNRI exist in VBAP but you don't want to get occurrences repeated.

Instead I suggest to reduce data you need, using a few internal tables with data filtered and match them, because till you'll use standard table you'll have to face primary key rules.

Hope to help

0 Kudos
176

Hi,

did you try to add a DISTINCT after SELECT ?

Regards,

Klaus