Startseite Beiträge getaggt mit "DISTINCT"

Schlagwort: DISTINCT

[ABAP] OpenSQL: Unterschied SELECT, SELECT SINGLE, SELECT DISTINCT

SELECT

* Selektiert alle Einträge für 'DE' und 'US', mit Mehrfachnennungen
SELECT cityto
  INTO TABLE @DATA(it_dest)
  FROM spfli
  WHERE countryfr = 'DE'
     OR countryfr = 'US'.

*CITYTO:
*SAN FRANCISCO
*NEW YORK
*FRANKFURT
*SAN FRANCISCO
*NEW YORK
*TOKYO
*NEW YORK
*FRANKFURT
*NEW YORK
*BERLIN
*FRANKFURT
*SINGAPORE
*SINGAPORE
*SAN FRANCISCO
*FRANKFURT
*FRANKFURT
*NEW YORK

cl_demo_output=>display_data( it_dest ).

SELECT DISTINCT

* Selektiert alle Einträge für 'DE' und 'US', ohne Mehrfachnennungen
SELECT DISTINCT cityto
  INTO TABLE @DATA(it_dest)
  FROM spfli
  WHERE countryfr = 'DE'
     OR countryfr = 'US'.

*CITYTO:
*TOKYO
*BERLIN
*NEW YORK
*SINGAPORE
*FRANKFURT
*SAN FRANCISCO
cl_demo_output=>display_data( it_dest ).

SELECT SINGLE

* Selektiert den ersten Datensatz für 'DE' und 'US'
SELECT SINGLE cityto
  INTO @DATA(lv_dest)
  FROM spfli
  WHERE countryfr = 'DE'
     OR countryfr = 'US'.

*CITYTO:
*SAN FRANCISCO
cl_demo_output=>display_data( lv_dest ).

[ABAP] OpenSQL: Zusammenführen von Tabelleninhalten mit SELECT UNION

Variante 1 (UNION == UNION DISTINCT – einfaches Auftreten der Datensätze)

DATA: lv_devclass TYPE string VALUE 'ZABAP2XLSX'.

SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'PROG' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'CLAS' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'FUGR' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'DEVC' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'DOMA' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'DTEL' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'INTF' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'MSAG' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'TABL' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'TTYP' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'WDYA' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'XSLT' AND devclass = @lv_devclass
UNION DISTINCT
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'WDYN' AND devclass = @lv_devclass
 ORDER BY obj_name
 INTO TABLE @DATA(it_prog_range).

DATA(lv_lines) = lines( it_prog_range ).
cl_demo_output=>write_data( lv_lines ).
cl_demo_output=>write_data( it_prog_range ).
cl_demo_output=>display( ).

Variante 2 (UNION ALL – mehrfach auftretende Datensätze)

DATA: lv_devclass TYPE string VALUE 'ZABAP2XLSX'.

SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'PROG' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'CLAS' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'FUGR' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'DEVC' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'DOMA' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'DTEL' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'INTF' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'MSAG' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'TABL' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'TTYP' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'WDYA' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'XSLT' AND devclass = @lv_devclass
UNION ALL
SELECT obj_name
  FROM tadir
  WHERE pgmid = 'R3TR' AND object = 'WDYN' AND devclass = @lv_devclass
 ORDER BY obj_name
 INTO TABLE @DATA(it_prog_range).

DATA(lv_lines) = lines( it_prog_range ).
cl_demo_output=>write_data( lv_lines ).
cl_demo_output=>write_data( it_prog_range ).
cl_demo_output=>display( ).

Links