ソフトウェア開発者の日常

こだわりなく書きたいことを書いていきます。

SQL

SQL:CASE types text and date cannot be matchedの原因と対策

手持ちのデータをSQLで加工していて、「CASE types text and date cannot be matched」というエラーが発生しました。 CASE types text and date cannot be matchedの原因 PostgreSQLに保存されているデータを取得するために、 SELECT name , url , CASE WHEN…

カラムの別名に大文字を使って失敗した経験

SQL

データベースにPostgreSQLを使っていて、カラム名に別名をつけた方がわかりやすいと感じる機会がありました。 例えば、 SELECT no FROM table1; のnoのままでは、プログラム上意味が分かりにくいと感じました。プログラムとしては、noカラムの値を、membersh…

PostgreSQL:特定の区切り文字で連結されて1カラムに入っているデータを、分割して複数のレコードとして取得する方法の補足

1年ほど前に書いたSQLの記事を改めて読んで、どのような動作になっているのかわかりにくい部分があると感じたので、補足する記事です。 ajya.hatenablog.jp元のデータは以下とします。 番号(no) 名前(name) 1 大沼 直輝 2 金谷 凌 3 青井 敬三/表 博康 4 八…

データベースのデータコピー

テーブル内で列をコピーテーブル内で列から列へコピーしたいときがあります。 今回初期設定した列が実は違っていたので、一括でコピーしたくなりました。 方法は以下のとおりでした。 UPDATE member SET newcol = oldcol WHERE id = 11; photo credit: Rahul…

SQL:データがあれば件数をカウントし、データがなければ0件として結果を取得する方法

SQL

作品に対する投票をするシステムがあります。 投票の結果のデータをお客さんに送ったところ、0件だった場合も0件のデータが欲しいとの連絡がありました。全作品に0件というデータがあり、そこに票を加算していれば簡単に算出できます。 投票のシステムは、票…

PostgreSQL:特定の区切り文字で連結されて1カラムに入っているデータを、分割して思い通りの順番に並べ替える方法

あるデータにおいて、当初は文字列の先頭と特定の文字の後に数字がついていました。 途中から仕様が変わって、文字列にはまったく数字が付かなくなり、以下のようなデータになりました。 番号(no) 名前(name) 1 大沼 直輝/金谷 凌/青井 敬三/表 博康 2 八…

PostgreSQL:特定の区切り文字で連結されて1カラムに入っているデータを、分割して複数のレコードとして取得する方法

既にデータベースに登録されているデータを元にして、新しいデータを作成しなければならなくなりました。元のデータを見ると、以下の表の様に特定の区切り文字(/)で連結されています。 番号(no) 名前(name) 1 大沼 直輝 2 金谷 凌 3 青井 敬三/表 博康 4 …

SQL:同じテーブルに格納されている値をサブクエリ(副問合せ)で結合して取得する

SQL

同じテーブルに値が2レコードとして保存されていて、クエリで1レコードとして取得する方法をすぐ忘れてしまうのでメモです。具体的なクエリは以下のとおりです。 SELECT table1.id AS table1_id, tmp.tmp_id FROM table1 LEFT OUTER JOIN ( SELECT id AS tmp…

Oracle:DATE型カラム用データの生成方法

Oracleにデータを挿入しようとしたところ、「ORA-01830: 日付書式の変換で不要なデータが含まれています。 」が表示されました。 SQL文は、 INSERT INTO users (column1, datecolumn1) VALUES ('1', '2016/02/04 23:59:59') です。 DATE型のカラムでエラーと…

結果が一定にならないため、再度同じデータが表示される現象が発生

SQL

わかってみれば単純な話ですが、すぐには気がつけませんでした。 photo credit: Parallels Desktop via photopin (license)同じデータが表示される現象お客さんからデータを見るためにページ送りをしていくと、49ページ目と50ページ目で、ほぼ同じデータが表…

SQL:UNIONで結合するデータを、結合前のデータごとにソートする方法

SQL

UNIONを使ったときのソートでちょっと悩んだので記録しています。 photo credit: SQL file in Text editor via photopin (license) colum1colum2sort_key 1000001 1000122 1000213 1000314 1000405 というデータを colum1colum2sort_key 1000001 1000122 100…

Postgre SQL:値の設定ルールが変わったために、ソートの条件を見直し

今年からルールが変わったために対応していました。 photo credit: therefromhere via photopin cc昨年まではあるカラムに対して、A-01~99/B-01~99/C-01~99という値を割り振られていました。 今年はお客さんの都合で、AA-CC-01~99/AA-DD-01~99/AB-CC-01…

Accessのリンクテーブルでは、カラム単位でコピーして更新ができない?

SQLでできるから問題はないのですが、更新できませんでした。 photo credit: Bureau de Jayne avant son départ via photopin (license)カラムの内容を別のカラムにコピーしたい新しく追加したカラムに、既存のカラムからコピーしたいときは、 UPDATE author…

PostgreSQL:条件式と文字列連結を使って、ハイフン(-)を付けた結果を取得できた

代表者と同行者を同時に登録するシステムがあります。 登録されたデータの出力時、縦並びにデータを出力していて、番号は同じ番号にしていました。 番号を同じにしたのは、同行者とわかりやすくするためです。 番号 名前 1 参加壱番 1 同行壱番 1 同行弐番 2…

カラムの順番を考えていなくて発生したエラー

SQL

順番のことを忘れていました。 photo credit: wetribe via photopin cc以前テーブルからテーブルへコピーする方法を書きました。 SQL Server:テーブルからテーブルへ、データをコピーする方法 - ソフトウェア開発者の日常 SQL Server:テーブルからテーブル…

SQL Server:テーブルからテーブルへ、データをコピーする方法

ワークテーブルにコピーするときに便利な方法ですね。 photo credit: wetribe via photopin ccSQL Serverの環境で、tablebに保存してあるデータを、tableaにコピーしなければならなくなりました。 プログラムでコピーしてもいいのですが、SQLでコピーできた…

PostgreSQL:2列を同一列としてソートする方法

CASE式でソートカラムを用意してソートしました。 PostgreSQL上に作成されたデータベースのテーブルに、column1とcolumn2があります。 column1はNULLの場合もありますが、column2はNULL以外の値が必ず入っています。 column1とcolumn2を同一列とみなし、NULL…

SQL:PostgreSQLでSQL上であまりを求めて絞り込む方法

データをExcelでもらってデータベースに登録した後になって、Excelのデータの小数点以下の計算が正しくなくて、1の位が5か0以外は修正が必要ということがわかりました。検索で絞り込めないか?全部のデータから該当するデータだけ探すのは大変なので、SQLで…

SQLの確認時は*でデータを参照していても問題ないけれど、プログラム上では使わない方が無難

SQL

2つのテーブルを結合して、データを取得している部分がありました。 以下のようなクエリで取得していました。 SELECT table1.mail, teble2.data1, * FROM table1 LEFT OUTER JOIN teble2 ON table1.id = table2.id きっかけ仕様追加で、3つのテーブルを結合…

SQL:PostgreSQLでSQL上で日付を判定して、結果を変える方法

毎年5月1日に会費を処理システムがあり、バッチ処理で夜間に処理してしまえばいいのですが、なぜか日中に処理を行うことになっています。 そのために、5月1日に会費の処理が行われるまで、システムは会費が処理されていないために利用できない状態になってい…

PostgreSQLでシリアル型の次の値を取得する方法

PostgreSQLで、インサートした後のシリアル型のカラムの値を取得するには、シーケンスとして定義されている値を SELECT nextval('test_table_id_seq'); で取得しいます。 取得した値が10だったら、 INSERT INTO test_table (test_table_id, item1) VALUES(10…

SQL:PostgreSQLでデータベースに格納された文字列の長さを調べる方法

2000件以上のデータの中から、長い文字列が格納された行を処理した結果を確認したくなりました。 プログラムで調べてもいいのですが、SQLでできないか確認したら、PostgreSQLでは、char_length()という関数が用意されていました。 使い方 使い方は簡単で SEL…

SQL:UNIONで結果を統合するときに忘れてはいけないこと

SQL

UNION使った上に、SUMで合計を求めたときに起きたことです。 最初は気がつかないで、はまってました。 UNIONを使って、2つのテーブルから結果を統合して、SUMで合計を求めて表示したら、SUMを使わないでプログラムで合計した値と、結果が一致しませんでした…

SQL:SQL Server用UNIONで結果を統合して、ORDER BYでNULLをソート順の下位にする方法

2日続けてのSQLについてです。 昨日、ORDER BYにCASE式を導入すれば、NULLをソート順の下位にできると書きました。 SQL:SQL Server用ORDER BYでNULLをソート順の下位にする方法 - ソフトウェア開発者の日常 早速プログラムに反映して、動作確認をしたら、エ…

SQL:SQL Server用ORDER BYでNULLをソート順の下位にする方法

以前以下の記事を書きました。 SQL:ORDER BYでNULLをソート順の下位にする方法 - ソフトウェア開発者の日常 同じことをしたくなったので、SQL Serverで試すと、ISのあたりの記述がおかしいというようなエラーになってしまいました。 他の記述方法がないか調…

SQL:ORDER BYでNULLをソート順の下位にする方法

SQL

調べたら簡単でした。 photo credit: Michael Kappel via photopin cc ACCESSで作られたシステムで、日付型のカラムに値が設定されているデータをソートの上位にしたかったので、調べました。 結果は、 SELECT * FROM TEST ORDER BY 日付型カラム IS NULL DE…

SQL:CASEを使って列をまとめる

SQL

SQLの便利な面を改めて感じる。 photo credit: Alexander Becker via photopin cc ことの起こり 日付型のカラムdate1とdate2があり、date1はNULLの場合があるが、date2は必ず値が入っている。 date1に値があればdate1を使い、NULLだったらdate2を使って、ソ…

SQL:COALESCEを使って、NULLを0に置き換える

SQLは使えば使うほど、奥の深さがわかってくる。 photo credit: kogakure via photo pin cc SELECTした結果で、数値項目を必ず0にする方法 PostgreSQLでは、COALESCEを使えばいいということがわかった。 SELECT table_a.id_a, table_a.id_b, COALESCE(table_…

SQL:日付範囲の比較で四苦八苦した

考え始めたときは、CASE文を使うとは思っていなかった。 photo credit: therefromhere via photo pin cc ことの発端 期間の判定で、6/1~7/15の間に今日の日付が入っているか判定するのは、SQLのWHEREで 6/1 <= 今日の年月 AND 今日の年月 <= 7/15 と書けば…