Внедрение SQL-оператора case в SQL-запрос, использующий FOR XML

Question

Вчера я получил замечательную помощь от одного из пользователей 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-сервер может рассчитать информацию, которую я запрашивал, чтобы получить поле Total deductions. Однако добавление этой логики в запрос FOR XML оказалось неуловимым. Я не хочу сказать, что это невозможно сделать на том основании, что кажется, что мало что может быть сделано в SQL с небольшими побочными эффектами.

Если я просто вырезал и вставил часть запроса в мой запрос 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, или я просто пропустил очевидную "боковую мысль", которую я должен был иметь?

Спасибо

Answer 1

Я думаю, что вставка оригинального выражения case в правильном месте должна работать. Попробуй это:

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',
            LandingHeaders.VesselOwner AS '@Owner',
            FORMAT(SUM(LandingDetails.Quantity * LandingDetails.UnitPrice), 'N2') AS '@Owed',
            SocietyMemberships.WeeklyDeductionRate AS '@WeeklyDeductionRate',
            SocietyMemberships.FromMinimumReturn AS '@FromMinimumReturn',
            Deductions.DeductionRate AS '@DeductionRate',

          CASE 
             WHEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) - (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn 
             THEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate
             ELSE SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate 
          END AS '@TotalDeductions',

            (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,
                    LandingHeaders.VesselOwner,
                    SocietyMemberships.WeeklyDeductionRate,
                    SocietyMemberships.FromMinimumReturn,
                    Deductions.DeductionRate
        ORDER BY ContactId

        FOR XML PATH ('Owner'), TYPE)

    FOR XML PATH ('PurchaseOrders'), TYPE

END

Возможно, запрос можно улучшить, но без определений таблиц и некоторых примеров данных его немного сложно протестировать.

Проверьте эту скрипту SQL для слегка подправленной версии, которая должна дать тот же результат.