[OpenSQL] Joining previously selected data

[Former Member]

usually you do JOINS like this:

SELECT foo1 foo2 INTO TABLE inttab FROM tab1 INNER JOIN ON tab1~field = tab2~field WHERE ....

Its ok but the WHERE condition is applied on the whole set of data AFTER the join.

What i need to do is first select data THEN apply join on that selected data it would sound something like this :

SELECT field1 INTO foo0 FROM (SELECT {here goes statement }) INNER JOIN ON.....

I expect it to work in that manner that the inner SELECT is executed first - then the join is applied on the returned dataset.

Is this possible to achieve in OpenSQL ?

[Former Member]

HI,

Use the For All Entries..

First select the data into internal table from itab1 and later select data from itab2 using for all entries from itab1.

Select * from <Table1> into  itab1 where <condition>.

Select * from <Table2> into  itab2 For All Entries in itab1 where  <field = itab1-field>.

Or

SELECT field1 INTO foo0 FROM <Table1> where Field = ( SELECT field From <Table2> Where <Condt>.).
ENDSELECT.

[Former Member]

The best approach is to use select seperately.

Select the entries in the first table according to your condition .

In the second select you can use for all entries and fetch the data accordingly.

Regards,

Lalit Mohan Gupta.

[Former Member]

Hi Jacek,

Select for all entries will not give you good performance. The best way to go is by using joins. If you are using the index correctly, your inner joins will give you much better performance than select for all entries or looping over internal tables.

But you can't use joins for cluster tables/pooled tables. In that case you will have to use for all entries.

The method you have mentioned above is possible in Native SQL. But here you can use sub queries in the where clause and not the from clause. Open SQL will not give you some of the flexibilities available with native SQL.

Regards,

Abdullah

[Former Member]

Hello Abdullah !

Thanks for the reply - but could you give me some native SQL Example that uses subqueries to achieve what i want ?

I just want to know ifi get things right - if i cant use subqueries in FROM clause - where should i use them ?

[MarcinPciak]

Hi Jacek,

Have a look at these:

1) an example of nesting select queries - not recommended as performance here is usually poor. Isntead use inner join which is more effective.

SELECT foo1 foo2 FROM dbtab1 INTO wa_dbtab1.
    SELECT foo1 foo2 FROM dbtab2 INTO wa_dbtab2
                                    WHERE wa_dbtab1-field1 = wa_dbtab2-field1
                                            AND wa_dbtab1-field2 = wa_dbtab2-field2.
ENDSELECT.

2) an example of subquery - more effective than above but generally not recommended too

SELECT foo1 foo2 INTO wa_dbtab1
                               FROM dbtab1
                                "subquery is formulated right here, this one will check read record from outer table only if there is corresponding entry in inner table 
                                WHERE EXISTS (SELECT foo1 FROM dbta2     "only a single column here allowed
                                                               WHERE .... ) 

ENDSELECT.

In the latter example you can use as well LIKE, = , BETWEEN , IN after WHERE. Depending on the addition, your subquery can return either a single value or a set of values.

For more information please refer [subqueries|http://help.sap.com/saphelp_nw70/helpdata/EN/dc/dc7614099b11d295320000e8353423/frameset.htm].

Note!

I am not talking about performance here. Generally fetching data into some internal tables can be more effective than the methods described above. Anyhow, that was not the question, right:)

Regards

Marcin

[Former Member]

Okay so maybe ill guve more practical example

I have many records in T558A table (over 10 milion) i need to :

1. Fetch max date ENDDA for each employee

2. Join that data with one field of PA0003 table.

So i do it in two queries :

SELECT DISTINCT pernr FROM T558A GROUP BY pernr SORT BY endda DESCENDING.

the second one is SELECT SINGLE in a loop that fetches from PA0003.

The thing is that generating this query takes close to 10 min - im aiming on lowering the time as much as possible

[Former Member]

A JOIN is still probably your best bet. If you retrieve and process a lot of data, it will take some time. There's no getting around that.

Rob

[Former Member]

Okay - one last question

Does join condition need to contain only '=' operator in SQL ?

Is this join valid in OpenSQL ? :

SELECT f1 FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1~pernr = t2~pernr AND t1~date < t2~date WHERE......

[MarcinPciak]

No, you can only use = when formulating a join condition. You can however set different comparison marks in WHERE clasue.

Please have a look at this query. It may help you with your requirement.

DATA: BEGIN OF wa,
       pernr TYPE pa0003-pernr,
       prdat TYPE pa0003-prdat,
       endda TYPE t558a-endda,
      END OF wa.

SELECT a~pernr MAX( a~endda ) b~prdat INTO (wa-pernr, wa-endda, wa-prdat)
                          FROM t558a AS a INNER JOIN pa0003 AS b ON
                               a~pernr = b~pernr
                          GROUP BY a~pernr b~prdat.
  WRITE: / wa-pernr, wa-endda, wa-prdat.
ENDSELECT.

One more thing. Don't use SELECT/SINGLE statements inside the loop. This produces same sturctured queries (even the values are different) hence DB interface sends alike queries to DB, which is unnecessary. Avoid this. Instead buffer such query in internal table, and once data fetched, read your table entires in a way you want. This will be relief for DB.

Of course this has to be considered case by case. Here, when data number is huge, this could cause unnecessary network load. So always try to think about those two aspects and choose the best fitting one.

Regards

Marcin