Сложный запрос с вложенными запросами для Access (ADO)

← →
Макс (2004-12-20 23:20) [0]

Добрый день.
Возникла проблема с запуском запроса к БД.
Имеем БД с таблицами indicators, indicator_values.
Задача:
Получить список, сгруппированный по perspective с соответствующими avg(first(indicator_value)/target_value) для indicator"ов с type="возрастающий", объединенный со списком perspective с соответствующими avg(first(indicator_value)/target_value) для indicator"ов с type="убывающий". Проще говоря, процент достижения целевых значений показателей перспектив с учетом их типа.

Поля indicators: perspective,target,indicator,target_value,type;

Поля indicator_values: indicator,current_data,indicator_value;

Запрос1:
SELECT [indicators].[perspective], First([indicator_values].[current_data]) AS [FirstOfcurrent_data], [indicator_values].[indicator], First([indicator_values].[indicator_value]) AS [FirstOfindicator_value], [indicators].[target_value]
FROM [indicator_values] INNER JOIN [indicators] ON [indicator_values].[indicator] = [indicators].[indicator]
GROUP BY [indicators].[perspective], [indicator_values].[indicator], [indicators].[target_value]
ORDER BY First([indicator_values].[current_data]), First([indicator_values].[indicator_value]);

Выдает перспективу, наименование показателя, целевое значение показателя, дату последней записи показателя, последнее значение показателя.

Запрос 2:

(SELECT [indicator],(([FirstOfindicator_value]/[target_value])*100) FROM (SELECT [indicators].[perspective], First([indicator_values].[current_data]) AS [FirstOfcurrent_data], [indicator_values].[indicator], First([indicator_values].[indicator_value]) AS [FirstOfindicator_value], [indicators].[target_value]
FROM [indicator_values] INNER JOIN [indicators] ON [indicator_values].[indicator] = [indicators].[indicator]
GROUP BY [indicators].[perspective], [indicator_values].[indicator], [indicators].[target_value], [indicators].[type]
HAVING (([indicators].[type]) = "Возрастающий")
ORDER BY First([indicator_values].[current_data]), First([indicator_values].[indicator_value])))
UNION
(SELECT [indicator],(([target_value]/[FirstOfindicator_value])*100) FROM (SELECT [indicators].[perspective], First([indicator_values].[current_data]) AS [FirstOfcurrent_data], [indicator_values].[indicator], First([indicator_values].[indicator_value]) AS [FirstOfindicator_value], [indicators].[target_value]
FROM [indicator_values] INNER JOIN [indicators] ON [indicator_values].[indicator] = [indicators].[indicator]
GROUP BY [indicators].[perspective], [indicator_values].[indicator], [indicators].[target_value], [indicators].[type]
HAVING (([indicators].[type]) = "Убывающий")
ORDER BY First([indicator_values].[current_data]), First([indicator_values].[indicator_value])));

Выдает процент достижения целевого значения для каждого показателя.

Запрос 3:

(SELECT [%$##@_Alias].[perspective], Avg((([FirstOfindicator_value]/[target_value])*100)) AS Completed
FROM
(
SELECT [indicators].[perspective], First([indicator_values].[current_data]) AS [FirstOfcurrent_data], [indicator_values].[indicator], First([indicator_values].[indicator_value]) AS [FirstOfindicator_value], [indicators].[target_value]
FROM [indicator_values] INNER JOIN [indicators] ON [indicator_values].[indicator] = [indicators].[indicator]
GROUP BY [indicators].[perspective], [indicator_values].[indicator], [indicators].[target_value], [indicators].[type]
HAVING (([indicators].[type]) = "Возрастающий")
ORDER BY First([indicator_values].[current_data]), First([indicator_values].[indicator_value])
)
GROUP BY [%$##@_Alias].[perspective])
UNION
(SELECT [%$##@_Alias].[perspective], Avg((([target_value]/[FirstOfindicator_value])*100)) AS Completed
FROM
(
SELECT [indicators].[perspective], First([indicator_values].[current_data]) AS [FirstOfcurrent_data], [indicator_values].[indicator], First([indicator_values].[indicator_value]) AS [FirstOfindicator_value], [indicators].[target_value]
FROM [indicator_values] INNER JOIN [indicators] ON [indicator_values].[indicator] = [indicators].[indicator]
GROUP BY [indicators].[perspective], [indicator_values].[indicator], [indicators].[target_value], [indicators].[type]
HAVING (([indicators].[type]) = "Убывающий")
ORDER BY First([indicator_values].[current_data]), First([indicator_values].[indicator_value])
)
GROUP BY [%$##@_Alias].[perspective])

Выдает список перспектив с искомыми (см.вверху) значениями достижений. НО! Если среди показателей перспективы существуют цели убывающие и возрастающие, то выдается две строки на перспективу. Нужно ввернуть запрос, который в случае наличия двух строк для одной перспективы считал и выводил бы среднее арифметическое этих двух значений.
Попытки использовать запрос 3 в качестве источника в запросе
select perspective,avg(completed) from {текст запроса3} group by perspective
приводит к "ошибке в запросе union".
Если же я в Аксессе создаю запрос 3 и обращаюсь к нему из АДО как к таблице:
select perspective,avg(completed) from запрос3 group by perspective
то всё работает ОК.

Вопросы:
1. Можно ли корректно составить запрос для выполнения задачи?
2. Если нет, то можно ли средствами ADO создавать в аксессовской базе не таблицы, а именно запросы? Ну, что-нить типа create query ("select * from..")? :)

Заранее спасибо за ответы.

← →
sniknik © (2004-12-20 23:47) [1]

> 1 ...
наверняка можно но разбиратся долго...

> ... у, что-нить типа create query
CREATE VIEW представление [(поле_1[, поле_2[, ...]])] AS инструкцияSelect

← →
Макс (2004-12-21 06:55) [2]

Насколько я понял, CREATE VIEW не хочет работать с запросом с UNION? Я пока не смог придумать способа это обойти :(

← →
sniknik © (2004-12-21 07:46) [3]

CREATE VIEW ViewUNION AS
SELECT * FROM (
SELECT * FROM Table1
UNION
SELECT * FROM Table2)

← →
ЮЮ © (2004-12-21 10:24) [4]

> from {текст запроса3}

Очевидно, пропущены операторные скобки
from ({текст запроса3} )

← →
Макс (2004-12-21 11:05) [5]

2sniknik:
"В подчиненном запросе не допускаются объединения"

Буду пытаться перепроектировать БД - может получится проще запрос составить...

Попробую ещё представить два объединяющихся запроса как представление и обращаться уже к ним.

Спасибо за ответы.

← →
sniknik © (2004-12-21 11:19) [6]

> 2sniknik:
> "В подчиненном запросе не допускаются объединения"

именно так как в > sniknik © (21.12.04 07:46) [3]
или внес свои "улутшения"?

← →
Макс (2004-12-21 11:50) [7]

CREATE VIEW ViewUNION AS
SELECT * FROM (

SELECT [indicators].[perspective], First([indicator_values].[current_data]) AS [FirstOfcurrent_data], [indicator_values].[indicator], First([indicator_values].[indicator_value]) AS [FirstOfindicator_value], [indicators].[target_value]
FROM [indicator_values] INNER JOIN [indicators] ON [indicator_values].[indicator] = [indicators].[indicator]
GROUP BY [indicators].[perspective], [indicator_values].[indicator], [indicators].[target_value], [indicators].[type]
HAVING (([indicators].[type]) = "Возрастающий")
ORDER BY First([indicator_values].[current_data]), First([indicator_values].[indicator_value])
)
GROUP BY [%$##@_Alias].[perspective])
UNION
(SELECT [%$##@_Alias].[perspective], Avg((([target_value]/[FirstOfindicator_value])*100)) AS Completed
FROM
(
SELECT [indicators].[perspective], First([indicator_values].[current_data]) AS [FirstOfcurrent_data], [indicator_values].[indicator], First([indicator_values].[indicator_value]) AS [FirstOfindicator_value], [indicators].[target_value]
FROM [indicator_values] INNER JOIN [indicators] ON [indicator_values].[indicator] = [indicators].[indicator]
GROUP BY [indicators].[perspective], [indicator_values].[indicator], [indicators].[target_value], [indicators].[type]
HAVING (([indicators].[type]) = "Убывающий")
ORDER BY First([indicator_values].[current_data]), First([indicator_values].[indicator_value])

)

То бишь всё, как было написано выше - create view _name_ as select * from (select1 union select2)...

← →
Макс (2004-12-21 11:57) [8]

Вопрос вдогонку:
Есть программа ErWin. Она замечательно экспортирует нарисованную в ней БД в формат Access, генерируя скрипт.
Могу ли я использовать сей скрипт при динамическом создании БД через ADO? Если могу, то как?

← →
sniknik © (2004-12-21 12:00) [9]

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

← →
Макс (2004-12-21 12:03) [10]

делал бы в мастере - всё бы работало :)

> делал бы в мастере - всё бы работало :)
а нафига тогда столько скобок? мастер понятно вставляет... где читабельность? не пользуешся короткими алиасами, отступами.

← →
Макс (2004-12-21 12:12) [13]

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

← →
Макс (2004-12-21 12:14) [14]

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

---
Тогда ответь на вопрос - можно ли запускать к-л образом скрипты создания базы из дельфи - посредством АДО или чего-либо ещё?

создание базы DAO/ADOX (второе предпочтительнее), все остальное таблицы,индексы,вьюшки,... все. делается SQL ными командами через ADO. это если сам.
если ту хочеш подсунуть куданибудь скрипт от ErWin-а и на выходе получить готовую базу, то не выйдет, в "скрипте" jet понимает единственную команду. (можно толко через парсер какойнибудь выделять по одной команде и выполнять (мне это без надобности, я и так по одной пишу и добавляю ;о))

Сложный запрос с вложенными запросами для Access (ADO) Найти похожие ветки