Двойная группировка в АБАП

billion

Собрал таблицу в которой видно из какого подразделения и по какой причине увольнялись.

Упёрся в проблему. Никак не могу сгруппировать так чтоб было видно в каком подразделении сколько было каждой причины. Причём одной таблицой, чтоб потом в эксель выплюнуть.

Code:

TYPES: BEGIN OF PP0001,
  pernr type pa0001-pernr,
  BEGDA TYPE PA0000-BEGDA,
  MASSG TYPE PA0000-MASSG,
  ORGEH TYPE PA0001-ORGEH,
  MGTXT TYPE T530T-MGTXT,
  ORGTX TYPE T527X-ORGTX,
END OF PP0001.
data: wa0000 TYPE PP0001, wa0009 TYPE table of PP0001.
** Сбор всех данных
select a~begda a~MASSG b~ORGEH c~mgtxt d~orgtx into (wa0001-BEGDA,  wa0001-MASSG, wa0001-ORGEH, wa0001-mgtxt, wa0001-orgtx)
from PA0000 as a
join PA0001 as b on a~pernr = b~pernr and b~endda = '99991231'
join T530T as c on c~massg = a~massg and c~massn = 'C1' and c~sprsl = 'RU'
join T527X as d on d~orgeh = b~orgeh and d~endda = '99991231' and d~sprsl = 'RU'
where a~MASSN = 'C1'
AND a~begda >= '20110101'
and a~begda < '20120101'
and b~orgeh > 0
order by b~ORGeh a~begda.
append wa0001 TO wa0009.
* write: / wa0001-begda, wa0001-ORGEH, wa0001-orgtx, wa0001-massg, wa0001-MGTXT.
endselect.

необходимо получить следующее:

Rodon

Сперва выбери условия отбора.
Потом в цикле по этим условиям делай выборки.
И заполняй таблицу результат.

LAT

billion написал(а):

Никак не могу сгруппировать так чтоб было видно в каком подразделении сколько было каждой причины. Причём одной таблицой, чтоб потом в эксель выплюнуть.

Можно попробовать типа такого (код на правильность не проверял!)

Code:

TYPES: BEGIN OF type_massg,
         massg TYPE pa0000-massg,
         mgtxt TYPE t530t-mgtxt,
         count TYPE i,
       END OF type_massg.
DATA: t_massg TYPE TABLE OF type_massg.
DATA: massg TYPE type_massg.
DATA: r_massg TYPE REF TO type_massg.

TYPES: BEGIN OF type_final,
         orgeh TYPE pa0001-orgeh,
         orgtx TYPE t527x-orgtx,
         t_massg TYPE TABLE OF type_massg WITH DEFAULT KEY,
       END OF type_final.
DATA: t_final TYPE TABLE OF type_final.
DATA: r_final TYPE REF TO type_final.

DATA: r_pp0001 TYPE REF TO pp0001.

SORT wa0009 BY massg.
LOOP AT wa0009 REFERENCE INTO r_pp0001.
  IF massg-massg <> r_pp0001->massg.
    massg-massg = r_pp0001->massg.
    massg-mgtxt = r_pp0001->mgtxt.
    APPEND massg TO t_massg.
  ENDIF.
  READ TABLE t_final REFERENCE INTO r_final WITH KEY orgeh = r_pp0001->orgeh.
  IF sy-subrc <> 0.
    APPEND INITIAL LINE TO t_final REFERENCE INTO r_final. " тут, возможно, мизерная потеря в производительности
    r_final->orgeh = r_pp0001->orgeh.
    r_final->orgtx = r_pp0001->orgtx.
  ENDIF.
  READ TABLE r_final->t_massg REFERENCE INTO r_massg WITH KEY massg = r_pp0001->massg.
  IF sy-subrc <> 0.
    APPEND INITIAL LINE TO r_final->t_massg REFERENCE INTO r_massg. " тут, возможно, мизерная потеря в производительности
    r_massg->massg = r_pp0001->massg.
    r_massg->count = r_massg->count + 1.
  ENDIF.
ENDLOOP.

SORT t_final BY orgeh.
LOOP AT t_massg REFERENCE INTO r_massg.
  WRITE: r_massg->mgtxt.
ENDLOOP.
LOOP AT t_final REFERENCE INTO r_final.
  WRITE: r_final->orgeh, r_final->orgtx.
  LOOP AT t_massg REFERENCE INTO r_massg.
    READ TABLE r_final->t_massg INTO massg WITH KEY massg = r_massg->massg.
    IF sy-subrc <> 0.
      CLEAR: massg-count.
    ENDIF.
    WRITE: massg-count.
  ENDLOOP.
ENDLOOP.

Можно еще с at new, at end of поиграться. Или с collect.

И:
1) наверное, лучше сделать select into table, типа:

Code:

SELECT pa0000~begda pa0000~massg pa0001~orgeh t530t~mgtxt t527x~orgtx
  INTO CORRESPONDING FIELDS OF TABLE wa0009
  FROM pa0000 JOIN pa0001 ON pa0001~pernr = pa0000~pernr AND pa0001~endda = '99991231'
              JOIN t530t ON t530t~massg = pa0000~massg AND t530t~massn = 'C1' AND t530t~sprsl = 'RU'
              JOIN t527x ON t527x~orgeh = pa0001~orgeh AND t527x~endda = '99991231' AND t527x~sprsl = 'RU'
  WHERE pa0000~massn = 'C1' AND pa0000~begda >= '20110101' AND pa0000~begda < '20120101' AND pa0001~orgeh > 0.

2) RU' заменить на sy-langu
3) работать с '99991231' с помощью константы

LAT

billion написал(а):

Code:

join T530T as c on c~massg = a~massg and c~massn = 'C1' and c~sprsl = 'RU'
join T527X as d on d~orgeh = b~orgeh and d~endda = '99991231' and d~sprsl = 'RU'

Да, тут лучше сделать left join'ы - вдруг где-то забудут сделать текстовое описание .

Gustav

А вот так радикально на Native SQL не хотите попробовать?

Code:

REPORT  zzzzzz.

TYPES: BEGIN OF pp0001,
        orgtx         TYPE t527x-orgtx, "Подразделение
        count_sobstv  TYPE i, "Собственное желание
        count_pension TYPE i, "Уход на пенсию
       END OF pp0001.

DATA:
    itab                   TYPE STANDARD TABLE OF pp0001,
    l_stmt                 TYPE string,
    l_stmt_ref             TYPE REF TO cl_sql_statement,
    l_res_ref              TYPE REF TO cl_sql_result_set.

DATA: tab_pointer       TYPE REF TO data,
      wa_pointer        TYPE REF TO data.

FIELD-SYMBOLS: <tab_data> TYPE STANDARD TABLE,
               <wa_data>  TYPE pp0001,
               <fs_org>   TYPE ANY,
               <fs_sob>   TYPE ANY,
               <fs_pen>   TYPE ANY.

START-OF-SELECTION.

  CONCATENATE
  `WITH query1 AS ( `
  `SELECT orgtx, mgtxt, COUNT(*) AS cnt `
  `FROM ( `
  `SELECT a.begda, a.massg, b.orgeh, c.mgtxt, d.orgtx `
  `FROM pa0000 a `
  `JOIN pa0001 b ON a.pernr = b.pernr AND b.endda = '99991231' `
  `JOIN t530t  c ON c.massg = a.massg AND c.massn = 'C1' AND c.sprsl = 'RU' `
  `JOIN t527x  d ON d.orgeh = b.orgeh AND d.endda = '99991231' AND d.sprsl = 'RU' `
  `WHERE a.mandt  = '` sy-mandt `' `
  `  AND b.mandt  = '` sy-mandt `' `
  `  AND c.mandt  = '` sy-mandt `' `
  `  AND d.mandt  = '` sy-mandt `' `
  `  AND a.massn = 'C1' `
  `  AND a.begda >= '20110101' `
  `  AND a.begda < '20120101' `
  `  AND b.orgeh > 0 `
  `ORDER BY b.orgeh, a.begda `
  `) GROUP BY orgtx, mgtxt `
  `) ` "базовый запрос

  `SELECT orgtx, ` "Подразделение
  `       SUM(count_sobstv)  AS count_sobstv, ` "Собственное желание
  `       SUM(count_pension) AS count_pension ` "Уход на пенсию
  `FROM ( `

  `SELECT DISTINCT orgtx, 0 AS count_sobstv, 0 AS count_pension `
  `FROM query1 ` "все имеющиеся уникальные подразделения

  `UNION ALL `
  `SELECT orgtx, SUM(cnt) AS count_sobstv, 0 AS count_pension `
  `FROM query1 `
  `WHERE mgtxt = 'Собственное желание' `
  `GROUP BY orgtx `

  `UNION ALL `
  `SELECT orgtx, 0 AS count_sobstv, SUM(cnt) AS count_pension `
  `FROM query1 `
  `WHERE mgtxt = 'Уход на пенсию' `
  `GROUP BY orgtx `

  `UNION ALL ` "чисто тестовый фрагмент для демонстрации работы запроса
  `SELECT 'ФИКТИВНЫЙ ОТДЕЛ' AS orgtx, 1 AS count_sobstv, 1 AS count_pension `
  `FROM dual UNION ALL `
  `SELECT 'ФИКТИВНЫЙ ОТДЕЛ' AS orgtx, 3 AS count_sobstv, 4 AS count_pension `
  `FROM dual UNION ALL `
  `SELECT 'ФИКТИВНЫЙ ОТДЕЛ' AS orgtx, 5 AS count_sobstv, 2 AS count_pension `
  `FROM dual ` "потом удалить эти 7 строк кода

  `) GROUP BY orgtx `
  INTO l_stmt. "формируем текст запроса Native SQL

  GET REFERENCE OF itab INTO tab_pointer.
  CREATE DATA wa_pointer LIKE LINE OF itab.

  ASSIGN wa_pointer->*  TO <wa_data>.
  ASSIGN tab_pointer->*  TO <tab_data>.

  CREATE OBJECT l_stmt_ref.
  l_res_ref = l_stmt_ref->execute_query( l_stmt ).
  l_res_ref->set_param_table( tab_pointer ).
  l_res_ref->next_package( ). "здесь получаем результат
  l_res_ref->close( ).

  "перебор строк результата
  LOOP AT <tab_data> INTO <wa_data>.
    ASSIGN COMPONENT 1 OF STRUCTURE <wa_data> TO <fs_org>.
    ASSIGN COMPONENT 2 OF STRUCTURE <wa_data> TO <fs_sob>.
    ASSIGN COMPONENT 3 OF STRUCTURE <wa_data> TO <fs_pen>.
    WRITE: / sy-tabix, <fs_org>, <fs_sob>, <fs_pen>.
  ENDLOOP.

  FREE: l_res_ref, l_stmt_ref.

Проверялось на SAP ECC 6.0 с Oracle 10.2. Поскольку некоторые таблицы, вовлеченные в запрос у меня пустые, то для того, чтобы убедиться в его работоспособности, я добавил три строки с фиктивным отделом, которые в самом внешнем запросе складываются. В продуктивной версии их нужно будет удалить.

Небольшое неудобство - в отличие от Open SQL нужно помнить про мандант и включать его в условие WHERE. Грандиозное преимущество - наглядность и относительная простота как в разработке запроса, так и в его обслуживании.

billion

to LAT & Gustav:

Благодарю! Код рабочий. пока его прикручивал Gustav предложил лучший для меня вариант. раньше я с БД работал и в АБАП многих вещей не понимаю. А тут родной SQL

to Gustav:

код рабочий. благодарю! но при запуске выдаёт одну строку с фиктивным подразделением 1. если закомментировать 7 строк то вообще ничего не выдаёт.... разбираюсь....

billion

to Gustav:

разбираюсь.... а вот интересно! курсоры и цыклы тоже можно крутить в таком синтаксисе Open SQL?
дело в том что хочу динамически всё закрутить. чтоб при добавлении или удалении причины из справочника у меня ничего не рушилось

zsap

Native SQL в сапе это большое зло, я бы не советовал им пользоваться. И уж точно не стоит этого делать для решения такой элементарной задачки, где одного select-а с group by на Open SQL вполне достаточно

billion

to zsap:

а в чём именно зло заключается?

zsap

billion написал(а):

to zsap:

а в чём именно зло заключается?

Плохая переносимость, синтаксис проверяется только во время выполнения - сложно сопровождать, в журнал использования ничего не пишется, аналогично ничего не пишется и в журналы производительности, много лишних телодвижений и кода с неочевидным смыслом типа этого

Code:

ASSIGN COMPONENT 1 OF STRUCTURE <wa_data> TO <fs_org>.

С учетом всего этого даже индусы пользуют native sql крайне редко ))

billion, если ввязались в абап, советую для начала ознакомится с курсами BC4XX. На самом деле тут все намного проще, даже никаких курсоров не нужно ))

Gustav

zsap написал:

Native SQL в сапе это большое зло, я бы не советовал им пользоваться.

Я, наверное, не очень правильно выразился, сказав "Native SQL". Правильнее это называть "API ADBC" (ABAP Database Connectivity).
Вот здесь есть хорошая статья: http://sapland.ru/articles/spj/2007/5/v ... -adbc.html
Цитата оттуда:

Цитата:

ADBC был разработан как дополнение к функциональным возможностям ABAP Native SQL, реализованным в SQL-команде EXEC. SQL-оператор EXEC позволяет разработчикам выполнять команды SQL, ограниченные одной базой данных и не представленные в функциональности Open SQL. Они могут обращаться к таблицам БД, которые не зависят от ABAPсловаря, что дает возможность адресации данных, созданных вне SAP NetWeaver Application Server (AS). Однако в некоторых случаях недостатки и принципиальные недоработки в SQL-операторе EXEC ограничивают сферу его применения:

* В SQL-операторе EXEC поддерживаются только статические SQL-операторы. Это означает, что он не может использоваться для решения определенного класса задач, требующих средств динамического SQL, таких как родовые запросы. Конечно, в таких ситуациях всегда можно прибегнуть к динамическому созданию и исполнению ABAP-программы, однако это достаточно сложно с точки зрения программирования и неэффективно с точки зрения исполнения.

* Недостаточно гибкая обработка ошибок. Если по какой-либо причине выполнение SQL-оператора завершается неуспешно, текущая прикладная транзакция в исполняющей системе ABAP прерывается, в результате чего программа возвращает ошибку. Однако в некоторых случаях после возникновения ошибки SQL можно передать дальнейшее управление процессом прикладной программе. Эта программа, например, может анализировать возвращенный код SQL и предпринимать различные действия в зависимости от результатов анализа.

* Наличие множественных подключений к БД чревато ошибками и требует жесткой программистской дисциплины. Причина состоит в том, что SQL-операторы выполняются в контексте глобального подключения, а не отдельных объектов подключения, которые могут передаваться в качестве параметров между модулями программы. Для этой ситуации характерны те же виды ошибок и неприятных неожиданностей, которые возникают при использовании глобальных переменных программы.

Поскольку однозначно верного способа устранения этих недостатков в рамках существующей концепции SQL-команд EXEC не существует, был разработан API ADBC – новый инструмент для разработчиков, в котором реализован более гибкий подход к выполнению операторов Native SQL. ADBC представляет собой объектно-ориентированный API уровня вызовов, который позволяет преодолеть ограничения, свойственные EXEC SQL. Этот API обеспечивает доступ ко всем функциональным возможностям SQL связанной системы базы данных. Все SQL-операторы могут создаваться динамически и исполняться с использованием соответствующих методов API. Кроме того, в API ADBC реализована четкая концепция работы с множественными подключе ниями к базе данных. Каждое открытое соединение с базой данных связано с объектом подключения, который затем может применяться для создания и выполнения SQL-операторов через это соединение. Наконец, при возникновении ошибок все методы API ADBC выдают четкие сообщения об особых ситуациях, которые могут быть получены и обработаны вызывающей программой. При этом разработчик может в полном объеме управлять поведением программы при возникновении тех или иных ошибок.

zsap написал:

И уж точно не стоит этого делать для решения такой элементарной задачки, где одного select-а с group by на Open SQL вполне достаточно

Я бы не сказал, что она такая уж абсолютно элементарная. Ведь речь идет о перекрестном запросе типа сводной таблицы Excel, с превращением "строчек в столбцы". Разве можно это сделать на Open SQL? Он допускает написание конструкций типа "SELECT FROM ( SELECT FROM (...)...)" ? А с ADBC - ни одного цикла, ни одной утомительной перекладки - сразу имеем внутреннюю таблицу, которую можно тут же слить в Excel.

zsap

Gustav написал:

Я, наверное, не очень правильно выразился, сказав "Native SQL". Правильнее это называть "API ADBC" (ABAP Database Connectivity).

Это не принципиально )) Все что я писал о native sql в полной пере относится и к "API ADBC".

Gustav написал:

zsap написал:

И уж точно не стоит этого делать для решения такой элементарной задачки, где одного select-а с group by на Open SQL вполне достаточно

Я бы не сказал, что она такая уж абсолютно элементарная. Ведь речь идет о перекрестном запросе типа сводной таблицы Excel, с превращением "строчек в столбцы". Разве можно это сделать на Open SQL? Он допускает написание конструкций типа "SELECT FROM ( SELECT FROM (...)...)" ? А с ADBC - ни одного цикла, ни одной утомительной перекладки - сразу имеем внутреннюю таблицу, которую можно тут же слить в Excel.

Забить в код запроса ТЕКСТОВЫЕ значения справочника это конечно гениальное решение проблемы, не каждый додумается )
select-ом выбирается плоская таблица, потом в зависимости от метода выгрузки в Excel можно в абапе ее переложить в сводную, либо в самом Excel-е

Gustav

zsap написал:

много лишних телодвижений и кода с неочевидным смыслом типа этого

Code:

ASSIGN COMPONENT 1 OF STRUCTURE <wa_data> TO <fs_org>.

Что имеется в виду под "лишними телодвижениями"? Пяток строк для работы с классами CL_SQL_STATEMENT и CL_SQL_RESULT_SET ? Я бы сказал, что при обычных перекладках между внутренними таблицами телодвижений заметно больше. А приведенная строка кода, как можно заметить, не имеет отношения ни к Native, ни к ADBC. В рамках примера она просто часть цикла "на скорую руку" по выводу чего-то на экран, чтобы было понятно, что что-то происходит.

Так что, "софистика, пастор, софистика" (с) Штирлиц

zsap написал:

Забить в код запроса ТЕКСТОВЫЕ значения справочника это конечно гениальное решение проблемы, не каждый додумается )

Ну, это скорее к топикстартеру вопрос. Я лишь переложил его исходный запрос Open SQL на Native и обернул еще несколькими SELECT'ами, показав как это в принципе можно оформить. И понятно, что все константы можно в CONCATENATE заменить переменными... Ну, давайте, покритикуйте еще, что колонка count_sobstv называется так, а не как-нибудь cnt_own_wish или еще как...

zsap

Gustav написал:

zsap написал:

Забить в код запроса ТЕКСТОВЫЕ значения справочника это конечно гениальное решение проблемы, не каждый додумается )

Ну, это скорее к топикстартеру вопрос. Я лишь переложил его исходный запрос Open SQL на Native и обернул еще несколькими SELECT'ами, показав как это в принципе можно оформить. И понятно, что все константы можно в CONCATENATE заменить переменными... Ну, давайте, покритикуйте еще, что колонка count_sobstv называется так, а не как-нибудь cnt_own_wish или еще как...

Изначально никаких текстовых констант в запросе не было. Предложенное вами решение не формирует сводную таблицу в общем случае, а считает количество по нескольким заранее заданным признакам. Это как бы более узкая задача, которая в abap+open sql решается проще.

Кстати, Excel и сам способен из плоской таблицы сделать сводную, если оформление не слишком сложное

Gustav

zsap написал:

Изначально никаких текстовых констант в запросе не было. Предложенное вами решение не формирует сводную таблицу в общем случае, а считает количество по нескольким заранее заданным признакам. Это как бы более узкая задача, которая в abap+open sql решается проще.

Наконец, уразумел. Вы (все) хотели универсального решения, а я буквально думал только про два частных случая, приведенных как пример в первом сообщении. "Болван... Согласен!" (с) Тогда конечно - проще поручить эту задачку Excel'ю и сводной таблице, ибо в SAP'е придется динамически формировать как фрагменты текста запроса, так и динамическую внутреннюю таблицу для приема результатов. Впрочем и то, и другое - не так уж и сложно (по динамической таблице я находил примеры на форуме и уже воплощал их в своих разработках).

to billion:
Если будет желание все-таки повозиться в SAP'e, то я придал запросу вид, более наглядный для целей динамического формирования. Попутно упростил его, хоть оракловый SQL немного вспомнил:

Code:

  CONCATENATE

  `WITH query1 AS ( `
  `SELECT orgtx, mgtxt, COUNT(*) AS cnt `
  `FROM ( `
  `SELECT a.begda, a.massg, b.orgeh, c.mgtxt, d.orgtx `
  `FROM pa0000 a `
  `JOIN pa0001 b ON a.pernr = b.pernr AND b.endda = '99991231' `
  `JOIN t530t  c ON c.massg = a.massg AND c.massn = 'C1' AND c.sprsl = 'RU' `
  `JOIN t527x  d ON d.orgeh = b.orgeh AND d.endda = '99991231' AND d.sprsl = 'RU' `
  `WHERE a.mandt  = '` sy-mandt `' `
  `  AND b.mandt  = '` sy-mandt `' `
  `  AND c.mandt  = '` sy-mandt `' `
  `  AND d.mandt  = '` sy-mandt `' `
  `  AND a.massn = 'C1' `
  `  AND a.begda >= '20110101' `
  `  AND a.begda < '20120101' `
  `  AND b.orgeh > 0 `
  `ORDER BY b.orgeh, a.begda `
  `) GROUP BY orgtx, mgtxt `

  `UNION ALL ` "чисто тестовый фрагмент для демонстрации работы запроса
  `SELECT 'ФИКТИВНЫЙ ОТДЕЛ' AS orgtx, 'Собственное желание' AS mgtxt, 9 AS cnt `
  `FROM dual UNION ALL `
  `SELECT 'ФИКТИВНЫЙ ОТДЕЛ' AS orgtx, 'Уход на пенсию' AS mgtxt, 10 AS cnt `
  `FROM dual UNION ALL `
  `SELECT 'ФИКТИВНЫЙ ОТДЕЛ' AS orgtx, 'Уход на пенсию' AS mgtxt, 7 AS cnt `
  `FROM dual ` "потом удалить эти 7 строк кода

  `) ` "базовый запрос

  `SELECT `   "вот тут теперь фишка!
  `  orgtx, ` "Подразделение
  `  SUM(CASE WHEN mgtxt = 'Собственное желание' THEN cnt ELSE 0 END) cnt_1, `
  `  SUM(CASE WHEN mgtxt = 'Уход на пенсию'      THEN cnt ELSE 0 END) cnt_2  ` 
  `FROM query1 `
  `GROUP BY orgtx `

  INTO l_stmt. "формируем текст запроса Oracle

Соответственно, понятно, что строк "SUM(CASE..." можно нагенерить по числу элементов справочника причин. Причем, названия полей могут не совпадать с названиями полей типа (pp0001), но обязательно должны совпадать по общему количеству, последовательности и типу (числа нормально принимаются и текстовыми полями).

zsap написал:

Кстати, Excel и сам способен из плоской таблицы сделать сводную, если оформление не слишком сложное

Ну, это я в курсе, даже писывал на эту тему, правда, в другой "степи": http://www.axforum.info/forums/blog.php?b=60 , http://www.axforum.info/forums/blog.php?b=120

Правила форума

Двойная группировка в АБАП

Кто сейчас на конференции