Мастера DELPHI (документация, исходники, скачать)

Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG

« предыдущая ветвь | форум | следующая ветвь »

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

Макс © (2004-12-20 23:20:00)
Добрый день.
Возникла проблема с запуском запроса к БД.
Имеем БД с таблицами 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-21 12:38:00) [1]
создание базы DAO/ADOX (второе предпочтительнее), все остальное таблицы,индексы,вьюшки,... все. делается SQL ными командами через ADO. это если сам.
если ту хочеш подсунуть куданибудь скрипт от ErWin-а и на выходе получить готовую базу, то не выйдет, в "скрипте" jet понимает единственную команду. (можно толко через парсер какойнибудь выделять по одной команде и выполнять (мне это без надобности, я и так по одной пишу и добавляю ;о))

Макс (2004-12-21 12:14:00) [2]
я не пользуюсь ErWin, я замечательно пишу скрипты сам... хотя и мастером пользуюсь аксесовским, правда с обязательной переделкой (сталивался, мастер подсунул глючный с циклическими ссылками да и медленные часто)

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

Макс (2004-12-21 12:12:00) [3]
умение создавать читабельный код приходит с опытом :)
скобки для безглючности - хз, какие там у джета зарезервированные слова...

sniknik (2004-12-21 12:08:00) [4]
> делал бы в мастере - всё бы работало :)
а нафига тогда столько скобок? мастер понятно вставляет... где читабельность? не пользуешся короткими алиасами, отступами.

Макс (2004-12-21 12:03:00) [5]
делал бы в мастере - всё бы работало :)

sniknik (2004-12-21 12:03:00) [6]
я не пользуюсь ErWin, я замечательно пишу скрипты сам... хотя и мастером пользуюсь аксесовским, правда с обязательной переделкой (сталивался, мастер подсунул глючный с циклическими ссылками да и медленные часто)

sniknik (2004-12-21 12:00:00) [7]
у тебя UNION выходит за пределы скобок, а у меня нет.
у тебя селект из первого селекта на него групировка и к нему юнион пристегнут... неправильно в обшем. начинай с малого, возьми мой - сработает, добавь чуток... так и монстра напишеш. (хотя ты их видно не пишеш, в мастере делаеш... ну так подложи данный туда, для "затравки")

Макс (2004-12-21 11:57:00) [8]
Вопрос вдогонку:
Есть программа ErWin. Она замечательно экспортирует нарисованную в ней БД в формат Access, генерируя скрипт.
Могу ли я использовать сей скрипт при динамическом создании БД через ADO? Если могу, то как?

Макс (2004-12-21 11:50:00) [9]
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)...

sniknik (2004-12-21 11:19:00) [10]
> 2sniknik:
> "В подчиненном запросе не допускаются объединения"

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

Макс (2004-12-21 11:05:00) [11]
2sniknik:
"В подчиненном запросе не допускаются объединения"

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

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

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

ЮЮ (2004-12-21 10:24:00) [12]
> from {текст запроса3}

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

sniknik (2004-12-21 07:46:00) [13]
CREATE VIEW ViewUNION AS
SELECT * FROM (
SELECT * FROM Table1
UNION
SELECT * FROM Table2)

Макс (2004-12-21 06:55:00) [14]
Насколько я понял, CREATE VIEW не хочет работать с запросом с UNION? Я пока не смог придумать способа это обойти :(

sniknik (2004-12-20 23:47:00) [15]
> 1 ...
наверняка можно но разбиратся долго...

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

Наверх

	« предыдущая ветвь \| форум \| следующая ветвь »