Как получить набор данных, используя сложный запрос на выборку
У меня стоит задача сделать список более ранних продаж со схожей спецификацией.
Нужно вывести следующий список для % совпадения > 30% отсортированный по %совпадения+Дата создания:
Номер продажи / Дата созд / Заказчик / Состояние / Сумма / %совпадения
Я составил такой вот запрос на выборку, который возвращает нужные мне данные:
SET @OpportunityID = '{DF213AAF-393D-434F-B0F0-E3E1F614C8D6}'
SELECT O.ID, O.OpportunityNumber, O.CreatedOn, O.CustomerID, C.Name, S.Name, O.BasicAmount, T.PercentW
FROM tbl_Opportunity O
LEFT OUTER JOIN
(SELECT T.OpportunityID AS OpportunityID, SUM(T.PercentWeight) AS PercentW
FROM
(SELECT OpportunityID, (100 / (SELECT COUNT(ID) FROM tbl_OfferingInOpportunity WHERE OpportunityID = @OpportunityID)) AS PercentWeight
FROM tbl_OfferingInOpportunity
WHERE OfferingID IN (SELECT OfferingID FROM tbl_OfferingInOpportunity WHERE OpportunityID = @OpportunityID) AND
OpportunityID > @OpportunityID) AS T
GROUP BY T.OpportunityID
HAVING SUM(T.PercentWeight) > 30) T ON T.OpportunityID = O.ID
LEFT OUTER JOIN tbl_Account C ON C.ID = O.CustomerID
LEFT OUTER JOIN tbl_OpportunityStatus S ON S.ID = O.StatusID
ORDER BY T.PercentW DESC, O.CreatedOn DESC
Хочу в Террасофт выводить окно-грид с колонками, и использовать для этого выборку данных (датасет).
Вопрос в том, как в Террасофт создать Select Query, чтобы его можно было подключить к датасету?
Пытался создать Custom Query, но он не возвращает данные как Select Query и, соответственно, его нельзя использвать для датасета:
SQL код:
FROM !tbl_Opportunity!> O
LEFT OUTER JOIN
(SELECT T.OpportunityID AS OpportunityID, SUM(T.PercentWeight) AS PercentW
FROM
(SELECT OpportunityID, (100 / (SELECT COUNT(ID) FROM !tbl_OfferingInOpportunity!> WHERE OpportunityID = :OpportunityID)) AS PercentWeight
FROM !tbl_OfferingInOpportunity!>
WHERE OfferingID IN (SELECT OfferingID FROM !tbl_OfferingInOpportunity!> WHERE OpportunityID = :OpportunityID) AND
OpportunityID > :OpportunityID) AS T
GROUP BY T.OpportunityID
HAVING SUM(T.PercentWeight) > 30) T ON T.OpportunityID = O.ID
LEFT OUTER JOIN !tbl_Account!> C ON C.ID = O.CustomerID
LEFT OUTER JOIN !tbl_OpportunityStatus!> S ON S.ID = O.StatusID
ORDER BY T.PercentW DESC, O.CreatedOn DESC
Написал для теста Custom Query такую функцию, но в конце переменная Result содержит значение 0, а не выборку данных:
OpportunityID = '{DF213AAF-393D-434F-B0F0-E3E1F614C8D6}';
var cq_SimilarSpecification =
Services.GetNewItemByUSI('cq_SimilarSpecification');
SetParameterValue(cq_SimilarSpecification.Parameters, 'OpportunityID',
OpportunityID);
var Result = cq_SimilarSpecification.Execute();
debugger;
}
function Main() {
SimilarSpecification();
}
Нравится
В Select Query можно впихнуть произвольный код, вплоть до вызова функции, с помощью некоторых махинаций.
"Зверев Александр" написал:В Select Query можно впихнуть произвольный код, вплоть до вызова функции, с помощью некоторых махинаций.
Александр, благодарю за ценный комментарий. Ваше решение этого вопроса натолкнуло меня на еще более приемлимое, на мой взгляд, решение - без использования функции.
Первая колонка содержит вот такой sql код:
O.ID AS ID, O.OpportunityNumber AS OpportunityNumber, O.CreatedOn AS CreatedOn, O.CustomerID AS CustomerID, C.Name AS CustomerName, S.Name AS StatusName, O.BasicAmount AS BasicAmount, CAST(T.PercentW AS FLOAT) / 100 AS PercentW, CASE WHEN O.ID = :OpportunityID THEN N'СРАВНИВАЕМАЯ' ELSE N'' END AS Descr FROM (SELECT T.OpportunityID as OpportunityID, SUM(T.PercentWeight) as PercentW FROM (SELECT OpportunityID, (CONVERT(FLOAT, 10000) / (SELECT COUNT(ID) FROM [dbo].[tbl_OfferingInOpportunity] WHERE OpportunityID = :OpportunityID)) AS PercentWeight FROM [dbo].[tbl_OfferingInOpportunity] WHERE OfferingID IN (SELECT OfferingID FROM [dbo].[tbl_OfferingInOpportunity] WHERE OpportunityID = :OpportunityID)) AS T GROUP BY T.OpportunityID HAVING SUM(T.PercentWeight) > 30) T LEFT OUTER JOIN [dbo].[tbl_Opportunity] O ON T.OpportunityID = O.ID LEFT OUTER JOIN [dbo].[tbl_Account] C ON C.ID = O.CustomerID LEFT OUTER JOIN [dbo].[tbl_OpportunityStatus] S on S.ID = O.StatusID ORDER BY T.PercentW DESC, O.CreatedOn DESC /*
Посоздавал соответствующие колонки с таким же названием как в запросе, только пустые (как в Вашем примере).
Добавил параметр OpportunityID.
Остальне - как Вы написали.
Далее я создал датасет и окно с гридом, и - работает :smile:
Еще раз благодарю :twisted: