#sql #sql-server #sql-server-2008
#sql #sql-server #sql-server-2008
Вопрос:
Я использую средство чтения данных для получения значений из одной таблицы с использованием 3 разных запросов. Я получаю «System.Исключение IndexOutOfRangeException» для «billed030», которое находится во 2-м операторе select ниже.
Как я могу запросить базу данных здесь, потому что «где» отличается для каждого запроса.
BEGIN
SET NOCOUNT ON;
SELECT SUM(CAST(AmountBilled AS decimal(18, 2))) AS billed,
SUM(CAST(AmountPaid AS decimal(18, 2))) AS paid
FROM OrderBilling
LEFT OUTER JOIN Orders ON OrderBilling.LinkId = Orders.LinkId
AND Orders.OwnerId = @OwnerId
WHERE OrderBilling.PaidInFull = 'False'
AND OrderBilling.OwnerID = @OwnerID
AND ClientId = @ClientId
UNION ALL
SELECT SUM(CAST(AmountBilled AS decimal(18, 2))) AS billed030,
SUM(CAST(AmountPaid AS decimal(18, 2))) AS paid030
FROM OrderBilling
LEFT OUTER JOIN Orders ON OrderBilling.LinkId = Orders.LinkId
AND Orders.OwnerId = @OwnerId
WHERE OrderBilling.InvoiceDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE()
AND OrderBilling.PaidInFull = 'False'
AND OrderBilling.OwnerID = @OwnerID
AND ClientId = @ClientId
UNION ALL
SELECT SUM(CAST(AmountBilled AS decimal(18, 2))) AS billed3060,
SUM(CAST(AmountPaid AS decimal(18, 2))) AS paid3060
FROM OrderBilling
LEFT OUTER JOIN Orders ON OrderBilling.LinkId = Orders.LinkId
AND Orders.OwnerId = @OwnerId
WHERE OrderBilling.InvoiceDate BETWEEN DATEADD(MONTH, -2, GETDATE()) AND DATEADD(MONTH, -1, GETDATE())
AND OrderBilling.PaidInFull = 'False'
AND OrderBilling.OwnerID = @OwnerID
AND ClientId = @ClientId;
END;
Комментарии:
1. К вашему сведению, SQL Server 2008 не поддерживается уже более года; вы действительно должны уже искать пути обновления. Что касается ошибки, откуда она берется? Это не ошибка SQL Server. Вы пытаетесь ссылаться на столбец
billed030
в вашем прикладном уровне? Если да, то этот столбец не существует в возвращаемом наборе данных; этот запрос возвращает только 2 столбца:billed
иpaid
.2. При
UNION (ALL)
этом имена столбцов для результирующего набора являются именами столбцов из первого запроса.3. Я не знаю, почему у вас есть исключение outofrange, но вы можете избавиться от своего billed030, поскольку любой псевдоним, отличный от ПЕРВОГО запроса в union all, не имеет смысла
4. Ваш текущий tsql выдаст результирующий набор, в котором никто (пользователь или код) НЕ сможет отличить, какая строка принадлежит к какой из ваших 3 групп. Если это то, что вам нужно, вы должны изменить запрос, чтобы сделать это возможным. Гордон предложил один подход.
5. Я ошибочно пометил sql server 2008. Это sql server 2012. Ошибка в .NET 4.5, и не были возвращены значения для billed030, paid030, billed3060 и paid 3060 . Если я удаляю первый оператор, тогда «030» является первым, тогда он работает для этого. Это просто не сработало со 2-м и 3-м операторами. Я пробовал с UNION All и без UNION All, и я просто хотел запустить 3 оператора в одном SP вместо кодирования 3 отдельных запросов. Я тестирую приведенный ниже ответ, и пока он работает. Это очень сложно для моего набора навыков, поэтому я благодарен вам за помощь. Я сообщу о результатах.
Ответ №1:
Это не дает конкретного ответа на вопрос (который не является ошибкой SQL Server), однако я не вижу здесь необходимости в 3 UNION ALL
запросах. Вы можете использовать некоторую условную агрегацию, а затем некоторое отключение для получения одного и того же набора данных:
WITH CTE AS(
SELECT SUM(CAST(O.AmountBilled AS decimal(18, 2))) AS billed, --If AmountBilled is in the table Orders, why a LEFT JOIN? If it isn't, why JOIN at all?
SUM(CAST(O.AmountPaid AS decimal(18, 2))) AS paid,
SUM(CASE WHEN OB.InvoiceDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE() THEN CAST(O.AmountBilled AS decimal(18, 2)) END) AS billed030,
SUM(CASE WHEN OB.InvoiceDate BETWEEN DATEADD(MONTH, -1, GETDATE()) AND GETDATE() THEN CAST(O.AmountPaid AS decimal(18, 2)) END) AS paid030,
SUM(CASE WHEN OB.InvoiceDate BETWEEN DATEADD(MONTH, -2, GETDATE()) AND DATEADD(MONTH, -1, GETDATE()) THEN CAST(O.AmountBilled AS decimal(18, 2)) END) AS billed3060,
SUM(CASE WHEN OB.InvoiceDate BETWEEN DATEADD(MONTH, -2, GETDATE()) AND DATEADD(MONTH, -1, GETDATE()) THEN CAST(O.AmountPaid AS decimal(18, 2)) END) AS paid3060
FROM dbo.OrderBilling OB
LEFT OUTER JOIN dbo.Orders O ON OB.LinkId = O.LinkId
AND O.OwnerId = @OwnerId
WHERE OB.PaidInFull = 'False'
AND OB.OwnerID = @OwnerID
AND OB.ClientId = @ClientId)
SELECT --V.category,
V.billed,
V.paid
FROM CTE C
CROSS APPLY (VALUES('',C.billed,C.paid),
('030',C.billed030,C.paid3060),
('3060',C.billed3060,C.paid3060))V(category,billed,paid);
Комментарии:
1. Это сработало для меня, за исключением «С CTE как ()» в начале и » Выбрать v… Из CTE C» в конце. Я удалил их и получил нужные мне результаты. Спасибо.
2. Это не даст тех результатов, которые вы определяете, но хорошо, @mlg74 .
3. Это дает мне нужные результаты. Добавленные части, которые я удалил, по-прежнему выдают мне то же исключение вне диапазона с сервера, поэтому я не уверен, почему это так, но часть Select Case, которую вы дали, отлично работает для меня.