「Web出版サイト」ベータ公開

Q&A集[?]

当サイトでのご質問の受付は終了しました

すべてのコンテンツを読み込み専用としたため、回答欄からも投稿できません

Apache OpenOffice/LibreOfficeのご質問はそれぞれのフォーラムへご投稿ください

質問コーナー

サイト内検索

分類メニュー

関連サイト


本日:1
昨日:0
総数:2219
現在:4


フィールドの結合〜Mysql

ページOpenOffice.org FAQの登録ページ
投稿者urudakara
分類
edit/refer
優先順位
edit/refer
状態
edit/refer
カテゴリー
edit/refer
投稿日2011-01-16 23:20:33 (日)
OSlinux(ubuntu10.04)
依存するページ
バージョン
edit/refer

メッセージ

回答ページでは行末に「~」を付加する必要はありません

こんにちはいつもお世話になってます。よろしくお願いします。

データベースエンジンをHSQLからMySQLへ変更しました。

それに伴ってクエリで問題が出てきてしまい困っています。

以下の様なフィールドとデータがあるとします。

識別番号	-	12500		(データ型: テキスト[VARCHAR])
産次		-	1		(データ型:十進数[DECIMAL])

そしてクエリのデザイン画面のフィールド欄に

“識別番号” + “産次”

としました。するとHSQLsとMySQLで異なる結果を返してきました。

HSQL	→	125001
MySQL	→	12501

となります。

これをMySQLでHSQLと同じ”125001”という結果を出したいと思いました。

SQL表示で編集を選んで、

select concat(“識別番号”,”産次”);

としてみましたが

SQL ステータス: HY000
エラーコード: 1000

parse error, expecting `BETWEEN' or `IN' or `SQL_TOKEN_LIKE'

とエラーがでます。

自分ではデータ型の問題だと思いcasttも使ってみましたがダメでした・・・。

何かいいアイディアがありましたら教えてください。 よろしくおねがいします


暗黙的な型変換

M.Kamataki (2011-01-17 00:48:28 (月))

MySQL 5.1の以下のマニュアルを見ると、結合フィールドの両方が数値っぽいと「+」を使った加算では暗黙的に数値型に型変換が行なわれるようですね。

http://dev.mysql.com/doc/refman/5.1-olh/ja/type-conversion.html

ところで、

select concat(“識別番号”,”産次”);

では、「FROM "テーブル名"」がありません。テーブル名を指定するとどうなりますか?(「産次」の前に不要なスペースがあったり、「castt」は「cast」の間違いだったりします。質問が正確かどうかも怪しいです)

エラー再現

M.Kamataki (2011-01-17 01:22:40 (月))

提示いただいたエラーがちょっと違いますが、再現しました。(MySQLのバージョンは4.0.x、ドライバはJDBC)

faq6_124_01.jpg

「SQL コマンドを直接実行」ボタンをオンにすると、FROM行のない上記SQLの結果は「識別番号 産次」という結果になりました。

faq6_124_02.png

SQLの全文

urudakara (2011-01-17 22:49:17 (月))

こんにちはkamatakiさん
いつもありがとうございます。

まず、私の質問の仕方が正確でなく混乱させてしまいました。ごめんなさい。

まずcasttの件ですが、その通りで私のタイプミスでした。

また From”テーブル名”の件ですが、一応長いSQL文でしたので、省略してしまいました。
From文はきちんとついていますので、その件は問題ないと思います。

型変換の事はまったくしりませんでした。
確かに産次は数値しかはいりませんが、識別番号には数値だけが入る場合もあります。
自動的に型変換がおこなわれているのかもしれません。

長いSQL文ですが、そのまま載せるようにします。

SELECT "ID" AS "ID", "繁殖識別番号" AS "繁殖識別番号",
"繁殖識別番号" + "産次" AS "繁殖産次識別番号",
"産次" AS "産次", "受精回数" AS "受精回数",
"受精日" AS "受精日", "乾乳日" AS "乾乳日",
"分娩日" AS "分娩日", "妊鑑日" AS "妊鑑日",
"妊鑑結果" AS "妊鑑結果", "妊鑑備考" AS "妊鑑備考",
"繁殖備考" AS "繁殖備考", "推定" AS "推定",
"受精種" AS "受精種", "子識別番号1" AS "子識別番号1",
"子識別番号2" AS "子識別番号2", "初回受精" AS "初回受精",
"最終受精" AS "最終受精", "前回分娩日" AS "前回分娩日",
"分娩間隔" AS "分娩間隔"
FROM "cowdata"."繁殖情報" AS "繁殖情報"
ORDER BY "繁殖識別番号" ASC, "受精日" DESC~

そして、この文の中の

"繁殖識別番号" + "産次" AS "繁殖産次識別番号",

が問題の結果を返す部分です。

自分はここを

select concat(“繁殖識別番号”,”産次”)AS"繁殖産次識別番号";

としました。


どこが問題なのでしょうか?勉強不足で申し訳ありせんが、自分で調べた範囲ではこれであっていると思うのですが・・・

cast で型変換の必要

ike@九州 (2011-01-18 10:12:25 (火))

確認SQL

select concat( "繁殖識別番号","産次" ) AS "繁殖産次識別番号" from 〜;

上記を以下に修正

select concat( "繁殖識別番号", cast("産次" as CHAR) ) AS "繁殖産次識別番号" from 〜;


HSQLDB でも cast は使えるので直接実行ボタンはオフでも良さそうですが
MySQL 接続では、クエリデザイン画面でボタンをオンにしておく必要がありますね。

「SQL コマンドを直接実行」ボタンをオン

M.Kamataki (2011-01-18 10:32:25 (火))

ike@九州さんからフォローがありましたが、HSQLDBとMySQLでは、クエリーデザイン画面での動作に差があるようです。MySQLでCONCAT関数を使う場合は、「SQL コマンドを直接実行」ボタンをオンにしてください。(HSQLDBでもCASE文を使う際はこのボタンをオンにする必要があります)

このボタンの動作ですが、オフの場合はクエリーの実行はBaseが肩代わりします。オンの場合はMySQLに直接クエリーを投げて結果を待ちます。MySQLのマニュアルに掲載されている命令、関数が実行できない場合は、このボタンをオンにすると大概は実行できます。

わたしの環境で試した限りでは、CONCAT関数の役割は文字列の結合なので、MySQLの暗黙の型変換が行なわれ、CAST関数は不要だと思います。一度、CAST関数なしでも試してみてください。

;; わたしの回答が分かりにくかったようですね。

まだエラーがでます(泣)

urudakara (2011-01-18 22:53:55 (火))

ike@九州さん、kamatakiさん回答頂き有り難うございます

さてike@九州さんに教えて頂いたSQLを入れて見ました。
結局まだエラーはでてしまっております。以下がそのスクリーンショットです。

Screenshot1.png

そして次にSQLを直接実行のボタンを押してクエリーをしてみてもエラーが出ます。

Screenshot2.png

スクリーンショットではエラーリストの真ん中のエラーの説明になってますが、
エラーリストの一番上のエラーを選択すると「データ内容が読み込めませんでした。」と出ました。

ここでふと、これは文字列の型が問題ではなく、別の原因から来るエラーなのではとも思えてきました。
ike@九州さんに教えてもらったクエリーは完璧に思えるからです。

ますます困ってしまいました・・・。自分でも色々調べてみますが、もし何かヒントがあれば教えて頂けたらと思います。

それとkamatakiさん

「;; わたしの回答が分かりにくかったようですね。」

そんな、そんな・・・。問題は私の知識と経験の足りなさです。
これでも一応自分の限界までは調べてはいますが、わからない事ばかりです。
これからも色々教えて下さいね。

書き方が悪かったようです

ike@九州 (2011-01-19 08:53:56 (水))

失礼しました
該当部分のみを確認するだけの構文で
select concat 〜 を記述してました
concat 〜から適用です

全体での構文は

SELECT "ID" AS "ID", "繁殖識別番号" AS "繁殖識別番号",
concat( "繁殖識別番号", cast("産次" as CHAR) ) AS "繁殖産次識別番号",
"産次" AS "産次", "受精回数" AS "受精回数",
"受精日" AS "受精日", "乾乳日" AS "乾乳日",
"分娩日" AS "分娩日", "妊鑑日" AS "妊鑑日",
"妊鑑結果" AS "妊鑑結果", "妊鑑備考" AS "妊鑑備考",
"繁殖備考" AS "繁殖備考", "推定" AS "推定",
"受精種" AS "受精種", "子識別番号1" AS "子識別番号1",
"子識別番号2" AS "子識別番号2", "初回受精" AS "初回受精",
"最終受精" AS "最終受精", "前回分娩日" AS "前回分娩日",
"分娩間隔" AS "分娩間隔"
FROM "cowdata"."繁殖情報" AS "繁殖情報"
ORDER BY "繁殖識別番号" ASC, "受精日" DESC


以上になります。
ubuntu 10.04
MySQL 5.1 にて確認済みです

出来ましたが・・・

urudakara (2011-01-19 15:16:32 (水))

ike@九州さん回答ありがとうございました。
私の単純なミスだったんですね。concatからで良かったんですね。

以下ike@九州さんのSQL文を入れてSQLの直接実行ボタンを押してクエリを実行してみたスクリーンショットです。
SQLの直接実行ボタンは押さないとエラーが出ました。
エラーが出ずにクエリが表示されているのがわかると思います。
本当は繁殖産次識別番号とか載せられればいいのですが、個人情報も含むため勘弁して下さい。

Screenshot3.png


これで一件落着と一息ついて、フォームを開いてみたらサブフォームの抽出が上手く動かなくなってました・・・。
SQLの直接実行ボタンを押したからでしょうか?
フォームの繁殖情報の所は全件が表示されてしまっています。
う〜む。
原因として何が考えられると思いますか?
自分でも色々試してみます。

Screenshot4.png

sf_繁殖情報の確認

ike@九州 (2011-01-19 16:55:49 (水))

念のため odb ファイルをコピーし
コピーしたファイルで作業を行ってください。

サブフォーム "sf_繁殖情報" のプロパティ、データタブの確認

form.png

リンク先、リンク元の選択し直しで正常にならなければ
テーブルコントロール "tc_産次情報" の削除、再作成が必要かもしれません

sf_繁殖情報の確認してみました

urudakara (2011-01-20 00:38:19 (木))

こんばんはike@九州さん。

ike@九州さんに指摘されたポイントを自分で色々やってみました。
まずリンク先、リンク元の選択し直しをしてみましたが、変化はありませんでした。
そこでサブフォーム繁殖情報・産次情報(sf_繁殖情報とその下のtc_産次情報。さらにsf_産次情報と産次情報)を消去してもう一度作り直してみました。
・・・がこれもダメでした。

SQL文変更以前のバージョンも残してあったのでコピーをとり、それのSQL文を貼り付けて見ました。
この際SQLの直接実行ボタンを押しました。それをしないと保存が出来ないからです。
それでフォームを開いたのですが、やはりダメでした。

ちょっと別の方法を考えた方がいいのかなあ・・・と思い始めている所です。
まあ、まだ少し考えて試してみます。
今後の参考になるかもしれないので、もうすこし粘ってみようと思います。
取り急ぎご報告だけさてせ頂きました。

検証してみました

urudakara (2011-01-20 12:06:20 (木))

こんにちは。

自分で色々検証してみようと思い、ウィザードを使ってフォームをあらたに作ってみてはどうかなあと思い作ってみました。

Screenshot5.png

スクリーンショットを見てもわかるようにやっぱり抽出はされていないようです。
原因はわかりませんが・・・。
とりあえずご報告と思いアップしました。

そのままでは無理なようです

ike@九州 (2011-01-20 16:04:51 (木))

解決に二つの方法がありました

方法その1
クエリー_繁殖情報を右クリック>コンテキストメニューから
ビュー "v_繁殖情報" を作成します。

サブフォーム "sf_繁殖情報" のプロパティ、データタブから
内容の種類 テーブル
内容 v_繁殖情報
リンク先、リンク元
を選択し直すと表示されるようになる筈ですが
"mf_個体個別情報"のアクティブなレコードの1件のみになります。


方法その2
絞込みのマクロを以下に変更して、"クエリー_繁殖情報"の内容を書き換えます

Sub search_shikibetsu(oEvent)
 Dim oForm As Object, sSearchStr As String, sSearchField As String,sSQL As String
 Dim oDoc As Object,oQueryDefs As Object,oQueryDef As Object
 oForm = oEvent.Source.getModel().getParent()
 oDoc = oForm.getParent.getParent.getParent
 oQueryDefs = oDoc.DataSource.getQueryDefinitions()
 oQueryDef = oQueryDefs.getByName("クエリー_繁殖情報")
 
 sSQL = "SELECT ""ID"", ""繁殖識別番号"", ""産次"", ""乾乳日"", ""分娩日"", " & _
 "concat(""繁殖識別番号"" , cast(""産次"" As CHAR)) AS ""産次識別番号"", ""受精回数"", " & _
 """受精日"", ""妊鑑日"", ""妊鑑結果"", ""妊鑑備考"", ""繁殖備考"", ""推定"", " & _
 """受精種"", ""子識別番号1"", ""子識別番号2"", ""初回受精"", ""最終受精"", " & _
 """前回分娩日"", ""分娩間隔"" FROM ""繁殖情報"""
 
 ' 検索文字列と検索フィールド名を取得
 sSearchStr = oEvent.Source.text
 IF sSearchStr = "" Then
  '絞込みクリア
  oForm.ApplyFilter = False
  sSQL = sSQL & " ORDER BY ""産次"" DESC;"
  oQueryDef.Command = sSQL 
  oDoc.store()    
 Else
 '絞込み
  sSearchFiled = "個体識別番号"
  oForm.Filter = """" & sSearchFiled & """ LIKE '" & sSearchStr & "%'"
  oForm.ApplyFilter = True
  sSQL = sSQL & " WHERE ""繁殖識別番号""" & _
  " LIKE '" & sSearchStr & "%'" & " ORDER BY ""産次"" DESC;"
 oQueryDef.Command = sSQL    
 End If
 oForm.reload()  
End Sub

次にサブフォーム "sf_繁殖情報" のプロパティ、データタブで
リンク先、リンク元をクリアします。

この場合、"sf_繁殖情報" は同時に絞り込まれた内容全てを表示しますが
パフォーマンスが悪くなるかも知れません。
やってみないと分からないところです。

sSQL の内容は以前のモノから書いていますので
現状に合わせないとエラーとなります

やってみます

urudakara (2011-01-22 05:20:23 (土))

ike@九州さんすぐにご回答頂きありがとうございました。
実はまだ指摘されたこと試せて居ません。
basicのコードまで書いて頂き恐縮します。
もう少し時間を下さい。
まずは取り急ぎご報告しました。

方法1と2を試してみました

urudakara (2011-01-28 05:07:36 (金))

こんにちはike@九州さん

試すのがが遅くなり申し訳ありませんでした。



指摘された方法1を試してみましたがうまくいきませんでした。

ビューを作成してそのビューをリンク先に選んだのですが、結局前の状態と同じでした。
またその過程で気づいたのですが、この方法だと新規のレコードの追加が出来ない様でした。


次に方法2の方を試してみたのですが、少し解らないことがあり教えて頂けたらと思います。

結論から言うとこれも上手くいきませんでした。

ただ、少し疑問があるのが、ike@九州さんの回答の


「絞込みのマクロを以下に変更して、"クエリー_繁殖情報"の内容を書き換えます」

とありますが、私はもとから存在するsearch shikibetsuをそっくり書き換えました。
ただ考えてみるとこのマクロは個体識別番号の検索の為のマクロで、検索の時以外は働かないのではと思います。
イベントはテキストの変更時となっています。
もしかしたらike@九州さんの意図は別の所にあったのでは?と思うのですが、Basicのコードを見てそれを読み取ろうとしたのですが、わかりませんでした。
あのコードはどこに割り付ければいいのか教えて頂けないでしょうか?
お手数かけますがよろしくお願いします。

どうも行き違いがあるようです

ike@九州 (2011-01-28 09:08:32 (金))

>このマクロは個体識別番号の検索の為のマクロで、検索の時以外は働かないのではと思います

そのように意図して
検索BOX にての特定の個体識別番号の呼び出しだと思っていました。

作業手順については全く把握出来ていないので
何処に何を入力し、何処をどの様にしたいのかが行き違っていそうですね

サンプルを用意していただいて、明確に作業手順を提示していただいた方が解決への近道だと思います。

サンプルをアップします。

urudakara (2011-01-30 12:36:27 (日))

こんにちはike@九州さん

一連の質問をするのに元になるファイルをアップしておりませんで、余計な手間をおかけしました。
すみませんでした。
サンプルをアップします。
今回アップしたサンプルはテーブルがMySQLと接続されています。
検証の為HSQLのバージョンが必要でしたらfaq/6/104にsampleがあります。
作業手順をまとめますと、

まずサンプルのファイルを標準HSQLからMySQLへ変更しました。
ここのQ&Aの助けをかりて何とか出来ました。
そこまでの経緯はfaq/6/109をご覧下さい。

このデータベースの変更の過程で今回の様な問題が発生したわけです。

サンプルのフォームの構造をいいますと

mf_個体個別情報(フィールド:個体識別番号) とsf_繁殖情報(フィールド:繁殖識別番号)が1対多の関係でリンクされています。
さらにsf_繁殖情報(フィールド:繁殖産次識別番号)とsf_産次情報(フィールド:産次識別番号)が1対多の関係でリンクされています。

問題になるのはsf_繁殖情報(フィールド:繁殖産次識別番号)です。
繁殖産次識別番号はsf_産次情報(フィールド:産次識別番号)とのリンクの為にクエリー_cowdata.繁殖情報で作られた番号です。

構成はクエリー_cowdata.繁殖情報(フィールド:繁殖識別番号)の後にクエリー_cowdata.繁殖情報(フィールド:産次)がくっついたものとなります。
基本的にクエリー_cowdata.繁殖情報(フィールド:繁殖識別番号)は10桁で構成されています。
つまりフィールド:繁殖産次識別番号はフィールド:産次(大抵1桁の整数)がくっついたものとなりますから11桁の番号になります。

データベースがHSQLだった時にはクエリーデザイン画面のフィールド欄に”繁殖識別番号”+”産次”で出していました。
MySQLへの変更の過程でこれが数値に扱われ例えは

繁殖識別番号:1099323570
産次:2
の場合1099323572という数値が返されるようになってしまったわけです。

この問題はike@九州さんの指摘によりきちんとした数値が返されるようになりましした。

しかしなぜかike@九州さんのSQL文を使うとフォームで繁殖情報・産次情報で全件抽出されてしまうようになってそれが問題となっている事です。

元のSQL文に戻せば無事に抽出されるのでやはりSQL文が問題なのではと思います。

お手数ですが、検証してみていただければと思います。

個人的には解決がかなり難しいのかなあと思って代替案を考えてます。
例えばsf_繁殖情報(フィールド:産次)に入力後に、sf_産次情報(フィールド:産次識別番号)へ
繁殖産次識別番号が入力されるようなマクロがあればなあ・・・。などと考えてます。

解決方がありましたら教えて頂ければと思います。

データ型の変換

ike@九州 (2011-01-31 13:04:51 (月))

添付されたサンプルは、こちらではフォーム、クエリ共編集状態で開く事が出来ない為、以前の HSQLDB サンプルから MySQL に全てコピーした後、産次情報を適当に追加して検証しました。

現在のフォーム設計内容が違っていれば、当てはまるかどうか不明なのをご了承の上で…

「SQL コマンドを直接実行」ボタンがオンである場合に上手くいかないようです。
HSQLDB でもボタンをオンにすると期待した動作になりませんでした。
つまり、この動作は Base 自体の上手くできた内部解釈仕様だという事になりますね。

この内部解釈仕様を利用する為には、ボタンをオフの状態でクエリ繁殖情報を使う事が出来れば解決する事になりそうです。

原因は concat 関数ではなく cast 関数にあるので
テーブル繁殖情報の編集から"産次"フィールドのデータ型をテキスト(1文字)に変更して、該当部分を

concat( "繁殖識別番号", "産次" ) AS "繁殖産次識別番号"

にて変更すればOKという事になります。

入力時に産次フィールドは数値以外も受け付けるので注意が必要ですが
知っていれば無問題では無いかと…この方法では如何でしょうか?

テーブル繁殖情報は別名コピーでのバックアップを作成しておいての型変換作業が宜しいかと思います。

実現できました

urudakara (2011-02-01 22:50:25 (火))

ike@九州さん、回答ありがとうございました。

思っていたことが実現出来ました!!!
助かりました。

Screenshot6.png

SQLのボタンを押すとうまくいかないなあ・・・とは思っていましたが、
データ型を変えることでcast関数使わないようにすればいいとは思いつきませんでした。

入力時には気をつけて入力するようにします。

今回は質問するときのマナーが良くありませんでしたね。反省します。
アップしたサンプルが編集不可だった件ですが、
私自身もダウンロードしてみましたが、編集状態では開けませんでしたね。
ただファイル>名前をつけて保存をしたら編集出来るようになりました。
しかし、テーブルはMySQLを見ているようですから開けませんね。
次回以降はHSQLDBでサンプルを作りMySQLにコピーして下さいと一文つけ加えるようにします。

ながながと質問に答えて頂きあがとうございました。
今後ともよろしくお願いします。

これで完了としまする。

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

補足

ike@九州 (2011-02-02 09:09:49 (水))

解決できて何よりでした。

以下補足です

HSQLDB では、フィールドの型がテキストと数値の組み合わせでも
concat ( テキスト , 数値 ) で正しく結合できたのですが

MySQL では、結果がエラーにはならないものの
<OBJECT> という表示になり
残念ながら、期待した結合にはなりませんでした。

添付されたサンプルは
編集>データベース>プロパティ から
自身の MySQL 環境へ変更して、テーブルをコピー後
すべて開き確認する事が出来ました。
考えれば分かる事で失礼しました。 おほほほ

一緒に考える事で、自身の知識習得になっていますので得しています。
時間が取れない事もあるので、ご了承下さいませ…

お名前:
題名:


添付ファイル: fileScreenshot6.png 812件 [詳細] filesample2.odb 484件 [詳細] fileScreenshot5.png 844件 [詳細] fileform.png 803件 [詳細] fileScreenshot4.png 782件 [詳細] fileScreenshot3.png 1064件 [詳細] fileScreenshot2.png 858件 [詳細] fileScreenshot1.png 806件 [詳細] filefaq6_124_02.png 446件 [詳細] filefaq6_124_01.jpg 454件 [詳細]