Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Хинт для where  [new]
michelI
Member

Откуда:
Сообщений: 25
Всем доброго дня.

create table a (f nchar(1))
insert a values ('1')
insert a values ('2')
insert a values ('a')
select f from (select f from a where ISNUMERIC(f)=1) b where cast(f as int)>0


Ошибка преобразования nchar в int.

Как указать оптимизатору, что надо вначале выполнить подзапрос, а потом фильтровать результат?

Спасибо.
31 янв 14, 13:00    [15499947]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
Добрый Э - Эх
Guest
СТЕ ?
31 янв 14, 13:01    [15499954]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
michelI
Member

Откуда:
Сообщений: 25
Нашел принудительный порядок join а для моего случая не смог разобраться.
31 янв 14, 13:03    [15499970]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
Добрый Э - Эх
Guest
michelI,

как принудительный порядок JOINа связано с порядком применения предикатов фильтрования?
31 янв 14, 13:04    [15499980]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
Добрый Э - Эх
Guest
Как вариант хака - можно добавить TOP в подзапрос
31 янв 14, 13:08    [15500032]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
michelI
Member

Откуда:
Сообщений: 25
как в моем случае использовать принудительный порядок предикатов фильтрования?
31 янв 14, 13:10    [15500041]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74930
DECLARE @a TABLE(f nchar(1))
insert @a values ('1')
insert @a values ('2')
insert @a values ('a')
select f from (select f + '' AS f from @a where ISNUMERIC(f)=1) b where cast(f as int)>0
31 янв 14, 13:12    [15500056]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
michelI
Member

Откуда:
Сообщений: 25
Добрый Э - Эх
Как вариант хака - можно добавить TOP в подзапрос

Не совсем понял как это скажется, но добавил
select f from (select top 100 percent f from a where ISNUMERIC(f)=1) b where cast(f as int)>0

не сработало
31 янв 14, 13:12    [15500058]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
o-o
Guest
michelI,

никак не задать.
традиционно обходится case-ом:
declare @a table (f nchar(1))
insert @a values ('1')
insert @a values ('2')
insert @a values ('a')

select case 
          when ISNUMERIC(f)=1 then f
       end as f
from @a
where case 
          when ISNUMERIC(f)=1 then f
       end >0
31 янв 14, 13:13    [15500061]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
_djХомяГ
Guest
Сказали уже CTE или
select *
from
(select f,IsNumeric(f) as Flag from a) t
where flag=1
31 янв 14, 13:14    [15500067]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
michelI
Member

Откуда:
Сообщений: 25
Спасибо pkarklin, получилось.
31 янв 14, 13:15    [15500072]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
Добрый Э - Эх
Guest
michelI
Добрый Э - Эх
Как вариант хака - можно добавить TOP в подзапрос

Не совсем понял как это скажется, но добавил
select f from (select top 100 percent f from a where ISNUMERIC(f)=1) b where cast(f as int)>0

не сработало
насколько я помню, 100 percent игнорится с некоторых пор. нужно циферками заведомо сильно большой топ задать. как пример...
31 янв 14, 13:15    [15500075]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
o-o
Guest
_djХомяГ
Сказали уже CTE

сами-то пробовали CTE?
оно отлично раскрывается в то же, что и было.
ошибка на месте.
31 янв 14, 13:19    [15500097]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Добрый Э - Эх
michelI
пропущено...

Не совсем понял как это скажется, но добавил
select f from (select top 100 percent f from a where ISNUMERIC(f)=1) b where cast(f as int)>0


не сработало
насколько я помню, 100 percent игнорится с некоторых пор. нужно циферками заведомо сильно большой топ задать. как пример...
Сейчас сервер кушает TOP(SELECT 100)PERCENT
Но всё, что написано в этой теме, неправильно. Уж сколько было про это на форуме:

преобразовать string в int
31 янв 14, 13:22    [15500119]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Собственно, как и раньше, ISNUMERIC() - недостаточно!
31 янв 14, 13:24    [15500129]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
Диклевич Александр
Member

Откуда:
Сообщений: 612
а версия сервера?
а то в 2012 есть TRY_CONVERT
31 янв 14, 13:27    [15500149]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
_djХомяГ
Guest
o-o
_djХомяГ
Сказали уже CTE

сами-то пробовали CTE?
оно отлично раскрывается в то же, что и было.
ошибка на месте.



declare @t table (a varchar(10))
insert into @t 
select '1'
union all
select 'a'
union all
select 'b'
union all
select '22 '


;with cte 
as
( 
select a,Isnumeric(a) as Flag from @t
)
select * from cte where Flag=1 

1	1
22 	1
31 янв 14, 13:28    [15500158]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
_djХомяГ
o-o
пропущено...

сами-то пробовали CTE?
оно отлично раскрывается в то же, что и было.
ошибка на месте.



declare @t table (a varchar(10))
insert into @t 
select '1'
union all
select 'a'
union all
select 'b'
union all
select '22 '


;with cte 
as
( 
select a,Isnumeric(a) as Flag from @t
)
select * from cte where Flag=1 

1	1
22 	1


а теперь добавь нужное ТС-у условие
and cast(a as int)>0
31 янв 14, 13:34    [15500208]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
michelI
Как указать оптимизатору, что надо вначале выполнить подзапрос, а потом фильтровать результат?

Спасибо.

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

Например на 2008R2 наблюдается такое поведение:
-- No Error
declare @input int = 0;
SELECT calculated_value = CASE WHEN @input <= 0 THEN 0 ELSE LOG10 (@input) END;
go

-- Error
ALTER FUNCTION dbo.test_case_short_circuit (@input INT)
RETURNS TABLE
AS
RETURN (
    SELECT calculated_value =
        CASE
            WHEN @input <= 0 THEN 0
            ELSE LOG10 (@input)
        END
);
GO
SELECT * FROM dbo.test_case_short_circuit (0);
GO


Взято из блога самих MS: Don’t depend on expression short circuiting in T-SQL (not even with CASE)
Должны были пофиксить в следующем релизе.

Вторая ситация:
DECLARE @i AS INT = 1; 
SELECT CASE @i WHEN 1 THEN NULL ELSE MIN(1/0) END; 

Фиксить это дело не будут. Но благодаря стараниям Paul White (connect item) и Aarorn Bertrand (connect item) пофиксили документацию, теперь в разделе примечания там сказано:

CASE (Transact-SQL)
The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement.

CASE (Transact-SQL)
31 янв 14, 13:39    [15500255]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
michelI
Всем доброго дня.

create table a (f nchar(1))
insert a values ('1')
insert a values ('2')
insert a values ('a')
select f from (select f from a where ISNUMERIC(f)=1) b where cast(f as int)>0


Ошибка преобразования nchar в int.

Как указать оптимизатору, что надо вначале выполнить подзапрос, а потом фильтровать результат?

Спасибо.


Можно так: SELECT f FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'SELECT f FROM a WHERE ISNUMERIC(f) = 1') AS b WHERE CAST(f AS int) > 0 - у оптимизатора есть время отдохнуть :)
31 янв 14, 14:44    [15500772]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
hack от pkarklina самый лаконичный
31 янв 14, 14:47    [15500797]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
o-o
Guest
NickAlex66
Можно так: SELECT f FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 'SELECT f FROM a WHERE ISNUMERIC(f) = 1') AS b WHERE CAST(f AS int) > 0 - у оптимизатора есть время отдохнуть :)


о, ну так отдыхают ВСЕ, особенно если

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
31 янв 14, 14:52    [15500842]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
o-o,

А вкл 'Ad Hoc Distributed Queries' не судьба?
31 янв 14, 15:04    [15500925]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
o-o
Guest
NickAlex66,

а че, у всех есть права?
я ни разу не админ, если что
31 янв 14, 15:14    [15500995]     Ответить | Цитировать Сообщить модератору
 Re: Хинт для where  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
o-o,

Может оно и к лучшему :)
31 янв 14, 15:18    [15501028]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить