вычисления внутри SELECT

[at0mix]

Привет.
Проштудировал руководство на firebird SQL (Руководство по языку SQL СУБД Firebird 3.0.8) и на BASE 7.3

В доке на 7.3 есть пример
SELECT "Checkout"."Receipt_ID", "Checkout"."Total", "Stock"."Item", "Stock"."Unit_Price", "Checkout"."Total"*"Stock"."Unit_price" AS "Total_Price"
FROM "Checkout", "Stock" WHERE "Stock"."ID" = "Checkout"."Item_ID";

т.е. можно в селекте написать операцию

то же самое есть в описании SQL для firebird

я сделал выборку:
SELECT "Цех", COUNT( CASE WHEN "линукс" = 1 THEN 1 ELSE NULL END ) "linuxFACT", ( SELECT "GR"."32" FROM "График" "GR" WHERE "GR"."Цех" = "PC"."Цех" ) "linuxPLAN" FROM "Список ПК" "PC" GROUP BY "PC"."Цех"

работает.
НО! если добавить вычисляемое поле
SELECT "Цех", COUNT( CASE WHEN "линукс" = 1 THEN 1 ELSE NULL END ) "linuxFACT", ( SELECT "GR"."32" FROM "График" "GR" WHERE "GR"."Цех" = "PC"."Цех" ) "linuxPLAN", "linuxFACT" - "linuxPLAN" "delta" FROM "Список ПК" "PC" GROUP BY "PC"."Цех"
- база выдает ошибку на вычисляемое ранее поле linuxFACT

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -206
*Column unknown
*linuxFACT
*At line 1, column 185
caused by
'isc_dsql_prepare'
/builds/AstraOS/buildsystem/astra-build-fixes/ci__libreoffice/libreoffice-7.3.2/connectivity/source/drivers/firebird/Util.cxx:68

как обойти проблему? выгребать это во временную таблицу?

[kompilainenn]

Base крайне нестабильная штука, а Firebird в нем ещё более нестабильная.
Я бы баг репорт написал про это. Шанс, что исправят - минимальный, никто этой частью ЛО не занимается

[rami]

как обойти проблему? выгребать это во временную таблицу?

Запрос берёт данные из таблицы и создаёт новые поля в запросе, которых нет в исходной таблице. Правильно ругается: Column unknown linuxFACT — нет такого столбца в таблице.

Так:

Код Выделить Развернуть

SELECT "Цех", COUNT( CASE WHEN "линукс" = 1 THEN 1 ELSE NULL END ) "linuxFACT", ( SELECT "GR"."32" FROM "График" "GR" WHERE "GR"."Цех" = "PC"."Цех" ) "linuxPLAN", COUNT( CASE WHEN "линукс" = 1 THEN 1 ELSE NULL END ) - ( SELECT "GR"."32" FROM "График" "GR" WHERE "GR"."Цех" = "PC"."Цех" ) "delta" FROM "Список ПК" "PC" GROUP BY "PC"."Цех"

[at0mix]

Запрос берёт данные из таблицы и создаёт новые поля в запросе, которых нет в исходной таблице. Правильно ругается: Column unknown linuxFACT — нет такого столбца в таблице.

уже понял что после AS это просто заголовок для вывода на экран - потому что при двойном вычислении все работает %(

SELECT "Цех", COUNT( CASE WHEN "PC"."линукс" = 1 THEN 1 ELSE NULL END ) "linuxFACT", ( SELECT "GR"."32" FROM "График" "GR" WHERE "GR"."Цех" = "PC"."Цех" ) "linuxPLAN", COUNT( CASE WHEN "PC"."линукс" = 1 THEN 1 ELSE NULL END ) - ( SELECT "GR"."32" FROM "График" "GR" WHERE "GR"."Цех" = "PC"."Цех" ) FROM "Список ПК" "PC" GROUP BY "PC"."Цех"

вопрос - как избежать этого? база понимает WITH?
похоже что нет - и JOIN тоже %((((
или делать временную таблицу и туда писать результат селекта а потом обрабатывать?
просто по последнему вычисленному столбцу мне нужно выбрать отрицательные результаты и выпнуть их в отчет

[rami]

Пробуйте такой запрос:

Код Выделить Развернуть

WITH "fact" AS (
  SELECT "Цех", COUNT(IIF("линукс" = 1, 1, NULL)) "linuxFACT" FROM "Список ПК" GROUP BY "Цех"
), "plan" AS (
  SELECT DISTINCT "PC"."Цех", "GR"."32" "linuxPLAN" FROM "График" "GR" LEFT JOIN "Список ПК" "PC" ON "GR"."Цех" = "PC"."Цех"
)
SELECT "fact"."Цех", "linuxFACT", "linuxPLAN", "linuxFACT" - "linuxPLAN" "delta"
FROM "fact" JOIN "plan" ON "fact"."Цех" = "plan"."Цех"

[at0mix]

Пробуйте такой запрос:
Код:

WITH "fact" AS (
  SELECT "Цех", COUNT(IIF("линукс" = 1, 1, NULL)) "linuxFACT" FROM "Список ПК" GROUP BY "Цех"
), "plan" AS (
  SELECT DISTINCT "PC"."Цех", "GR"."32" "linuxPLAN" FROM "График" "GR" LEFT JOIN "Список ПК" "PC" ON "GR"."Цех" = "PC"."Цех"
)
SELECT "fact"."Цех", "linuxFACT", "linuxPLAN", "linuxFACT" - "linuxPLAN" "delta"
FROM "fact" JOIN "plan" ON "fact"."Цех" = "plan"."Цех"

Состояние SQL: HY000
Код ошибки: 1000
syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

Причем если запускать ЭТУ команду через "Сервис"-SQL - все отрабатывает на отлично!
а если делать запрос в базе через редактирование запроса - выдает ошибку.....

как я понимаю это встроенный контроль в базе синтаксиса, и этот контроль нихрена не знает про многие опции sql - включая with

вопрос - а есть возможность отключить этот контроль? или имеет смысл копать в направлении встроенных процедур SQL?

потому что база не пропускает и переменные в именах полей селекта, и многие другие вещи.....

[rami]

Нажмите кнопку "Выполнить команду SQL непосредственно". Запрос будет работать только с нажатой кнопкой:

[economist]

это встроенный контроль в базе синтаксиса, и этот контроль нихрена не знает про многие опции sql - включая with

Зато этот контроль (парсер) умеет выполнять параметрические запросы, задающие вопрос пользователю. Так что кнопочку - да, стоит раз нажать при создании/сохранении всех запросов, но в некоторых придется отжать. Для макросов тоже есть соответствующая кнопочке команда:

Код Выделить Развернуть


RowSet = createUnoService("com.sun.star.sdb.RowSet") ' создаем рекордсет, основной/быстрейший способ чтения/правки данных в RBDMS
RowSet.EscapeProcessing=FALSE ' аналог нажатой кнопки [SQL]
RowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
RowSet.Command = "SELECT ..."
RowSet.execute()


[at0mix]

Нажмите кнопку "Выполнить команду SQL непосредственно". Запрос будет работать только с нажатой кнопкой:

Блин, издержки незнания английского языка %( сейчас проверил - да в доке написано про фишку.
Спасибо за помощь! буду проверять на предмет переменной в поле столбцов в селекте %)

Зато этот контроль (парсер) умеет выполнять параметрические запросы, задающие вопрос пользователю. Так что кнопочку - да, стоит раз нажать при создании/сохранении всех запросов, но в некоторых придется отжать. Для макросов тоже есть соответствующая кнопочке команда:

Да спасибо - я видел запросы, и даже матюкался когда хотел чтобы запрос брал данные из переменной в форме а не спрашивал меня.
сейчас поняв фокус с кнопкой буду проверять еще раз.

Еще раз спасибо за грамотные советы!

[at0mix]

Остался еще один вопрос - как выбрать нужный столбец?
в мурзилке на sql написано что можно ставить переменную вместо текста с именем столбца - но у меня в базе не получилось - ругается.
есть еще какие-то варианты?
может в макросе можно скажем считать столбец и записать его в другую базу а потом из второй базы уже брать по имени?

[economist]

Если SQL ругается на макрос - скорее всего ошибка в кавычках. Пробуйте:  
sql = "SELECT ""Поле"" FROM TABLE"
sql = "SELECT 'Поле' FROM TABLE"
sql = "SELECT Поле FROM TABLE"

Вот почему крайне нежелательны пробелы в именах полей (и таблиц) - с ними третий, самый простой вариант, не прокатит.

Если же выбирать столбец не в запросе, а в его результате (объекте RowSet) - то тут нужен дополнительный цикл для перебора всех его строк. Нужный столбец можно получить по его индексу. Считать можно как число через getValue, так и текст, через getString. Примеры есть на Форуме, искать по "rowset".

[at0mix]

Если SQL ругается на макрос - скорее всего ошибка в кавычках. Пробуйте: 
sql = "SELECT ""Поле"" FROM TABLE"

Нет, дело не в этом. Реализация в ЛО-базе sql-запросов судя по всему урезана и сильно.
т.е. БАЗА не понимает в принципе что имя переменной может хранить в себе имя поля!
конструкция типа
SELECT "Цех", "33" FROM "График"
отрабатывает ПРАВИЛЬНО - "33" - это имя поля
а вот конструкцию
SELECT "Цех", :param FROM "График"
переменную :param не воспринимает как имя поля - а как константу которую и прописывает в ответе

Более того - БАЗА не умеет работать с виртуальными таблицами CTE!
т.е. конструкция написанная rami  с отключенным парсером работает - и если запустить запрос - в окне будет выведен правильный ответ.
но в форме использовать временную таблицу fact нельзя %(

или я чего то не понимаю, или нужно как то макросом копировать полученные столбцы в физическую (не виртуальную) таблицу, или пробовать сделать представление (view)

[economist]

Форма может быть основана на  хранимом в базе Select-запросе или View с на/отжатой кн. SQL. Он должен работать с CTE

[sokol92]

т.е. БАЗА не понимает в принципе что имя переменной может хранить в себе имя поля!

В параметризованных (подготовленных) запросах параметр запроса не может указывать на имя таблицы или имя поля. В противном случае запрос не скомпилируешь.

О работе с параметризованными запросами в LO см. также здесь.