topic Re: Select multiple dtab's in Application Development and Automation Discussions

Select multiple dtab's

Former Member — Mon, 18 Jul 2005 11:41:18 GMT

Hi guys,

is it possible to select multiple dtab`s?

I thought about something like this:

" Select var1.dtab1, var2.dtab2 from dtab1, dtab2 where var3.dtab1 = var4.dtab2 ..."

Sorry for that Question but i'm a newbie in ABAP.

Thanks for reply's

Marcus

Re: Select multiple dtab's

athavanraja — Mon, 18 Jul 2005 11:46:18 GMT

yes, this is called JOINS

here is a sample code from ABAP key word documentation.

for more on this topic read the ABAP key word documentation for JOIN

PARAMETERS: p_cityfr TYPE spfli-cityfrom, 
            p_cityto TYPE spfli-cityto. 

DATA: BEGIN OF wa, 
         fldate TYPE sflight-fldate, 
         carrname TYPE scarr-carrname, 
         connid   TYPE spfli-connid, 
       END OF wa. 

DATA itab LIKE SORTED TABLE OF wa 
               WITH UNIQUE KEY fldate carrname connid. 

SELECT c~carrname p~connid f~fldate 
       INTO CORRESPONDING FIELDS OF TABLE itab 
       FROM ( ( scarr AS c 
         INNER JOIN spfli AS p ON p~carrid   = c~carrid 
                              AND p~cityfrom = p_cityfr 
                              AND p~cityto   = p_cityto ) 
         INNER JOIN sflight AS f ON f~carrid = p~carrid 
                                AND f~connid = p~connid ). 

LOOP AT itab INTO wa. 
  WRITE: / wa-fldate, wa-carrname, wa-connid. 
ENDLOOP.

Regards

Raja

Re: Select multiple dtab's

Former Member — Mon, 18 Jul 2005 11:47:19 GMT

Hi Marcus,

u can achieve the same using Joins:

check this sample query:

select cdhdr_{username cdpos}value_old cdpos~value_new
into table i_output
from ( cdhdr INNER JOIN cdpos

* on cdhdr_{objectid = cdpos}objectid )

where cdhdr~objectclas = lc_class
and cdhdr~objectid like lv_objid
and cdhdr~change_ind = lv_status
and cdpos_{objectid = cdhdr}objectid
and cdpos_{tabkey = cdhdr}objectid
and cdpos_{changenr = cdhdr}changenr.

But the two tables should be having a field in common. Now try it out this way as per your requirement.

hope this helps you.

Regards,

Anjali.

Re: Select multiple dtab's

Vinod_Chandran — Mon, 18 Jul 2005 11:48:10 GMT

Yes it is possible using JOINS.

See this example

DATA: CUSTOMER TYPE SCUSTOM,

BOOKING TYPE SBOOK.

SELECT SCUSTOM_{NAME SCUSTOM}POSTCODE SCUSTOM~CITY

SBOOK_{FLDATE SBOOK}CARRID SBOOK_{CONNID SBOOK}BOOKID

INTO (CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID)

FROM SCUSTOM LEFT OUTER JOIN SBOOK

ON SCUSTOM_{ID = SBOOK}CUSTOMID AND

SBOOK~FLDATE = '20011015'

ORDER BY SCUSTOM_{NAME SBOOK}FLDATE.

WRITE: / CUSTOMER-NAME, CUSTOMER-POSTCODE, CUSTOMER-CITY,

BOOKING-FLDATE, BOOKING-CARRID, BOOKING-CONNID,

BOOKING-BOOKID.

ENDSELECT.

Re: Select multiple dtab's

Former Member — Mon, 18 Jul 2005 11:49:47 GMT

Hello Raja,

I know the ABAP key word documentation,

and JOINS are not really new for me, but i thought there will be another solution for that problem.

In other program languages like PHP, it is possible to do:

Select var1.dtab1 from dtab1,dtab2 where .......

So it isn´t possible in ABAP?

regards

Marcus

Re: Select multiple dtab's

athavanraja — Mon, 18 Jul 2005 11:53:10 GMT

there is no other way using OPEN SQL.

I dont know whether NATIVE SQL (which you can use in an ABAP program) supports this kind of selects.

Regards

Raja

Re: Select multiple dtab's

Former Member — Mon, 18 Jul 2005 11:57:15 GMT

Hi,

This can be done by using Inner join in select query u can do a f1 check in select query,

eg.

SELECT c~ekgrp
a~matnr
b~maktx
c~werks
c~minbe
INTO CORRESPONDING FIELDS OF TABLE i_output
FROM mara AS a INNER JOIN makt AS b
ON a_{matnr = b}matnr
INNER JOIN marc AS c
ON a_{matnr = c}matnr
inner join MSKU as d* on a_{matnr = d}matnr* WHERE a~matnr IN s_matnr
AND a~mtart IN s_mtart
AND c~werks IN s_werks
AND c~ekgrp IN s_ekgrp
AND c~lgfsb IN s_lgfsb
and d~KUNNR in s_kunnr
and d~sobkz = c_w
and d~kulab > 0.

In this case it is for four tables.

Similarly u can check the sample code in the documentation.

If ur problem got solved kindly reward points and close the thread.

Re: Select multiple dtab's

tongping — Thu, 15 Nov 2007 02:42:03 GMT

hi,

In a join, is there an easy way to select all fields of one of the table?

E.g.

SELECT a~* INTO some_itab

FROM ( table_1 AS a

INNER JOIN table_2 AS b ON ....).

With a_{*, I want to select all fields of a into some_itab. Of course, a}* was reported not illegal grammar. Just want to know: is that possible to avoid use a_{field1, a}field2, ... to fetch all fields of a in the join ABAP open SQL?

Thanks and kind regards,

Said

Re: Select multiple dtab's

Former Member — Thu, 15 Nov 2007 02:49:55 GMT

Yes its possible to select data from multiple tables using JOINS..

as above examples..

But we should try to keep no DATAbase tables minimum as good Practise..

Try to yse READ and FOR ALL Entries for multiple tables .

Praveen

Re: Select multiple dtab's

tongping — Thu, 15 Nov 2007 04:25:38 GMT

thanks, praveen.

I think maybe I didn't express clear.

What is wanted is:

there two table a, b; they will be joined; while only all the fields of a need to be in the result. How to do this in ABAP SQL with something like table_1.* in standard SQL?

B.T.W.

Could you kindly explain the READ and FOR ALL in detail?

Regards,

Said