Comparing two tables

[simantini_sh]

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?

[FredericGirod]

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.
       

[simantini_sh]

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

[BenjaminWeishei]

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

[simantini_sh]

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.

[VXLozano]

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.

[former_member753791]

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

[RaymondGiuseppi]

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>