topic Re: Comparing two tables in Application Development and Automation Discussions

Comparing two tables

simantini_sh — Tue, 29 Jun 2021 09:17:57 GMT

I want to take date and pernr from CATSDB table based on date from my selection screen and check if this pernr is available in a custom table's pernr. How do I do this?

Re: Comparing two tables

FredericGirod — Tue, 29 Jun 2021 09:23:08 GMT

SELECT list_of_columns_you_want  
       INTO TABLE DATA(my_beautiful_table_CATSDB)
       FROM CATSDB 
       WHERE my_date in S_my_select_option_on_date.

LOOP AT my_beautiful_table_CATSDB
     refence into data(o_ligne_catsdb).
  READ TABLE my_custom_table
       TRANSPORTING NO FIELDS
       WITH KEY pernr = o_ligne_catsdb->pernr.
  IF sy-subrc EQ 0.
    " do something
  ELSE.
    " do something else
  ENDIF.
ENDLOOP.

Re: Comparing two tables

simantini_sh — Tue, 29 Jun 2021 09:27:21 GMT

Hi Fredric, thanks for the help, just wanted to know what is (o_ligne_catsdb)?

Re: Comparing two tables

BenjaminWeishei — Tue, 29 Jun 2021 09:34:46 GMT

You could also use an outer join.

   SELECT CATSDB~PERNR as pernr1,
          ZTABLE~PERNR as pernr2
          FROM CATSDB LEFT OUTER JOIN ZTABLE
          ON CATSDB~PERNR EQ ZTABLE~PERNR
          into TABLE @data(RESULT).

The table "RESULT" should cointain all PERNRs in the first column PERNR1 and should be empty in column PERNR2 if the pernr is missing in your custom Z table

PERN1 | PERNR2

000001 | 000001

000002 | 000000 -> PERNR2 would is missing in your Z Table

000003 | 000003

Re: Comparing two tables

simantini_sh — Tue, 29 Jun 2021 10:29:14 GMT

LOOP AT it_catsdb
REFERENCE INTO DATA(o_ligne_catsdb).
READ TABLE zps_resource_asn
WITH KEY pernr = o_ligne_catsdb->pernr
TRANSPORTING NO FIELDS.
IF sy-subrc = 0.
<fs_it_catsdb>-rproj = <fs_ld_prps>-posid.
ENDIF.

ENDLOOP.

here, I am getting an error saying "Field "PERNR" is unknown. It is neither in one of the specified tables nor defined by a "DATA" statement. "DATA" statement." But I have declared it before.

Re: Comparing two tables

VXLozano — Tue, 29 Jun 2021 10:39:24 GMT

You can pass the loop to a variable and not to a ref data if you want... it's not as cool, but it's effective and easier to understand (I'm strill trying to understand it).
But in the line of Frederic's code

loop at cats into data(cat).
  if line_exists( zres[ pernr = cat-pernr ] ).
    "do something.
  endif.
endloop.

Re: Comparing two tables

former_member753791 — Tue, 29 Jun 2021 13:48:41 GMT

Hi Simantini,

In order to check this, you can use FOR ALL ENTRIES to be the most accurate option.

Select  pernr
       workdate
FROM CATSDB
INTO TABLE GT_CATSDB
WHERE WORKDATE EQ P_DATE.
IF sy-subrc IS INITIAL.
SORT gt_catsdb BY pernr.
ENDIF.

Data(lt_catsdb) = gt_catsdb.
DELETE lt_catsdb WHERE PERNR IS INITIAL.
DELETE ADJACENT DUPLICATES FROM lt_catsdb COMPARING lt_catsdb.

IF lt_catsdb IS NOT INITIAL.
SELECT PERNR
FROM ZTABLE
INTO TABLE GT_ZTABLE
FOR ALL ENTRIES IN lt_catsdb
WHERE PERNR = lt_catsdb-pernr.
IF  sy-subrc IS INITIAL.
SORT gt_ztable BY pernr. 
ENDIF. 
ENDIF.

LOOP AT gt_catsdb INTO gs_catsdb.
READ TABLE gt_ztable TRANSPORTING NO FIELDS WITH KEY pernr = gs_catsdb-pernr BINARY SEARCH.
IF sy-subrc IS INITIAL.
"Do something like below example
MESSAGE "Pernr exists in ZTABLE" TYPE 'S'.
ELSE.
MESSAGE "Pernr does not exists in ZTABLE" TYPE 'S' DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF
ENDLOOP.
CLEAR gs_catsdb.

Regards,

Rohit

Re: Comparing two tables

RaymondGiuseppi — Fri, 02 Jul 2021 09:32:46 GMT

What do you want to do, do you need the records with data in both tables or only records without corresponding records?

You should look at the multiple JOIN types available in Abap SQL, you could also consider subquery.

" JOIN 
SELECT DISTINCT catsdb~pernr, catsdb~workdate, ztable~pernr AS pernr_check
  INTO TABLE @DATA(my_itab)
  FROM catsdb
  JOIN ztable " Adapt to your requirement LEFT/INNER/OUTER
    ON ztable~pernr EQ catsdb~pernr.
" SUBQUERY
SELECT DISTINCT catsdb~pernr, catsdb~workdate
  INTO TABLE @DATA(my_itab_2)
  FROM catsdb
  WHERE NOT EXISTS ( SELECT * FROM ztable WHERE pernr EQ catsdb~pernr ). " Adapt to your requirement<br>