topic Re: Problem with JOIN, repat data in Application Development and Automation Discussions

Problem with JOIN, repat data

Former Member — Wed, 10 Dec 2014 00:16:26 GMT

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?

Re: Problem with JOIN, repat data

Former Member — Wed, 10 Dec 2014 06:20:02 GMT

Hi,

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

Regards,

Klaus.

Re: Problem with JOIN, repat data

Former Member — Wed, 10 Dec 2014 16:49:04 GMT

Hello,

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

Re: Problem with JOIN, repat data

roberto_vacca2 — Wed, 10 Dec 2014 17:33:37 GMT

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.

Re: Problem with JOIN, repat data

fcorodriguezl — Wed, 10 Dec 2014 18:00:30 GMT

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.

Re: Problem with JOIN, repat data

Former Member — Wed, 10 Dec 2014 18:50:49 GMT

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?

Re: Problem with JOIN, repat data

roberto_vacca2 — Thu, 11 Dec 2014 11:28:54 GMT

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

Re: Problem with JOIN, repat data

Former Member — Thu, 11 Dec 2014 12:08:03 GMT

Hi,

did you try to add a DISTINCT after SELECT ?

Regards,

Klaus