トップ回答者
isnumericと使った複参照での疑問点

質問
-
SQL2008にて
文字と数字が混在しているテーブルからisnumeを用いて数字だけを取り出しその後9以上を取り出すサンプルで悩んでいます。
(1)は実行出来ますが (何故かsubstringで縛るとうまくいきました)
(2)は メッセージ 245、レベル 16、状態 1、行 2
varchar の値 'OU' をデータ型 int に変換できませんでした。 との エラーです
()T1 内で 数字だけを選択していますが、カッコ内のwhereが有効ではないのでしょうか?
drop table #aaaCREATE TABLE #aaa
(
aa varchar(10)
)
go
insert #aaa values ('10'),('OU')
go
select * from #aaago
-----(1)
select aa as a1
from (
select * from #aaa
where isnumeric(aa)=1
)T1
where convert(int,substring(aa,1,2))>=9go
------(2)
select aa as a2
from (
select * from #aaa
where isnumeric(aa)=1
)T1
where convert(int,aa)>=9
回答
-
中の実装まで知らないため何とも言えませんが、下記の(3)~(5)を見る限りオプティマイザが余計なお世話をしてくれていそうです。
(2)の場合、ほかに何にもしていないため、(3)と等価のような扱いを受けているようです。
せめて(4)のように評価順は同じまましてくれていると、エラーが出ないのですが……。
または、(1)や(5)のように、何か処理を入れてオプティマイザに最適化させないのも手かもしれません。
(余計な処理をする分遅くなりますが)。
最後に、型抜出だしのための前処理ということならば、別途テンポラリを作ってしまったほうが<del>速い</del>安全で保守性が高いかもしれません。------(3) SELECT aa AS a3 FROM( SELECT aa FROM #aaa )T1 WHERE CONVERT(int,aa)>=9 AND ISNUMERIC(aa)=1 go ------(4) SELECT aa AS a4 FROM( SELECT aa FROM #aaa )T1 WHERE ISNUMERIC(aa)=1 AND CONVERT(int,aa)>=9 go ------(5) SELECT aa AS a5 FROM ( SELECT aa FROM #aaa WHERE ISNUMERIC(aa)=1 )T1 WHERE CONVERT(int,CONVERT(varchar,aa))>=9 ------(6) SELECT aa INTO #bbb FROM #aaa WHERE ISNUMERIC(aa)=1 GO SELECT aa AS a6 FROM #bbb WHERE aa>=9 GO DROP TABLE #bbb GO
すべての返信
-
中の実装まで知らないため何とも言えませんが、下記の(3)~(5)を見る限りオプティマイザが余計なお世話をしてくれていそうです。
(2)の場合、ほかに何にもしていないため、(3)と等価のような扱いを受けているようです。
せめて(4)のように評価順は同じまましてくれていると、エラーが出ないのですが……。
または、(1)や(5)のように、何か処理を入れてオプティマイザに最適化させないのも手かもしれません。
(余計な処理をする分遅くなりますが)。
最後に、型抜出だしのための前処理ということならば、別途テンポラリを作ってしまったほうが<del>速い</del>安全で保守性が高いかもしれません。------(3) SELECT aa AS a3 FROM( SELECT aa FROM #aaa )T1 WHERE CONVERT(int,aa)>=9 AND ISNUMERIC(aa)=1 go ------(4) SELECT aa AS a4 FROM( SELECT aa FROM #aaa )T1 WHERE ISNUMERIC(aa)=1 AND CONVERT(int,aa)>=9 go ------(5) SELECT aa AS a5 FROM ( SELECT aa FROM #aaa WHERE ISNUMERIC(aa)=1 )T1 WHERE CONVERT(int,CONVERT(varchar,aa))>=9 ------(6) SELECT aa INTO #bbb FROM #aaa WHERE ISNUMERIC(aa)=1 GO SELECT aa AS a6 FROM #bbb WHERE aa>=9 GO DROP TABLE #bbb GO
-
http://otn.oracle.co.jp/forum/thread.jspa?threadID=35007126
OracleとPostgreSQLでは、case式で短絡評価を行うようですが、
SQLServerのcase式について明記してるのは探したけど見つからなかったですねぇwith work(aa) as( select '10' union select 'OU') select * from work where case when isnumeric(aa)=0 then 0 when convert(int,aa)>=9 then 1 end = 1; aa -- 10
- 回答の候補に設定 Aketi Jyuuzou 2010年7月14日 12:29