2005年10月20日

ANDとOR

僕はSQL文のWHERE句の中でANDやORを使うときはわかりやすいように大体括弧を付けてるんだけど、そのせいで他人の書いたSQL文を把握するのみちょっと時間がかかった‥‥。

どういうSQL文かというと↓みたいなの。

SELECT * FROM TAB1
WHERE COL1 = 'A' AND COL2 = 'B' OR COL3 = 'X' AND COL4 = 'Y'


あれ?ANDとORの評価の順番はどうなるんだっけ?と言った感じ。
普段意識してないと、やっぱりちょっと迷う。

正解はAND→ORの順。

つまり↑のは↓と同義。

SELECT * FROM TAB1
WHERE (COL1 = 'A' AND COL2 = 'B') OR (COL3 = 'X' AND COL4 = 'Y')


まあ、実際同義だとしても後者の書き方がやっぱり分かりやすいよな‥‥。
posted by あおべえ at 23:16 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年09月04日

ワイルドカードとエスケープ

SQL文でLIKE演算子を使用する場合のワイルドカード文字。

任意の一文字は「_」、任意の複数文字は「%」。

では、これらの「_」「%」という文字そのものを検索したい場合は?

3年以上データベースに触れてきたけど、今までそんな機会がなかったので知らなかったけど、その場合はエスケープ文字を使用すればOK。

C#では「\」だけど、SQL文では任意の文字を指定可能。

SELECT * FROM TAB1 WHERE COL1 LIKE '%\%' ESCAPE '\'

↑のSQL文だとCOL1の末尾に「%」が付くレコードを取得できる、といった感じ。

ちなみにこのエスケープの仕様はSQL Server,Oracle,DB2等、メジャーなDBではみんな同じらしい。

続きを読む
posted by あおべえ at 23:17 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年08月24日

DB2のバックアップ

DB2のコントロール・センターでバックアップを行うと、自動的に次のようなSQL文を発行してくれる。

CONNECT TO SAMPLE;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;
BACKUP DATABASE SAMPLE TO "C:\temp" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
CONNECT TO SAMPLE;
UNQUIESCE DATABASE;
CONNECT RESET;


順番としてはデータベースの静止→バックアップ→静止解除なんだけど、「BACKUP DATABASE 〜」で失敗すると、静止状態のままになる。

その状態のまま、再度コントロール・センターでバックアップを実行しても、発行されるSQL文は上記のまま。
で、静止状態のデータベースに対して、「QUIESCE DATABASE 〜」が発行されることになり、エラーになってしまう。

静止解除を行ってから再度バックアップすればいいだけの話だけど、データベースが静止状態なら、発行するSQL文変えてくれればいいのに‥‥。
posted by あおべえ at 00:37 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年08月12日

ダミーテーブルの話

Oracleではシステム日付を取ってくるときは↓のようなクエリ。

■Oracle
SELECT SYSDATE() FROM DUAL;

DUALといういわゆるダミーのテーブルを使う。
で、最近DB2を触ったんだけど、DB2では↓のようになるらしい。

■DB2
SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY1;

じゃあ、SQL Serverはというと↓。

■SQL Server
SELECT GETDATE()

テーブルから値を取得するのでなければ、FROM以降はいらないという特殊仕様。

実はDB2でも似たようなことが出来たり。

■DB2
VALUES CURRENT DATE;

VALUESはSQL ServerのSELECTとFROMなしのクエリと似てるけど、実は微妙に違う。

■SQL Server
SELECT '1','2'

■DB2
VALUES '1','2'

SQL Serverのクエリは1行2列を返すけど、DB2は2行1列を返す。
ただし、↓のようにすると、1行2列になる。

■DB2
VALUES ('1','2')

で、↓は2行2列。

■DB2
VALUES ('1','2'),('3','4')

ちなみに、VALUESを使ったときに列に別名をつける方法が分からんかった。
どうやるんだろ?それとも出来ないのか?
出来ないなら、SYSIBM.SYSDUMMY1との使い分けは結構重要?
posted by あおべえ at 00:09 | TrackBack(1) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年08月07日

showModalDialogの罠

JavaScriptのshowModalDialogでウィンドウを呼び出して処理を実行させようとしたんだけど、どうもうまく動かない。
ダイアログ内のformタグでtargetを"_self"にしてるにも関わらず、submitすると別ウィンドウが立ち上がる。
で、調べた結果、そういう動きになるものらしい。

回避方法はダイアログ内にフレームを作って、本来呼び出したいファイルをそのフレーム内に表示し、その中でsubmitすること。
簡単にできる方法でよかった‥‥。
posted by あおべえ at 00:21 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年07月11日

float型の謎

SQLServerのクエリアナライザで、以下のクエリを流してみる。

SELECT CAST(1.025 AS float)

結果は‥。

1.024999999999

えーと、文系出身の僕の頭なりに理解しようと努力してみると。
つまり浮動小数点数は概数だから、1.025のようにたまに正確に表現できない数値もあるということか?

まあ、ここまではいいとして。
float型の列を持つテーブルを作成し、1.025を挿入してクエリアナライザでSELECT文流すと、上と同じ結果になるのに、Enterprise Managerでテーブルを開くと、ちゃんと1.025って表示されるんだよね。
で、.NETで簡単なプログラム作って、float型の列を持つテーブルに値を挿入、SELECT文で値を取得、ってやっても同様に前述の現象は確認できず。
ついでに、C#のDouble型で同様の現象が起きるか試してみても発生しないし。
うーん、これってクエリアナライザがおかしいのか?
posted by あおべえ at 21:59 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年06月04日

標準偏差を求める集計関数

偏差値を出すクエリを書きたいな、と思ったんだけど、偏差値の求め方は↓の通り。

偏差値 = (偏差 / 標準偏差) * 10 + 50

しかし、標準偏差は偏差の算術平均(分散)の正の平方根で、偏差はある値と平均の差だから、[値と平均の差]の平均を求めないといけない。
ということは集計関数を含むサブクエリを2つ含めるのか?面倒くさいなぁ、と思ってたら、標準偏差を求める集計関数があった。うーん、初めて知ったよ。

OracleではSTDDEV、SQLServerではSTDEVで求められる。DB2でもSTDDEVは使えるらしいけど、母集団標準偏差になるらしい。
ちなみにOracleでの母集団標準偏差はSTDEV_POP、SQLServerでの母集団標準偏差はSTDEVP。
それから、OracleにはSTDDEV_SAMPというのもあって、これは標本標準偏差になるらしい。

と、ここまで書いておいて、標本標準偏差とか母集団標準偏差(母標準偏差?)の違いとか、よくわからんのだが‥‥。
そもそも標準偏差と言った場合、一般的には標本標準偏差なのか母集団標準偏差なのか、それともその2つとも別なのか?それすらわからん。
一応OracleのSTDDEVの結果とSTDDEV_SAMPの結果は一致したけど、STDDEVが標準偏差を求める関数と言うのは、手元のSQLのリファレンスにそう書いてあっただけだから、標準偏差=標本標準偏差とも言い切れないし。
とりあえずSTDEVとSTDEVPの違いは、SQLServerのBooksOnlineで確認できた。それによると、バイアスをかけたか、かけてないかの違いらしい。
じゃあ、バイアスって何?って話だけど、まあ、専門外なんで、こんなところで‥‥。
posted by あおべえ at 12:42 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年06月01日

意外と盲点?

@
SELECT *
FROM TAB1 T1
LEFT OUTER JOIN TAB2 T2
ON T1.COL1=T2.COL1
WHERE T2.COL2=1


A
SELECT *
FROM TAB1 T1
LEFT OUTER JOIN TAB2 T2
ON T1.COL1=T2.COL1
AND T2.COL2=1


@とA、似てるようだけど結果は異なる。
意外と気にしないで書いてしまいそうだけど、@は実質は外部結合にならないんだよね。

あ、僕は間違えてないよ!
posted by あおべえ at 23:07 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年05月25日

1レコード当たりの最大バイト数

警告: テーブル 'XXXXXX' が作成されましたが、最大行サイズ (99999) が行ごとの最大バイト数 (8060) を超えています。このテーブルの行の INSERT または UPDATE は、結果の行の長さが 8060 バイトを超える場合は失敗します。

SQL Serverでテーブルを作成したときに出た警告。
うーん、今まで意識したことなかったけど、行ごとの最大バイトは8060バイトだったのか。

じゃあ、Oracleではどうなんだろう?
と思って、いつもなら調べるところなんだけど、今日は疲れてたので挫折。
そのうち調べるか‥‥‥。
posted by あおべえ at 23:50 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年05月23日

VIEWとGROUP BY

VIEWからSUMを使って集計するSQL文を書いてたんだけど、どうもレスポンスが芳しくない。
似たような構造のテーブルを使った、似たようなVIEWに、似たようなクエリを投げるとそこそこ早いのに。

実行計画を確認したら原因が判明。

VIEWの中でGROUP BYを使ってた。
VIEWの中でGROUP BYを使ってると、当然GROUP BYによるソートとかが発生するので、その分遅かったらしい。GROUP BYしたVIEWに対して、さらにGROUP BYを使った集計って、VIEW内のGROUP BYは意味ないのに(サブクエリを使う場合は例外か?)、何で僕はこんなVIEW作ったんだろう?久しぶりに恥ずかしい失敗をした感じ‥‥。

ちなみにこれはOracleの話だけど、当然SQL Serverでも同様。
posted by あおべえ at 22:47 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年05月19日

LEFT、RIGHTとSUBSTRING

最近仕事ネタ書いてなかったなぁ。
まあ、ネタになるようなことがないので仕方ないけど。

今日は小ネタが入ったので、久しぶりに仕事ネタ。

SQLServer2000のLEFT、RIGHT関数とSUBSTRING関数。
文字列を切り出すという意味では似たような機能だけど、実は微妙に性質が違うらしい。

■LEFT、RIGHT関数の場合
SELECT LEFT('ABC',1)→OK
SELECT LEFT(123,1)→OK

■SUBSTRING関数の場合
SELECT SUBSTRING('ABC',2,1)→OK
SELECT SUBSTRING(123,2,1)→NG

SUBSTRING関数は引数にきちんと文字列を入れないとダメらしい。
対してLEFT、RIGHT関数は暗黙的に文字列に変換できるデータであれば問題なし。
実際Books Onlineで確認しても、LEFT,RIGHT関数の引数の説明には暗黙的にvarchar型に変換できるデータ型である必要があると書いてあるのに対して、SUBSTRING関数の引数の説明には何の説明もない。説明がない=文字データじゃなきゃダメってのはわかりづらい気もするけど、むしろ勝手にデータ型を変換してくれるLEFT、RIGHT関数が例外と考えるべきなんだろうね。

ちなみに、当たり前だけど、下のように変換すればもちろんエラーにはならず。
SELECT SUBSTRING(CAST(123 AS CHAR(3)),2,1)
SELECT SUBSTRING(CONVERT(VARCHAR(3),123),2,1)


では、Oracleではどうなのか?
SUBSTRING関数と同等の機能と思われるSUBSTR関数を使った↓のようなSQL文で実験。

SELECT SUBSTR(123,2,1) FROM DUAL

結果はエラー発生せず(Oracle9iの場合)。

しかし、個人的にはこういう暗黙の変換というのは使わない方がいいとは思うけど。いろんな意味で。
posted by あおべえ at 23:26 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年04月18日

UNIONとUNION ALL

@
SELECT * FROM TAB1
UNION ALL
SELECT * FROM TAB2


A
SELECT * FROM TAB1
UNION
SELECT * FROM TAB2


上のSQL文、どっちの早いかというと@。返ってくる行が同じでも。
至極当たり前の話なんだけど、Aは重複を排除するためにソートが入るから。
(@で返ってくる行がAで返ってくる行より極端に多かったり、ネットワーク環境によってはAが早くなる可能性もなくはないか?)

いや、恥ずかしながら、つい最近まで意識したことなかったよ‥‥。

ところで、上記の話はOracleもSQL Serverも同じだけど、SQL Serverで下のSQL文を実行したら、UNIONの操作がSort/Distinct Sortではなく、Merge Join/Unionになってた。オプティマイザが働いた?

SELECT 1
UNION
SELECT 0
UNION
SELECT 2
posted by あおべえ at 23:06 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年04月05日

テーブルの構造だけコピーのメモ

あるテーブルと同じ構造を持っていて、レコード件数が0のテーブルを作る方法のメモ。

■Oracle
CREATE TABLE TAB1_COPY
AS SELECT * FROM TAB1 WHERE 1<>1


■SQL Server
SELECT * INTO TAB1_COPY FROM TAB1 WHERE 1<>1

結果が0件のクエリからテーブル作成して、データ型がきちんと反映されるか、実際試すまでちょっと不安だったけど、問題ないみたい。
しかし、OracleもSQL Serverも構文は違うくせに動作は似てるんだなぁ。
え?OracleならEnterprise Managerの類似作成を使えばいいし、SQL ServerもEnterprise ManagerやクエリアナライザからDDLを生成できるじゃないかって?
いや、全くその通り‥‥。でも、まあ、OracleのEnterprise Managerは重いし‥‥。
posted by あおべえ at 23:17 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年03月30日

外部結合とORオペランド

SELECT * FROM A,B
WHERE A.A01=B.B01
OR A.A02=B.A02(+)


ORA-01719: ORまたはINオペランドの中で外部結合演算子(+)は使用できません

↑はORACLEで実行したSQL文とその結果(もちろんSQL文は適当なのに変えてます)。
外部結合の結合条件の中ではORは使えないらしい。
こういう場合は↓のようなSQL文にしろと、エラーメッセージガイドに書いてありました。

SELECT * FROM A,B
WHERE A.A01=B.B01
AND NOT (A.A02=B.A02)
UNION ALL
SELECT * FROM A,B
WHERE A.A02=B.A02(+)


SQL Serverはどうなんだろ?

SELECT * FROM A,B
WHERE A.A01=B.B01
OR A.A02*=B.A02


テーブル 'B' が、外部結合句の内部メンバです。テーブルが通常の JOIN 句にも使用されている場合は許可されません。

やっぱりだめっぽい。
しかし、↓はOK。ORACLEではNGなのに。

SELECT * FROM A,B
WHERE A.A01*=B.B01
OR A.A02*=B.A02


しかし、↑の結果は↓の@とは異なる結果が。おそらくAと同じ結果になるのかな?

@
SELECT * FROM A,B
WHERE A.A01*=B.B01
UNION
SELECT * FROM A,B
WHERE A.A02*=B.A02


A
SELECT * FROM A
LEFT OUTER JOIN B
ON A.A01*=B.B01
OR A.A02*=B.A02


WHERE句で結合条件する場合は、本当に意図通りの結果が返ってくるか、ちゃんと確認しないといけないなぁ。
まあ、僕はJOIN句のがわかりやすいんで、JOIN句で結合してるけど。

続きを読む
posted by あおべえ at 22:56 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年03月24日

DTSパッケージをC#から呼び出す

仕事とは関係ないけど、SQL Server 2000のDTSパッケージをC#から呼び出す方法を調べた。
何となく呼び出せるだろうな、とは思いつつも具体的にはわからない。
とりあえずCOMコンポーネントで"Microsoft DTSPackage Object Library"ってのがあったので、これを使ってみる。
インテリセンスを利用して、クラスやメソッドを調べた結果、Package2Classというクラスが使えそう。おそらく_Package_LoadFromSQLServerメソッドでパッケージをロードして、_Package_Executeメソッドで実行だろう、とういことで試してみる。
結果、意外とすんなり呼び出せてしまった。
でも、問題が。
_Package_LoadFromSQLServerメソッドのパラメータは以下の通り。

void Package2Class._Package_LoadFromSQLServer(string ServerName, string ServerUserName, string ServerPassword, DTS.DTSSQLServerStorageFlags Flags, string PackagePassword, string PackageGuid, string PackageVersionGuid, string PackageName, ref object pVarPersistStgOfHost)

ServerName、ServerUserNameとかはともかく、FlagsとpVarPersistStgOfHostの意味がわからん。
もうちょっと調べないと実際の開発には使えないなぁ。
posted by あおべえ at 23:37 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年03月02日

OracleとSQL Serverの文字列リテラルの制限

Oracleでリテラルの文字列って何文字までOKだっけ?ということで検証。
といっても、滅茶苦茶長いリテラルを用意してエラー起こして、エラーコードからリファレンスを参照したんだけど。
まあGoogleで調べればわかったかもしれないけど、「リテラル」という単語を思い出せなかったので‥‥‥。
↓が返ってきたエラー。

ORA-01704: 文字列リテラルが長すぎます

制限は4,000文字らしい。
そういえば、数値はどうなんだろう?ということで同様の方法で検証。
返ってきたエラーは↓。

ORA-01768: 数値列が長すぎます。

制限は255文字らしい。カンマ、符号、小数点含む。
それ以上は科学計算表記法を使ってくれとのこと。
科学計算表記法はこんなやつと思われる。→1.55E9

ちなみに、短い文字列を結合して('XXX‥‥XXX'||'XXX‥‥XXX')、長い文字列にしたらOKなんだろうか?

ORA-01489: 文字列を連結した結果、長さが最大長を超過しました

なるほど。
SQL Serverはどうなんだろ?
↓のようなSQL文で検証。

SELECT GETDATE()
WHERE 'a'='aaaaaaaa(中略)aaaaaaaa'


どうやら8,000文字まではOK。
8,001文字以上は下記のエラー。

TEXT、NTEXT および IMAGE データ型は、IS NULL または LIKE 演算子を使用しているときを除いて比較または並べ替えできません。

つまり、8,000文字まではchar型かvarchar型で、それ以上の長さになるとtext型になるわけね。
↓はSQL ServerのBooks Onlineより。
char
8,000 文字以内の固定長の Unicode 以外の文字データを格納するデータ型です。
varchar
8,000 文字以内の可変長の Unicode 以外のデータを格納するデータ型です。
text
2^31 - 1 (2,147,483,647) 文字以内の可変長の Unicode 以外のデータを格納するデータ型です。
ちなみに8,001文字以上でも↓のSQLはOK。

SELECT GETDATE()
WHERE 'a' LIKE 'aaaaaaaa(中略)aaaaaaaa'


ちゃんと今日の日付が返ってきました。
‥‥‥って、「'a' LIKE 'aaaaaaaa(中略)aaaaaaaa'」はFALSEじゃないのか?
それとも僕がLIKE演算子の意味を間違えてるのか?
ということで下記のSQL文で検証。

@
SELECT GETDATE()
WHERE 'a' LIKE 'ab'


A
SELECT GETDATE()
WHERE 'a' LIKE CAST('ab' AS text)


@のSQL文の結果は0行。
AのSQL文の結果は1行。
うん、間違ってないな。
text型は特殊なのか?text型なんて使ったことないからなぁ‥‥。
さすがにそこまで調べてる時間はなかったので今後text型を使う機会ができたら調べてみるか。
続きを読む
posted by あおべえ at 23:40 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

SQL Server2000で1ヶ月前の日付を取得するSQL文メモ

1ヶ月前
SELECT DATEADD(mm,-1,GETDATE())続きを読む
posted by あおべえ at 22:51 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年03月01日

SQL Server のSQL照合順序メモ

CaseSensitivity
CIを指定すると大文字小文字は区別されず、CSを指定すると大文字小文字が区別される。

AccentSensitivity
AIを指定するとアクセントは区別されず、ASを指定するとアクセントが区別される。

SはSensitiveとして、IはInsensitiveか?
posted by あおべえ at 23:13 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年02月15日

Oracleに接続できない!

今日、Oracleに接続するため、ユーザ名とパスワードを入力してたんだけど、どうしてもログインできない。
何でログインできなかったかと言うと、ユーザ名を「sa」と入力してました。それはSQL Serverだよね‥‥‥。
それに気付くのまで十数回「sa」と考え得るパスワードを入力してたよ。もちろん「system」にしたらすぐログインできました。
いやー、最近OracleとSQL Serverを交互に使ってるような感じだったのですっかり忘れてた。
posted by あおべえ at 00:11 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

2005年02月04日

久しぶりのAnalysis Services

久しぶりにSQL ServerのAnalysis Servicesを使う。
1年ぶり以上?
かなり使い方を忘れてました。
まあ使ってるうちにそこそこ思い出してきたけど。
っていうか、ここ最近ずっとOracleだったから、SQL ServerでSQL文を書くのにも戸惑った。
NUMERICをVARCHARに変換するのにCONVERTは割りとすぐ思い出したけど、あれ?あと一個なんだっけ?って考えちゃって、CASTを思い出すのに30秒ぐらいかけてしまった。
しかし、使ってない知識はやっぱり錆びれちゃうね。Analysis Servicesもせっかくいろいろ調べたのに、まだ思い出してないことありそうだし。
posted by あおべえ at 23:57 | TrackBack(0) | 仕事 | このブログの読者になる | 更新情報をチェックする

広告


この広告は60日以上更新がないブログに表示がされております。

以下のいずれかの方法で非表示にすることが可能です。

・記事の投稿、編集をおこなう
・マイブログの【設定】 > 【広告設定】 より、「60日間更新が無い場合」 の 「広告を表示しない」にチェックを入れて保存する。


×

この広告は1年以上新しい記事の投稿がないブログに表示されております。