Сложные SQL-строки и гибкость программ

Обычная рабочая ситуация  – приходит человек и говорит, что ему нужен примерно такой отчёт как есть но чтобы колонки таблицы были переставлены а просуммированы итоги по некому параметру. Ему надо такое. Возможно, ему это потребуется два раза в жизни, но сделать надо именно такое. Эти отчёты клепаются не приходя в сознание. Через месяц уже никто не может вспомнить было такое или нет, а если было, то к какому месту в программе оно прицеплено. Ими захламляется база, их теряют потом неожиданно находят, под настроение поинтересовавшись, что это за бумажка с названием «Выборка товаров за период для Васи».
Это ситуация, от которой плохо всем. Пользователь вынужден ждать, когда ему нарисуют желаемое. Программист занимается тупой дурью, переставляя запятые в нескольких местах.
Естественно от этого хотелось хоть как-то избавиться, дав возможность пользователю что-то выбирать и печатать самостоятельно, без программиста.
Решение, позволяющее хоть как-то всем упростить жизнь это дать пользователю возможность самому генерировать SQL-строки, возвращающие нужную информацию в нужной последовательности.
SQL строка  выгладит так:
http://akostina76.ucoz.ru/blog/2018-07-13-5221

SELECT таблица1.поле1, таблица2.поле2,….
FROM как-то соединённые таблицы
WHERE таблицаN.полеN=значениеN AND таблицаM.полеM=значениеM
GROUP BY таблица1.поле1, таблица2.поле2…
ORDER BY таблицаX.полеX, таблицаY.полеY
Эту строку надо сгенерировать по тому что пользователь нащёлкал мышью на экране и передать SQL-серверу. Сервер вернёт всю это информацию.
Сгенерировать такую строку отчасти легко. Вот стандартная форма для выборки платёжных поручений:

Всё то, что пользователь навыбирал на вкладке «Настройки» надо добавить в строку после слов SELECT и GROUP BY (только суммы надо суммировать, потому SUM). Это списки нужных полей, которые должны быть возвращены в указанной пользователем последовательности. Только человеческое название «Документ» надо заменить на машинное DOC.CDOC (выбор из таблицы DOC поля CDOC).
Выбранное на вкладке «Фильтр» поместиться в SQL-строке после слова WHERE. Вот это, например:

…станет примерно таким:
WHERE Isdate(DOC.D_RETURN) = 0)

AND DOC.DU_O>01.08.2017 AND DOC.DU_O<=03.11.2017                  K_UL_K_UL.K_ULID_PARENT_INN=7806042263
Примерно таким, там сложнее, но пусть всё так просто.
В SQL-cтроке осталось только то, что всё-таки придётся написать программисту. Это набор таблиц базы данных и то, как они связаны между собой, т.е то, что в строке после слова FROM. SQL- строка готова, можно передавать серверу. Это уже упрощает жизнь, потому что всё, что можно выбрать из документа конкретного типа пользователь теперь может выбрать сам.
Самое гибкое, что я видела это настройка СКД-шного отчёта 1С:

Тут есть и стандартный набор для SQL-строки (списки для SELECT, WHERE, GROUP, ORDER) и возможность красить (или как-то иначе оформлять) строки при выполнении какого-то условия (вкладка «Условное оформление») и очень много ещё чего, что позволяет выдать пользователю таблицу в которой миллион записей и сто колонок и пусть он сам себе выбирает оттуда всё, что хочет.
Это всё без всякой иронии прекрасно. Все же помнят времена когда этого не было. Но и эта гибкость ограничена, потому что условие FROM выдающее те самые миллион строк и сто колонок должен написать программист и в клиентском приложении будет только то, что он написал. Обычно это отдельные выборки по платёжкам, планам и прочим документам, в которых к основной таблице с информацией прицеплены какие-то поясняющие справочники.
Напрямую SQL-строкой для SQL –сервера я могу себе навыбирать что угодно, потому что там гибкость SQL-строки, а не программного генератора этой строки по тому что навыбирал пользователь в упрощённом (для него) диалоге.
Чтобы выбрать сложное надо знать структуру базы, а пользователю хочется дать чуть больше возможностей и гибкости, избавив его от этих знаний.
А теперь пусть мне надо объединить планы с фактом платёжек. Чтобы это получить мне либо надо чтобы как и раньше мне это выбрали и выдали на экран. Или я могу сама это всё вытащить SQL-строкой.
М-дя… Интернет всё стерпит. Вначале не адоптированный кусок выборки, читать в котором надо только то, что подчёркнуто синим:



И Т Д И Т П

В середине пропущены выборки из прочих таблиц. В этой SQL-строке объединяется информация из планов (CSUB_CUL_PFEA) и платёжек (ZF_R) с помощью выражения UNION ALL. А потом всё это разворачивается в строки и колонки (PIVOT) чтобы получилось так:

Я не видела экранных конструкторов, которые позволяли бы что-то так собирать из нескольких документов, а конструктором SQL-строки я это делаю относительно легко. То, что такой возможности нет в обычной программе… а где она есть? У меня она есть, но я же для себя пишу потому она у меня и есть))).
При сборке информации возникают некоторые сложности. В планах такие коды:

А в фактах такие:

Причём понятно, что 1100 это СГЭ, в ПД размазалось на всё, что больше 1500. Я не знаю, как это в стандартным клиенте объединится (если вообще объединяется), мне пришлось воткнуть в текст перекодировщик:

… иначе у меня информация расползалась по разным, совершенно независимым строкам, а я хочу это всё вместе собрать. И могу это сделать используя гибкость SQL-строки.
Так что то, что программа что-то не делает это не потому что она плохая а потому что она стандартная и дающая те возможности и ту гибкость которую полагается давать программам в данный момент.
Чтобы получить больше надо больше уметь и знать. Или уметь писать программистам технические задания по которым они напишут то, что будет выбирать заказанное, но гибким оно не будет. Будет конкретный «отчёт по заказу конкретного Васи». Раз этого нет,  значит не заказывали. Скорее всего, руками собирают по выборкам в Excel.

Дело ещё и в том, что я не знаю, что мне придёт в голову выбрать через пять минут. Захочу и выберу тот самый единственный раз как вчерашняя выборка:
http://akostina76.ucoz.ru/blog/2018-07-19-5229

Это SQL –язык такой хороший)). Он позволяет это делать если это вдруг надо. Программ дающих его гибкость нет… хотя бы потому что не надо. Или не очень надо. Тем, кто знает устройство базы (в каких таблицах что лежит) и SQL-евский конструктор сгодится, а сооружать что-то похожее для пользователя трудоёмко и пока непонятно зачем.