topic Re: Select Query using Joins in Application Development and Automation Discussions

Select Query using Joins

Former Member — Thu, 10 Apr 2008 12:00:47 GMT

Hi,

Please give me the Select query

I have to fetch EBELN,BUKRS,LIFNR,BSART,EKORG,EKGRP,WAERS,WKURS from EKKO and

EBELP,LGORT,MATKL ,WERKS,BEDNR,IDNLF,BPRME,NETPR from EKPO and

MENGE, SAKTO,AUFNR,ANLN1,GSBER,PS_PSP_PNR from EKKN and

LPEIN,EINDT,SLFDT, from EKET.

I have to fetch all the data into an single internal table(Mandatory) by a select query using Joins.

EKKO is having EBELN as primary key

EKPO is having EBELN,EBELP as primary key

EKKN is having EBELN,EBELP as primary key

EKET is having EBELN,EBELP as primary key

Can anybody Please give me the Select query.

With Regards

Ajay

Re: Select Query using Joins

Former Member — Thu, 10 Apr 2008 12:04:48 GMT

Hi ,

I am sending this sample coed for the Inner joins .Hope this will help you:

Inner joins using 3 tables

Try this :-

SELECT stpo_{stlnr stpo}idnrk mast_{matnr mara}mtart stpo~menge

INTO CORRESPONDING FIELDS OF TABLE zmat1 FROM mast

JOIN stpo ON stpo_{stlnr = mast}stlnr

JOIN mara ON mara_{matnr = mast}matnr

WHERE stpo_{stlty = 'M' "AND stpo}idnrk IN s_matnr

AND mast~werks = 1000.

Here s_matnr is a select-options on the selection-screen.

Or this.

Code:

Select single Vbrk_{Bukrs Vbrk}Kunrg Vbrk~Vbeln

Vbrk_{Fkdat Vbrk}Bstnk_Vf Vbrk~Zterm

Tvzbt~Vtext

Vbak_{Vbeln Vbak}Bstdk

Likp_{Vbeln Likp}lfdat Likp~Lfuhr

into w_vbrk

from vbrk

inner join Tvzbt on Tvzbt_{Zterm = Vbrk}Zterm and

Tvzbt~Spras = sy-langu

Inner join Vbfa as SalesLnk

on SalesLnk~vbeln = pu_vbeln and

SalesLnk~vbtyp_v = c_order

inner join Vbak on Vbak_{Vbeln = SalesLnk}Vbelv

Inner join Vbfa as DeliveryLnk

on DeliveryLnk~vbeln = pu_vbeln and

DeliveryLnk~vbtyp_v = c_Delivery

inner join Likp on Likp_{Vbeln = DeliveryLnk}Vbelv

where vbrk~vbeln = pu_Vbeln.

This code locates sales, delivery and payment terms info from a billing document number.

Here, this one also works fine :

select zfpcd_{cadivi zfpcd}proforma zfpcd_{factura zfpcd}aniofactura

zfpcd_{montousd zfpcd}montoap zfpcd_{ebeln zfpcd}inco1

zfpcd_{lifnr lfa1}name1 zcdvs_{status zfpcd}conint

into it_lista

from zfpcd inner join zcdvs

on zfpcd_{ebeln = zcdvs}ebeln

and zfpcd_{proforma = zcdvs}proforma

and zfpcd_{lifnr = zcdvs}lifnr

inner join lfa1

on zfpcd_{lifnr = lfa1}lifnr

where zcdvs~status = '04'.

Also Here is another solution that just uses inner joins:

SELECT vbak_{vbeln vbfa}erdat INTO (itab-vbeln, itab-wadat)

FROM ( vbak INNER JOIN vbap

ON vbap_{vbeln = vbak}vbeln )

INNER JOIN vbfa ON vbak_{vbeln = vbfa}vbelv

WHERE vbak~kunnr = m_wm AND

vbak~vbtyp = 'C' AND

vbfa~erdat IN s_date AND

vbap~matnr = 'MZ1807F' AND

vbfa_{posnv = vbap}posnr AND

vbfa~vbtyp_n = 'J'.

<REMOVED BY MODERATOR>

Cheers,

Chandra Sekhar.

Edited by: Alvaro Tejada Galindo on Apr 10, 2008 5:20 PM

Re: Select Query using Joins

Former Member — Thu, 10 Apr 2008 12:33:06 GMT

Hi,

You can write in this way

select a~ebeln

b~ebelp

c~menge

d~lpein

into table it_data

from ( ( ( ekko as a join ekpo as b

on a_{ebeln = b}ebeln )

join ekkn as c on b_{ebeln = c}ebeln and

b_{ebelp = c}ebelp )

join eket as d on c_{ebeln = d}ebeln and

c_{ebelp = d}ebelp ).

<REMOVED BY MODERATOR>

Thaks,

Suman

Edited by: Alvaro Tejada Galindo on Apr 10, 2008 5:20 PM

Re: Select Query using Joins

Former Member — Thu, 10 Apr 2008 12:55:24 GMT

Hi ,

I also had written the same query But so much Duplicate data is fetching.

Pleae give me the some good query

Regards

Ajay

Re: Select Query using Joins

Former Member — Thu, 10 Apr 2008 13:10:53 GMT

Hi,

Is it possible using Outer joins

Re: Select Query using Joins

Former Member — Thu, 10 Apr 2008 13:25:03 GMT

Hi ajay

if it is fetching a duplicate records then why u want to go in join way

ABAP language has a funda of inter nal table

make select query on all the 3 tables

then create one final table

loop and appen all records in it

use for all entries it will delete duplicate records automatically..

HTH

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Apr 10, 2008 5:21 PM

Re: Select Query using Joins

GrahamRobbo — Fri, 11 Apr 2008 00:06:32 GMT

Hi Ajay,

you can do this with outer joins. (Not the way I would do it).

Anyway, try something like this.


  TYPES:
    BEGIN OF ty_pdoc,
      ebeln TYPE ekko-ebeln,
      bukrs TYPE ekko-ebeln,
      ebelp TYPE ekpo-ebelp,
      lgort TYPE ekpo-lgort,
      matkl type ekpo-matkl,
      zekkn TYPE ekkn-zekkn,
      menge TYPE ekkn-menge,
      etenr TYPE eket-etenr,
      lpein TYPE eket-lpein,
    END OF ty_pdoc.

  DATA: lt_pdocs TYPE TABLE OF ty_pdoc.

  SELECT a~ebeln
          a~bukrs
          b~ebelp
          b~lgort
          b~matkl
          c~zekkn
          c~menge
          d~etenr
          d~lpein
    INTO CORRESPONDING FIELDS OF TABLE lt_pdocs
    FROM ( ( ( ekpo AS b
       LEFT OUTER JOIN ekkn AS c ON c~ebeln = b~ebeln
                               AND c~ebelp = b~ebelp )
       LEFT OUTER JOIN eket AS d ON d~ebeln = b~ebeln
                               AND d~ebelp = b~ebelp )
       LEFT OUTER JOIN ekko AS a ON a~ebeln = b~ebeln ).

Cheers

Graham Robbo