order by句でcase式

order by句でcase式が使えることに気づいていない人が割と多い。
例えば以下の例では、通常は、
A, B, C, SA
と並ぶところを、
SA, A, B, C
と並び変えている。

order by 
  case ランク when 'SA' then 1 else 2 end,
  case ランク when 'A' then 1 else 2 end,
  case ランク when 'B' then 1 else 2 end,
  case ランク when 'C' then 1 else 2 end

パフォーマンス的には宜しくないので、抽出されるレコードが少ない場合などに活用しましょう。
通常は、ちゃんと表示順を定めたテーブルを用意し、その表示順でソートしましょうね。

◆総括
良い子のみなさんはあんまりやっちゃダメですよ。
ただ、ちょっとしたテストなんかの時とか頭の隅に置いておくと役に立つ場面があるかもしれませんね。

OracleとSQL ServerのSQLを同時に書いているとついnullの処理でうっかりしてしまう・・・

SQL Serverで、nullの場合に他の値に置き換える関数は、isnull。Oracleは、nvl。
まぁ、これは簡単なことなんだが、nullの時に空文字に置き換えようと、SQL Serverで以下のように書いていると

isnull(Ａ項目, '')

つい、Oracleでも、

nvl(Ａ項目, '')

と書いてしまう。
これは全く意味がなくて、Ａ項目がnullならnullにnvlで置き換えていることになる(笑)
Oracleは空文字とnullが同じ扱いなので、つまり、空文字はnullになってしまうからである。
よって、

nvl(Ａ項目, ' ')

と、nullの時には空白に置き換えなければならない。

この、

nvl(Ａ項目, '')

は、わかりにくいバグで、実行時に初めて発覚するからやっかいである。
注意！注意！　自戒の意味を込めて。

SQL Server スキーマ名を省略したらアクセスできなくなった

以下のような簡単なSQLがアクセス出来なくなった。

select * from テーブルＡ

「オブジェクト名 'テーブルＡ' は無効です。」といったエラーが出る。

以下のようにスキーマ名を付けるとちゃんとアクセスできる。

select * from hoge.テーブルＡ

もちろん、アクセスしているユーザーは、hogeスキーマをデフォルトにしている。
悩んでネットを探すと、答えが簡単に見つかった。感謝！

結論から言えば、SQL Serverにアクセスしてるユーザーが、「SysAdmin」サーバーロールに属しているのが原因だった。
これに属していると、スキーマ名はdboに強制的にされてしまうらしい。

SQLでスキーマ名（所有者名）の修飾無しでテーブル参照したい
https://oshiete.goo.ne.jp/qa/4681290.html

普段は必ずスキーマを指定するのでこのような問題にぶつかったことはなかったのだが、Visual Studio 2019でTableAdapterを生成した際に自動生成されるInsert文で、Insertした後の値を取るための Select文のテーブル名にスキーマ名が修飾されておらず、今回の問題が発生した。
このスキーマ文が付かないのはバグっぽいなぁ。

今回は、SysAdminを外して解決。
まぁ、そもそも一般ユーザーにSysAdminは要らないからね。
今回、SysAdminを誤って付けちゃったのが原因で、これまでもTableAdapterが生成するSelect文にはスキーマ名が修飾されていなかったのかもしれないなぁ。

SQL ServerからOracleへのリンクサーバーのパフォーマンス

Oracleへのリンクサーバーのテーブルを読む際に、とんでもなく遅いパフォーマンスのテーブルに出会った。
１つのテーブルから2件のレコードを読むだけなのに１分20秒もかかる。
一方で、６つぐらい外部結合していても数秒で表示されるクエリもある。
レコード数は前者は約２１０万４千件、後者は約５万件である。この違いなのかもしれない。
上記のＳＱＬはドット表記やらフォーパート表記とか呼ばれたりするようだ。
いわゆるSQL ServerのSQL文でOracleから抽出する。どうもこの時にOracleでインデックスが使われなかったり、データをローカルに持っきて
そこでフィルターをかけて抽出されていると言った記事をいくつか見た。

で、解決策はOpenQueryを使う方法のようで、Accessで言えばパススルークエリのようなものらしい。
こちらはOracleにSQLを直接送ってOracleで処理してもらうので、Oracle用のSQLを書く必要がある。

このOpenQueryを試したところ、上記の１分２０秒かかっていたものが１秒もかからないぐらいで終わるようになった。

比較的件数の少ないテーブルだと、ドット表記とフォーパート表記はあまり変わらないのかもしれない。
事実、私のところでは今のところそのように感じている。
もう少し研究してみるが、面倒だがOpenQueryで書いておくとパフォーマンス的には間違いないのかもしれない。
ちなみにOpenQueryではパラメータが使えないので、文字列連結でSQL文を組み立てることになる。
よって、SQLインジェクションには注意する必要がある。

select * from openquery(リンクサーバー名, oracleのSQL文)

openquery内のリンクサーバー名は、４パートではなく２パートで構成されたものなので注意。
言い換えると、前者はドットが３つで４パート、後者はドットが１つで２パートである。

＃ちなみにSQL Serverへのリンクサーバーは今のところ高速に動作する。もう少し開発を進めていかないとわからないが・・・

System.IO.FileNotFoundException: 'ファイルまたはアセンブリ 'System.XmlSerializers, Version=4.0.0.0,・・・・・指定されたファイルが見つかりません。'

Visual Studio 2019に変えてから始めてだとおもうのだが、デバッグ実行すると以下の例外が発生した。

System.IO.FileNotFoundException: 'ファイルまたはアセンブリ 'System.XmlSerializers, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'、またはその依存関係の 1 つが読み込めませんでした。指定されたファイルが見つかりません。'

ネット調べてみると以下に答えがあった。

XmlSerializer giving FileNotFoundException at constructor
https://stackoverflow.com/questions/1127431/xmlserializer-giving-filenotfoundexception-at-constructor

ツール　→　オプション　→　デバッグ　→　一般　で、「マイコードのみを有効にする」にチェックを入れれば良い

そう言えば、以前にもこの設定やったような記憶があるなぁ。何となく・・・

【Oracle】NVL関数ではまる。NZLから返される値と比較する場合、末尾の空白を考慮する必要がある

NVL関数は、レファレンスによると、

NVL( 文字列 , 変換文字列 ), 文字列 がNULLの場合は、変換文字列を返します。

とある。いたって簡単かつ便利な関数である。
ところがである。これにchar(2)などの固定長の項目を適用すると、末尾の空白を含めて比較しなければならなくなる。

カラムＡがchar(2)だとすると、ここに、'Q 'という値が入っているとする。

カラムＡ ＝ ’Q'　はtrueになる。

しかし、

NVL(カラムＡ, '') = 'Q'　は、falseになってしまう。

NVL(カラムＡ, '') = 'Q ' と、Qの末尾に空白を付けるとtrueになる。

注意が必要だ。

OracleのSQLで、ERROR ORA-00907: 右カッコがありません。

久しぶりにOracleのSQLを書いていたのだが、以下のSQLで、
「ERROR ORA-00907: 右カッコがありません。」なんて謎のエラーが出た。

＃下記のSQLは実際のＳＱＬではなく、テーブル名や項目名を変え、かつ簡略化するために手動で書き換えてますので
タイポとかあったらすみません。

select  t.放送日, t.枠ＣＤ
from Ｑシート t
where t.放送日= '20200114' and トリガ = 'Q' and ＤＴ <> '' and
    (select トリガ from Ｑシート t1
        where t1.放送日= t.放送日 and t1.枠ＣＤ = t.枠ＣＤ and
             t1.イベントＳＥＱ =
                        (    
                            select min(イベントＳＥＱ) from Ｑシート qt
                                where qt.放送日= t.放送日 and d qt.枠ＣＤ = t.枠ＣＤ and
                                        qt.イベントＳＥＱ > t.イベントＳＥＱ
                                order by qt.イベントＳＥＱ
                        )
    )
    , '') = 'Q'
order by t.枠ＣＤ, t.イベントＳＥＱ

いや、ちゃんとカッコの整合性は取れてます。足りない右カッコなんてありません！
って、悩んだんだけど、結局、副問い合わせのorder by句がいらなかったんだよね。
まぁ、そりゃそうかって感じなんだけど、何でこんなにわかりにくエラーメッセージなんでしょうね。
おかげで３０分ぐらいは損をしてしまった・・・