none
isnumericと使った複参照での疑問点 RRS feed

  • 質問

  • SQL2008にて

    文字と数字が混在しているテーブルからisnumeを用いて数字だけを取り出しその後9以上を取り出すサンプルで悩んでいます。

    (1)は実行出来ますが (何故かsubstringで縛るとうまくいきました)
    (2)は メッセージ 245、レベル 16、状態 1、行 2
    varchar の値 'OU' をデータ型 int に変換できませんでした。 との エラーです
    ()T1 内で 数字だけを選択していますが、カッコ内のwhereが有効ではないのでしょうか?


    drop table #aaa

    CREATE TABLE #aaa
      (
         aa     varchar(10)
      )
    go
      insert #aaa  values ('10'),('OU')
    go
    select * from #aaa

    go
    -----(1)
    select aa as a1
    from (
    select * from #aaa
     where isnumeric(aa)=1
     )T1
     where convert(int,substring(aa,1,2))>=9

     go
    ------(2)
     select aa as a2
    from (
    select * from #aaa
     where isnumeric(aa)=1
     )T1
     where convert(int,aa)>=9

    2010年7月13日 8:24

回答

  • 中の実装まで知らないため何とも言えませんが、下記の(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
    
    • 編集済み ChukiMVP 2010年7月14日 1:13 誤解を与えそうな表現を修正
    • 回答としてマーク yosshii 2010年7月14日 3:00
    2010年7月13日 16:52

すべての返信

  • 中の実装まで知らないため何とも言えませんが、下記の(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
    
    • 編集済み ChukiMVP 2010年7月14日 1:13 誤解を与えそうな表現を修正
    • 回答としてマーク yosshii 2010年7月14日 3:00
    2010年7月13日 16:52
  • Chukiさま

    ありがとうございました。

     

    2010年7月14日 3:02
  • 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
    
    
    2010年7月14日 12:29