Compare 2 fields value of the DB table with another 2 fields of different DB table in sub query.

[avinashd_m]

Hi All,

I have a requirement as below.

Table 1 as fields -> Element ID, attribute ID, Text,

Table2 has fields -> Element ID, attribute ID, sequence number, value_for_element,

Now i need to select Element ID and attribute ID, Text fields value from table1 only if there is same element ID and attribute ID value in Table2.

So i wrote a sub/nested query like below which doesn't work.

Select Element ID attribute ID Text,

from table1 as A where ( A.element ID, A.attribute ID ) in ( select distinct Element ID, attribute ID from Table2 )

So basically , i want to compare 2 fields from outer query to inner query and fetch data from outer table.

Please suggest the possible ways to achieve this requirement.

Thanks in advance,

Avinash

[geert-janklaps]

Hi,

Can't you just use an inner join for your requirement?

SELECT DISTINCT table1~elementid, table1~attributeid, table1~text
FROM table1
INNER JOIN table2 ON table2~elementid = table1~elementid AND table2~attributeid = table1~attributeid
INTO TABLE @DATA(lt_result).

Best regards,

Geert-Jan Klaps

[avinashd_m]

Hi Geert-Jan Klaps,

Thanks for the response.

My second table 'table2' can contain duplicate records like below

Element ID, attribute ID, sequence number, value_for_element,

0001, 001, 1, ABC

0001, 001, 2, XYZ

0002, 001, 1, PQR

0002, 002, 2, ABC

So with you suggestion, though it works fine, but with inner join, even though in second table we have only 2 distinct records for Element ID, attribute ID, the system will end up comparing all the 4 records on execution i think, so to avoid that, i wanted to use nested query where i will be comparing with only distinct records.

Please correct me if i am wrong here.

Regards,

Avinash

[geert-janklaps]

Hi,

Did you try something like following?

SELECT table1~elementid, table1~attributeid, table1~text
  FROM table1
  WHERE ( table1~elementid, table1~attributeid ) IN ( SELECT DISTINCT table2~elementid, table2~attributeid FROM table2 )
  INTO TABLE @DATA(lt_result).

Not sure if this will be more performant compared to the inner join version. I would probably use the inner join, but it might be worth to compare both version e.g. using a SAT / SQL trace.

I used following example to try the above example:

SELECT vbap~vbeln, vbap~posnr
  FROM vbap
  WHERE ( vbap~vbeln, vbap~posnr ) IN ( SELECT DISTINCT vbep~vbeln, vbep~posnr FROM vbep )
  INTO TABLE @DATA(lt_result).

Best regards,

Geert-Jan Klaps

[Sandra_Rossi]

Does this syntax really work in ABAP SQL? ("WHERE( vbap~vbeln, vbap~posnr )IN(SELECTDISTINCT vbep~vbeln, vbep~posnr FROM vbep )")

I thought it had to be written this way (I use other tables here):

SELECT table1~carrid, table1~connid, table1~fldate
  FROM sflight AS table1
  WHERE EXISTS ( SELECT * FROM spfli as table2 WHERE table2~carrid = table1~carrid
                                                 AND table2~connid = table1~connid )
  INTO TABLE @DATA(lt_result).

[Patrick_vN]

I think both options work: sap.help.com. Though I admit that in the example, there is only a check on a single field (cityfrom).

[geert-janklaps]

Hi Sandra,

According to the documentation (I'm looking in an S/4HANA 2020 system) this should work:

At least the result was correct when I tested it 🙂 (but I'm typically avoiding sub queries as much as possible, so I'm not really an expert on this topic)

Best regards,

Geert-Jan

[Sandra_Rossi]

Thanks! Got it: added in 7.54, see Modification 8 of https://help.sap.com/doc/abapdocu_754_index_htm/7.54/en-US/index.htm?file=abennews-754-abap_sql.htm