Блог

Я новичок в stack overflow и самостоятельно изучал SQL для создания пользовательских отчетов на работе. Я был бы очень признателен, если бы кто-нибудь мог указать мне правильное направление, чтобы возвращать среднее значение при использовании выражения case, вот история:

Я пытаюсь создать запрос, который возвращает данные о ценах из счетов-фактур продаж. У меня есть функциональный отчет, но я пытаюсь стандартизировать ценовую единицу измерения, поскольку наши затраты не всегда устанавливаются в том же UOM, что и цена продажи. Я ограничен в том, какой код я могу написать, поскольку я использую внутреннюю программу создания отчетов, которая позволяет нам писать только подзапросы; У меня нет прямого доступа к СУБД.

Я написал оператор case в своем подзапросе, который решает проблему UOM, но я застрял, пытаясь агрегировать результаты.

После изменения [View_QB_SalesOrderLine].[Цена] в среднее значение ([View_QB_SalesOrderLine].[Цена], я попытался агрегировать выражение Case моего подзапроса, используя AVG как до, так и внутри оператора Case, но все, что я пытаюсь, приводит к сбою запроса.

Единственный код, который я написал ниже, — это подзапрос, все остальное генерируется инструментом создания отчетов. Я очень ценю любую помощь, которую я могу получить!

Обновлено запросом, который вызвал ошибку, а не рабочим кодом, который я пытался создать…урок усвоен. Этот запрос выполняется, если я удаляю AVG из обращения, но возвращает только средние значения [Цены], сгруппированные по MFR, КАТАЛОГУ и [условной стоимости СПА]. Для каждого экземпляра элемента создается новая строка с уникальным [условным значением стоимости СПА]. При использовании AVG перед выражением Case запрос выдает ошибки (ошибка: ссылка на объект не установлена для экземпляра объекта.) * Это сообщение кажется общим, поскольку это единственное сообщение, которое я когда-либо видел отображаемым, независимо от проблемы с запросом. Пожалуйста, имейте в виду, что я могу писать подзапросы только в этом инструменте, весь остальной код генерируется инструментом. Я подозреваю, что инструмент, автоматически добавляющий подзапрос в Group By, имеет какое-то отношение к ошибке при добавлении AVG в подзапрос.

Для контекста я добавил изображение того, как выглядит графический интерфейс, с которым я работаю. Я пишу пользовательские выражения в Notepad и вставляю их в инструмент.Report Writer

 SELECT  DISTINCT [Mfr] = [View_QB_SalesOrderLine].[Mfr]
,[CatalogNo] = [View_QB_SalesOrderLine].[CatalogNo]
,[SPA Cost Conv] = (AVG(CASE
    WHEN [Price UOM] = [SPA Cost UOM] 
    THEN [SPA Cost] 
    WHEN [Price UOM] = 'E' AND [SPA Cost UOM] = 'C'
    THEN [SPA Cost]/100
    WHEN [Price UOM] = 'E' AND [SPA Cost UOM] = 'M'
    THEN [SPA Cost]/1000
    WHEN [Price UOM] = 'C' AND [SPA Cost UOM] = 'E'
    THEN [SPA Cost] * 100
    WHEN [Price UOM] = 'C' AND [SPA Cost UOM] = 'M'
    THEN [SPA Cost] / 100
    WHEN [Price UOM] = 'M' AND [SPA Cost UOM] = 'E'
    THEN [SPA Cost] * 1000
    WHEN [Price UOM] = 'M' AND [SPA Cost UOM] = 'C'
    THEN [SPA Cost] * 100
END))
,[Price] = AVG([View_QB_SalesOrderLine].[Price])
,[Price UOM] = [View_QB_SalesOrderLine].[Price UOM]
FROM View_QB_SalesOrderLine
GROUP BY [View_QB_SalesOrderLine].[Mfr]
,[View_QB_SalesOrderLine].[CatalogNo]
,AVG(CASE
    WHEN [Price UOM] = [SPA Cost UOM] 
    THEN [SPA Cost] 
    WHEN [Price UOM] = 'E' AND [SPA Cost UOM] = 'C'
    THEN [SPA Cost]/100
    WHEN [Price UOM] = 'E' AND [SPA Cost UOM] = 'M'
    THEN [SPA Cost]/1000
    WHEN [Price UOM] = 'C' AND [SPA Cost UOM] = 'E'
    THEN [SPA Cost] * 100
    WHEN [Price UOM] = 'C' AND [SPA Cost UOM] = 'M'
    THEN [SPA Cost] / 100
    WHEN [Price UOM] = 'M' AND [SPA Cost UOM] = 'E'
    THEN [SPA Cost] * 1000
    WHEN [Price UOM] = 'M' AND [SPA Cost UOM] = 'C'
    THEN [SPA Cost] * 100
END)
,[View_QB_SalesOrderLine].[Price UOM]
ORDER BY [View_QB_SalesOrderLine].[Mfr]  ASC
,[View_QB_SalesOrderLine].[CatalogNo]  ASC
  

Вы ссылались на подзапросы и внешние запросы, но, похоже, ничто из приведенного выше не относится ни к одному из них. Это заставляет меня задуматься, нужно ли добавлять условие корреляции. Также обратите внимание, что Price агрегат не был автоматически добавлен в столбцы группировки. Поэтому я не уверен, что мне интересно, являются ли причиной этого дополнительные круглые скобки или какой-либо неподдерживаемый синтаксис.

 SELECT View_QB_SalesOrderLine.Mfr, View_QB_SalesOrderLine.CatalogNo,
    AVG("SPA Cost UOM" *
      CASE "Price UOM"   "SPA Cost UOM"
        WHEN 'EM' THEN 0.001
        WHEN 'EC' THEN 0.01
        WHEN 'CM' THEN 0.01
        WHEN 'CE' THEN 100
        WHEN 'MC' THEN 100
        WHEN 'ME' THEN 1000
        ELSE 1
      EN) AS "SPA Cost Conv",
    AVG(Price) AS Price
    /* , "Price UOM" -- this is not valid here */
FROM View_QB_SalesOrderLine
GROUP BY Mfr, CatalogNo
ORDER BY Mfr, CatalogNo
  

Давным-давно я работал с инструментом запроса, который реорганизовывал разрывы строк, поэтому при использовании комментариев было безопаснее использовать форму в квадратных скобках /* */ , а не в конце строки -- . Возможно, вы захотите полностью удалить мою прокомментированную строку.

использовать

 Group BY 
  

в вашем коде, основанном на требовании, которое, как я полагаю, в вашем случае было бы продано