topic Re: select statement in Application Development and Automation Discussions

select statement

Former Member — Mon, 19 May 2008 16:52:13 GMT

v hv 2 tables,,from which i hv to match a common field ...which is having the same name........and i want to retrieve the data from the other field of table 2 ........and i hv to do it with out using inner join ....so plz suggest me?????

Re: select statement

Former Member — Mon, 19 May 2008 17:11:55 GMT

Hi,

Here, i am taking two tables mara and marc.

If you want to do without innerjoin, then you need to declare 2 internal tables and you need to use FOR ALL ENTRIES.

i am giving sample code here.

Here, instead of "*" you can fetch required fields according to ur use.

data : itab1 type standard table of mara,

itab2 type standard table of marc.

select * from mara into table itab.

select werks from marc into corresponding fields of table itab2 for all entries in itab1 where matnr = itab1-matnr.

Reward points if helpful.

Regards

Sandeep reddy

Edited by: Sandeep Reddy on May 19, 2008 7:12 PM

Re: select statement

Former Member — Mon, 19 May 2008 17:14:19 GMT

Let's assume the first table is named 'TABLE1' and the second 'TABLE2', and the common field is FIELD.

First: Do the selection in TABLE1 and save the result in an internal table:

SELECT * INTO CORRESPONDING FIELDS OF TABLE tb1 FROM table 1 WHERE ...(any condition you want)

Second: You have to do the selection in the second table with the aditional statement 'FOR ALL ENTRIES', and then you have to check the common field in the WHERE statement.

SELECT * INTO tb2 FROM table2

FOR ALL ENTRIES IN tb1

WHERE field = tb1-field AND ...(any other condition you want)

Before doing the second select, you have to check if the internal table tb1 has values (check sy-subrc after the first query); if the internal table is empty the second SELECT would bring you any value from the second table, and if you don't have any other conditions in the WHERE statement, it would bring you the whole table.

Re: select statement

Former Member — Mon, 19 May 2008 17:17:33 GMT

Hi,

if the both tables are having common field, 1st you get a data into one table

using that table you can get other table data which is relevant to 1st table.

using for all entries.

Eg:

select * from makt

into table it_makt

for all entries in it_mara

where matnr = it_mara-matnr.

Regards

Ganesh

Re: select statement

Former Member — Mon, 19 May 2008 19:45:03 GMT

If you want an example to join 3 tables, you can find it at this path in SAP

ABAPDOCU(Transaction)~~>ABAP Database Access~~>Open SQL~~>Read data~~>Inner Join.

you can find the following code*

DATA: BEGIN OF wa,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

fldate TYPE sflight-fldate,

bookid TYPE sbook-bookid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY carrid connid fldate bookid.

SELECT p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

LOOP AT itab INTO wa.

AT NEW fldate.

WRITE: / wa-carrid, wa-connid, wa-fldate.

ENDAT.

WRITE / wa-bookid.

ENDLOOP.

In the same way you can join two tables.

Re: select statement

Former Member — Mon, 19 May 2008 20:09:20 GMT

Hi,

FOR ALL ENTRIES IN it's possibly the best way to do this.

Just match a common field by putting in into the WHERE clause.

Note:it's VERY important to the program's performance that the FOR ALL ENTRIES IN table doesn't have any duplicate records. If you need those duplicate records, just make an auxiliar FOR ALL ENTRIES IN table.

Like this:


TYPES: BEGIN OF ty_mara,
matnr TYPE mara-matnr
mtart  TYPE mara-mtart
ernam TYPE mara-ernam
END OF ty_mara.

TYPES: BEGIN OF ty_marc,
matnr TYPE mara-matnr
werks TYPE marc-werks
END OF ty_marc.

DATA: itab_mara  TYPE TABLE OF ty_mara,
          itab_marc  TYPE TABLE OF ty_marc.

SELECT mara matnr ernam 
FROM mara
INTO TABLE itab_mara.

*** Here, you retrieve data from itab_marc, according to itab_mara:
SELECT matnr werks
FROM marc
INTO TABLE itab_marc
FOR ALL ENTRIES IN itab_mara
WHERE matnr = mara-matnr.

Hope it helps,

Brian Gonsales