Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как обрaботать NULL в подзапросе, подскажите пожалуйста  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 522
 		Добрый день!

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

Сама схема таблиц такая:
--таблица изделий
declare @Subjects table([Name] varchar(10), id int)
insert into @Subjects(name, id)
select 'Шкаф1', 1 union all
select 'Шкаф2', 2 union all
select 'Шкаф3', 3  

--таблица мастеров
declare @Creators table(ID int, [Name] nvarchar(10))
insert into @Creators(ID, [Name])
select 1, 'Ivanov' union all
select 2, 'Petrov' union all
select 3, 'Sidorov'

--таблица связей
declare @Relations table(SubjectID int, CreatorID int, Comment nvarchar(10))
insert into @Relations(SubjectID, CreatorID, Comment)
select 1, 1, 'Хорошо' union all
select 1, 2, NULL union all
select 2, 2, 'Хорошо' union all
select 3, 2, 'Недодел' union all
select 2, 3, NULL 

--нужно получить все изделия конкретного мастера (CreatorID=2) с комментариями. В этом поле Comment могут быт NULL 
SELECT s.[Name], 
	(select rel.Comment as 'data()' 
		FROM @Subjects AS s2 INNER JOIN @Relations as rel ON s2.ID = rel.SubjectID AND rel.[CreatorID] = 2 
		WHERE s.id = s2.id order by rel.[CreatorID] for xml path(''))
	FROM @Subjects s


в итоге, я получаю такие данные:
Шкаф1	NULL
Шкаф2	Хорошо
Шкаф3	Недодел

И вот вопрос, как этот NULL здесь можно заменить на пустую строку? ЧТо лучше сделать: CASE, прверять на ISNULL...?
Не исключено, что и сам запрос здесь из древности прибыл, возможно его сделать иначе.
11 июн 18, 16:47    [21484727]     Ответить | Цитировать Сообщить модератору
 Re: Как обрaботать NULL в подзапросе, подскажите пожалуйста  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 522
 		а, возможно ещё важные заметка, поля (SubjectID и CreatorID) - уникальный ключ
11 июн 18, 16:50    [21484728]     Ответить | Цитировать Сообщить модератору
 Re: Как обрaботать NULL в подзапросе, подскажите пожалуйста  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 522
 		а, возможно ещё важная заметка, поля в таблице @Relations (SubjectID и CreatorID) - уникальный ключ
11 июн 18, 16:51    [21484729]     Ответить | Цитировать Сообщить модератору
 Re: Как обрaботать NULL в подзапросе, подскажите пожалуйста  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
 		У меня ваш запрос возвращает пустую строку, а никакой не NULL, что логично,
ибо XML считает NULL пустой строкой.

А для таких данных вам результат нравится?
--таблица изделий
declare @Subjects table([Name] varchar(10), id int)
insert into @Subjects(name, id)
select 'Шкаф1', 1 union all
select 'Шкаф2', 2 union all
select 'Шкаф3', 3  

--таблица мастеров
declare @Creators table(ID int, [Name] nvarchar(10))
insert into @Creators(ID, [Name])
select 1, 'Ivanov' union all
select 2, 'Petrov' union all
select 3, 'Sidorov'

--таблица связей
declare @Relations table(SubjectID int, CreatorID int, Comment nvarchar(20))
insert into @Relations(SubjectID, CreatorID, Comment)
select 1, 1, 'Хорошо' union all
select 1, 2, NULL union all
select 2, 2, 'Хорошо&Плохо' union all
select 3, 2, 'Недодел' union all
select 2, 3, NULL 

--нужно получить все изделия конкретного мастера (CreatorID=2) с комментариями. В этом поле Comment могут быт NULL 
SELECT s.[Name], 
	quotename((select rel.Comment as 'data()' 
		FROM @Subjects AS s2 INNER JOIN @Relations as rel ON s2.ID = rel.SubjectID AND rel.[CreatorID] = 2 
		WHERE s.id = s2.id order by rel.[CreatorID] for xml path('')))
	FROM @Subjects s
11 июн 18, 17:55    [21484783]     Ответить | Цитировать Сообщить модератору
 Re: Как обрaботать NULL в подзапросе, подскажите пожалуйста  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 522
iap
У меня ваш запрос возвращает пустую строку, а никакой не NULL, что логично,
ибо XML считает NULL пустой строкой.

Я сам увидел это, да. Но, на реальных данные там действительно NULL возвращает. Возможно на виртуальных таблицах иначе реализовано.

iap
А для таких данных вам результат нравится?

Тут всё равно надо "чистить" результат на клиенте, а, собственно, этого и хочу избежать, т.к. массив сразу падает на лист Excel. Но если есть NULL, то возникает исключение.

Пока обернул в такой вариант, чтоб работала не стояла:
cast(ISNULL((select rel.Comment as 'data()' 
		FROM @Subjects AS s2 INNER JOIN @Relations as rel ON s2.ID = rel.SubjectID AND rel.[CreatorID] = 2 
		WHERE s.id = s2.id order by rel.[CreatorID] for xml path('')),'') as nvarchar(255)), m.CategoryID, 4
11 июн 18, 18:23    [21484816]     Ответить | Цитировать Сообщить модератору
 Re: Как обрaботать NULL в подзапросе, подскажите пожалуйста  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 522
 		Пардон,
в конце m.CategoryID, 4 случайно скопипастил.
11 июн 18, 18:28    [21484820]     Ответить | Цитировать Сообщить модератору
 Re: Как обрaботать NULL в подзапросе, подскажите пожалуйста  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
palladin600
iap
А для таких данных вам результат нравится?

Тут всё равно надо "чистить" результат на клиенте, а, собственно, этого и хочу избежать, т.к. массив сразу падает на лист Excel. Но если есть NULL, то возникает исключение.

Пока обернул в такой вариант, чтоб работала не стояла:
cast(ISNULL((select rel.Comment as 'data()' 
		FROM @Subjects AS s2 INNER JOIN @Relations as rel ON s2.ID = rel.SubjectID AND rel.[CreatorID] = 2 
		WHERE s.id = s2.id order by rel.[CreatorID] for xml path('')),'') as nvarchar(255)), m.CategoryID, 4
Достаточно так:

(select rel.Comment
		FROM @Subjects s2 JOIN @Relations rel ON s2.ID = rel.SubjectID AND rel.[CreatorID] = 2 
		WHERE s.id = s2.id order by rel.[CreatorID] for xml path(''), TYPE).value('.','nvarchar(255)')
11 июн 18, 18:33    [21484826]     Ответить | Цитировать Сообщить модератору
 Re: Как обрaботать NULL в подзапросе, подскажите пожалуйста  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 522
iap
Достаточно так:

кстати, не сработало. Попались данные с NULL'ом. Так вот Null и вернуло, вместо пустой строки.
21 июн 18, 14:01    [21509703]     Ответить | Цитировать Сообщить модератору
 Re: Как обрaботать NULL в подзапросе, подскажите пожалуйста  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
palladin600
iap
Достаточно так:

кстати, не сработало. Попались данные с NULL'ом. Так вот Null и вернуло, вместо пустой строки.
Весь результат обработать ISNULLом, да и дело с концом
21 июн 18, 14:05    [21509717]     Ответить | Цитировать Сообщить модератору
 Re: Как обрaботать NULL в подзапросе, подскажите пожалуйста  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
palladin600
iap
Достаточно так:

кстати, не сработало. Попались данные с NULL'ом. Так вот Null и вернуло, вместо пустой строки.
Попробуйте в текст вставить символы "&", "<", ">" и т.п. и сравните ваш вариант и мой.
21 июн 18, 14:08    [21509722]     Ответить | Цитировать Сообщить модератору
 Re: Как обрaботать NULL в подзапросе, подскажите пожалуйста  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 522
 		iap, можно ли вообще здесь избавиться от подзапроса, который через xml вытаскивает? Просто данные из поля Comment получить. Это ж обычное поле, для чего тут xml?
21 июн 18, 16:15    [21510154]     Ответить | Цитировать Сообщить модератору
 Re: Как обрaботать NULL в подзапросе, подскажите пожалуйста  [new]
Посетитель
Member

Откуда:
Сообщений: 1249
 		palladin600,

у вас, похоже, могут быть несколько строк в @relations по одному изделию
а подзапрос их склеивает.

если не может быть несколько строк, то можно заменить подзапрос на left join

возможно, изначально не было фильтра по rel.[CreatorID] = 2, это объяснило бы наличие order by
21 июн 18, 16:24    [21510177]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить