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

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

8051core

Согласен с zsap, что Native SQL, тем более для таких задач, зло.
В стандарте SAPа я не припомню программ, где данные из инфотипов выбираются с помощью select-ов.
Все делается через LBD или функции *READ_INFOTYPE.

Зачем простую задачу, подобных которой возникают десятки в HR модуле, решать с помощью костылей на Native SQL.

Запрос выборки данных из инфотипов и последующая выборка текстов это две разные задачи.
Разбейте задачу на куски.
Зачем пытаться запихать все выборки в один magic query

, который выберет вам все данные.

billion

сначала обрадовался SQL, а потом долго ругался.... любая мелкая ошибка валит сессию САПА. решил использовать вариант от LATа:

Code:

SORT wa0009 BY massg.
LOOP AT wa0009 REFERENCE INTO r_pp0001.
  IF x_massg-massg <> r_pp0001->massg.
    x_massg-massg = r_pp0001->massg.
    x_massg-mgtxt = r_pp0001->mgtxt.
    APPEND x_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.
    add 1 to r_massg->count.
  ENDIF.
ENDLOOP.

вывел всё в эксель и красиво оформил.
одна проблема:
в таблице wa0009 три записи с причиной (massg=05):

а вот r_massg->count показывает "1" всегда.

короче всё работает. все причины учитываются, но только один раз. у меня итоговая таблица вышла только с 0 и 1.
ох и трудный этот САП.... всё исковырял... не могу найти причину.

LAT

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

одна проблема:
в таблице wa0009 три записи с причиной (massg=05):

а вот r_massg->count показывает "1" всегда.

Я таки чуток набокопорил

. Но это радует: если свеженаписанная программа при первом же запуске работает, как следует, это не к добру...
Попробуйте заменить:

Code:

  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.
    add 1 to r_massg->count.
  ENDIF.

на

Code:

  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.
  ENDIF.
  r_massg->count = r_massg->count + 1.

Примечание. add 1 to r_massg->count. - то же самое, что r_massg->count = r_massg->count + 1.

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

.... всё исковырял... не могу найти причину.

??? Ошибка очень простая.

billion

ВО! заработало! я именно с этими строками и игрался. поэтому и на форум вставил код с двойной инкрементацией

повставлял счётчики, поставил точки остановки, запустил дебаггер, код c IF комментировал по всякому, но именно инкремент вынести за IF не догадался. Спасибо! заработало! Задачка простая для тех кто в САПе хоть чуть-чуть шарит, а я в него третий день тока как залез

на MS SQL я бы эту задачку за 10 минут не спеша бы нарисовал, а тут ни "чихнуть" ни "пукнуть" низя

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

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

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