Вчера я получил замечательную помощь от другого пользователя SO (см. здесь), что позволило мне сделать большой прогресс в достижении моей цели. Теперь я пытаюсь установить, может ли предложенное волшебное дополнение быть встроено в существующий запрос, который производит вывод xml.
Существующий запрос выглядит следующим образом:
PROCEDURE [dbo].[CreateLandingPurchaseOrderDetails]
-- Add the parameters for the stored procedure here
@startDate DATE,
@endDate DATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT (SELECT
Contacts.ContactId AS '@ContactId',
VesselOwner AS '@Owner',
FORMAT(SUM(LandingDetails.Quantity * LandingDetails.UnitPrice), 'N2') AS '@Owed',
SocietyMemberships.WeeklyDeductionRate AS '@WeeklyDeductionRate',
SocietyMemberships.FromMinimumReturn AS '@FromMinimumReturn',
Deductions.DeductionRate AS '@DeductionRate',
(SELECT DISTINCT
ld1.ProductId AS '@ProductId',
FORMAT(AVG(ld1.UnitPrice), 'N2') AS '@Cost',
FORMAT(SUM(ld1.Quantity), 'N2') AS '@Quantity'
FROM LandingDetails ld1
INNER JOIN dbo.LandingHeaders lh1
ON ld1.LandingId = lh1.LandingId
WHERE Posted = 0
AND lh1.VesselOwner = LandingHeaders.VesselOwner
GROUP BY ld1.ProductId
FOR XML PATH ('Products'), TYPE)
FROM dbo.LandingDetails
INNER JOIN dbo.LandingHeaders
ON LandingDetails.LandingId = LandingHeaders.LandingId
INNER JOIN dbo.Vessels
ON LandingHeaders.VesselId = Vessels.VesselId
INNER JOIN dbo.Contacts
ON Vessels.OwnerId = Contacts.ContactId
INNER JOIN dbo.SocietyMemberships
ON Contacts.SocietyId = SocietyMemberships.SocietyId
INNER JOIN dbo.Deductions
ON Vessels.DeductionId = Deductions.DeductionId
WHERE LandingHeaders.Posted = 0
AND LandingDate1 BETWEEN @startDate AND @endDate
GROUP BY ContactId,
VesselOwner,
SocietyMemberships.WeeklyDeductionRate,
SocietyMemberships.FromMinimumReturn,
Deductions.DeductionRate
ORDER BY ContactId
FOR XML PATH ('Owner'), TYPE)
FOR XML PATH ('PurchaseOrders'), TYPE
END
Это создает вывод xml в этих строках;
<PurchaseOrders>
<Owner ContactId="39" Owner="Paul Joy" Owed="1,609.39" WeeklyDeductionRate="10.00" FromMinimumReturn="110.00" DeductionRate="0.0150">
<Products ProductId="33" Cost="5.00" Quantity="0.40" />
<Products ProductId="34" Cost="1.80" Quantity="0.90" />
<Products ProductId="41" Cost="2.30" Quantity="1.30" />
Я хотел бы добавить еще один атрибут к элементу Owner ( TotalDeductions ). Из предыдущего вопроса, который я задал, я вижу, как SQL может вычислить информацию, которую я искал, чтобы создать поле «Общие вычеты». Однако добавление этой логики в запрос FOR XML оказалось труднодостижимым. Я ненавижу говорить, что это невозможно сделать на том основании, что кажется, что почти ничего нельзя сделать в SQL с небольшим боковым мышлением.
Если я просто вырезаю и вставляю часть запроса case в мой запрос FOR XML, компилятор указывает, что Owed не является допустимым именем. Это я понимаю. Однако, если я просто вставлю часть владельца для xml следующим образом:
WITH cte AS (
'embed the part of the FOR XML producing the owner element here
)
SELECT
ContactId,
Owed,
WeeklyDeductionRate,
FromMinimumReturn,
DeductionRate,
CASE
WHEN Owed - (Owed * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn
THEN Owed * DeductionRate + WeeklyDeductionRate
ELSE Owed * DeductionRate END
AS TotalDeductions
FROM cte
Затем, оставив в стороне ошибки компиляции, на тот момент я не буду создавать XML, который мне нужен.
Нашел ли я наконец что-то, что на самом деле нельзя сделать в SQL, или я просто пропустил очевидную «нестандартную мысль», которую должен был иметь?
Спасибо