Добавьте имя столбца в переменную и используйте его при последующих вычислениях в предложении WHERE

ProgramBox

Добавьте имя столбца в переменную и используйте его при последующих вычислениях в предложении WHERE

Post author:admin
Запись опубликована:14 января, 2022
Post category:Вопросы по программированию

#sql #sql-server #tsql

Вопрос:

У меня проблема. Мне нужно определить имя столбца, под которым будут продолжаться вычисления. Поэтому я написал избранное:

 DECLARE @column VARCHAR(MAX)
DECLARE @ColumnA VARCHAR(MAX)
DECLARE @ColumnB VARCHAR(MAX)

SET @ColumnA = 'RegistrationDate'
SET @ColumnB = 'EntryDate'
SET @column = CASE
    WHEN CONVERT(DATE,GETDATE()) NOT IN (
        '2021-08-04','2021-08-05','2021-08-06','2021-08-07','2021-08-08','2021-08-09','2021-08-10','2021-09-07','2021-09-08','2021-09-09','2021-09-10','2021-09-11',
'2021-09-12','2021-09-13','2021-10-05','2021-10-06','2021-10-07','2021-10-08','2021-10-09','2021-10-10','2021-10-11','2021-11-09','2021-11-10','2021-11-11','2021-11-12','2021-11-13','2021-11-14','2021-11-15','2021-12-07',
'2021-12-08','2021-12-09','2021-12-10','2021-12-11','2021-12-12','2021-12-13'
        ) THEN
            QUOTENAME(@Column)
        ELSE
            QUOTENAME(@ColumnB)
    END

SELECT @column

который возвращает меня [RegistrationDate] или [EntryDate] и сохраняет это в переменной @column . Теперь, когда я знаю, под каким столбцом мне следует вычислять, я хочу вставить эту переменную @column в мое основное WHERE предложение select one:

 DECLARE @column VARCHAR(MAX)
DECLARE @ColumnA VARCHAR(MAX)
DECLARE @ColumnB VARCHAR(MAX)

SET @ColumnA = 'RegistrationDate'
SET @ColumnB = 'EntryDate'
SET @column = CASE
    WHEN CONVERT(DATE,GETDATE()) NOT IN (
        '2021-08-04','2021-08-05','2021-08-06','2021-08-07','2021-08-08','2021-08-09','2021-08-10','2021-09-07','2021-09-08','2021-09-09','2021-09-10','2021-09-11',
'2021-09-12','2021-09-13','2021-10-05','2021-10-06','2021-10-07','2021-10-08','2021-10-09','2021-10-10','2021-10-11','2021-11-09','2021-11-10','2021-11-11','2021-11-12','2021-11-13','2021-11-14','2021-11-15','2021-12-07',
'2021-12-08','2021-12-09','2021-12-10','2021-12-11','2021-12-12','2021-12-13'
        ) THEN
            QUOTENAME(@Column)
        ELSE
            QUOTENAME(@ColumnB)
    END

SELECT
    CASE WHEN final.Branch IS NULL THEN 'Total'
         ELSE final.Branch
    END AS 'Branch',
    final.TR

FROM
    (
        SELECT
            CASE 
                WHEN main.BRANCHNO = 1 THEN 'One'
                WHEN main.BRANCHNO = 2 THEN 'Two'
                WHEN main.BRANCHNO = 3 THEN 'Three'
                WHEN main.BRANCHNO = 4 THEN 'Four'
                WHEN main.BRANCHNO = 5 THEN 'Five'
                WHEN main.BRANCHNO = 6 THEN 'Six'
            END AS 'Branch',
            COUNT(*) AS 'TR'

        FROM
        (
            SELECT
                *
            FROM
                [TABLE]
            WHERE
                Status = 100
                AND
                BRANCHNO IN (1,2,3,4,5,6)
                AND
                Type = 'TR'
                AND
                **@column** = CONVERT(DATE, CASE WHEN DATENAME(dw, getdate()) = 'Monday' THEN getdate()-3 ELSE getdate()-1 END
        )
    ) AS main
GROUP BY
    main.BRANCHNO WITH ROLLUP
) AS final

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

Msg 241, Уровень 16, Состояние 1, Строка 11 Не удалось выполнить преобразование при преобразовании даты и/или времени из символьной строки.

Я представлял себе все очень просто: я помещаю имя столбца в переменную, а затем это имя, помещенное в начале WHERE предложения, будет распознано как имя столбца, а затем *= CONVERT(DATE, CASE WHEN DATENAME(dw, getdate()) etc выполнит всю работу.

Но этого не произошло. Может быть, кто-то знает, почему и, может быть, они знают, как решить эту задачу?

1. SQL Server не позволяет использовать переменные для указания идентификаторов объектов базы данных (имен таблиц, имен столбцов и т. Д.), Поэтому вам необходимо использовать динамический SQL (т. Е. построение строк с sp_executesql помощью), Что чревато опасностью. Повеселиться.

2. Тем не менее, если вы проверяете только один из 2 столбцов ( RegistrationDate и EntryDate ), то есть более простые (и лучшие) альтернативы.

3. Вы не должны использовать SELECT * во вложенном запросе, когда внешний запрос использует только один столбец ( branchno ).

Ответ №1:

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

Некоторые варианты у вас, похоже, есть…

 AND CASE @column WHEN 'RegistrationDate' THEN RegistrationDate
                 WHEN 'EntryDate'        THEN EntryDate
    END
    =
    CONVERT(DATE, CASE WHEN DATENAME(dw, getdate()) = 'Monday' THEN getdate()-3 ELSE getdate()-1 END)

Или у вас есть два запроса, которые отличаются только ссылочным столбцом…

 IF (@column = 'RegistrationDate')
  <query1>
ELSE IF (@column = 'EntryDate')
  <query2>

Или «Динамический SQL», где вы создаете новую строку с вашим кодом SQL и выполняете ее вызовом sp_executesql (предполагая, что это SQL Server, каковым он и является).

Я рекомендую прочитать это : https://www.sommarskog.se/dyn-search.html

РЕДАКТИРОВАТЬ: Чистая альтернатива SQL, предполагающая SQL Server

 DECLARE @mode INT = CASE
    WHEN CONVERT(DATE,GETDATE()) NOT IN (
        '2021-08-04','2021-08-05','2021-08-06','2021-08-07','2021-08-08','2021-08-09','2021-08-10','2021-09-07','2021-09-08','2021-09-09','2021-09-10','2021-09-11',
'2021-09-12','2021-09-13','2021-10-05','2021-10-06','2021-10-07','2021-10-08','2021-10-09','2021-10-10','2021-10-11','2021-11-09','2021-11-10','2021-11-11','2021-11-12','2021-11-13','2021-11-14','2021-11-15','2021-12-07',
'2021-12-08','2021-12-09','2021-12-10','2021-12-11','2021-12-12','2021-12-13'
        ) THEN
            0
        ELSE
            1
    END;

DECLARE @filter_date DATE = CONVERT(DATE, CASE WHEN DATENAME(dw, getdate()) = 'Monday' THEN getdate()-3 ELSE getdate()-1 END;

WITH
  source AS
(
  SELECT
    *
  FROM
    [TABLE]
  WHERE
        Status = 100
    AND BRANCHNO IN (1,2,3,4,5,6)
    AND Type = 'TR'
),
  filtered_source AS
(
  SELECT 0 AS mode, * FROM source WHERE RegistrationDate = @filter_date
  UNION ALL
  SELECT 1 AS mode, * FROM source WHERE EntryDate        = @filter_date
)
SELECT
  COALESCE(
    CASE 
      WHEN BRANCHNO = 1 THEN 'One'
      WHEN BRANCHNO = 2 THEN 'Two'
      WHEN BRANCHNO = 3 THEN 'Three'
      WHEN BRANCHNO = 4 THEN 'Four'
      WHEN BRANCHNO = 5 THEN 'Five'
      WHEN BRANCHNO = 6 THEN 'Six'
    END,
    'Total'
  )
    AS 'Branch',
  COUNT(*) AS 'TR'
FROM
  filtered_source
WHERE
  mode = @mode
GROUP BY
  GROUPING SETS (
    (mode),
    (mode, BRANCHNO)
  );

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

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

1. Удивительно, что SQL существует уже полвека, и у нас есть лучшие методы работы с SQL на протяжении десятилетий, и все же до сих пор нет хорошего способа динамического построения запроса в SQL, поэтому мы вынуждены использовать ужасно небезопасные методы, такие как динамический SQL или сторонние внешние ORM, такие как EF. Это безумие, как мы зашли так далеко, и до сих пор никто в командах Oracle, Postgres и SQL Server не решил: «Может быть, нам следует это исправить»…

2. @Dai — Я частично согласен с вами. Я говорю это отчасти потому, что в 90% случаев, которые я вижу, необходимость в динамическом SQL может быть устранена с помощью нормализованных структур. Проще говоря, если вы рассматриваете таблицу как электронную таблицу, она будет появляться неоднократно, но в гораздо меньшей степени, если вы используете нормализованные модели. Крайним следствием этого, однако, является то, что все становится ПОДСЛУШИВАЕМЫМ, и это тоже плохо. Итак, почему поставщики не расширяют SQL для размещения этих 10% случаев? Опять же, проще говоря, это будет иметь огромный каскадный эффект для оптимизаторов/планировщиков и будет дорогостоящим мероприятием без достаточной коммерческой выгоды.

Метки: Добавьте имя столбца в переменную и используйте его при последующих вычислениях в предложении WHERE