みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

文字列内の数値を検索に使う場合の手法

検索の仕方で質問があります。
WinXPでの開発です。
データベースはOracle、開発ソフトはC++Builder5です。

以下のようなテーブルとデータがあります。
TB2,TB3のidは必ずTB1に存在します。
TB3のdataには、カンマ区切りの文字列が入っています。
dataのカンマ区切りの内容は
"コード,サイズ1,サイズ2"
となっています。
実際はこのような単純なデータではないので、カラム分けするのは
設計時に難しかったようです。
------------------------------
CREATE TABLE TB1
(
id number,
name varchar2(20)
);

1 sample1
2 sample2
3 sample3
------------------------------
CREATE TABLE TB2
(
id number,
keyno number
);

1 100
3 200
------------------------------
CREATE TABLE TB3
(
id number,
data char(200)
);

1 aaa,0.1,0.2
2 bbb,0.3,0.4
------------------------------

たとえば
TB2.keynoが100であり、TB3.dataのサイズ1が0.1のデータのTB1.nameを取りだす
という場合、どのようなSQL文になるのでしょうか?

TB3のデータは読み込んだ後に一旦文字列を分解して
コード、サイズ1、サイズ2を取得しないことには検索出来ないと思います。

一番高速な手法はどうなるでしょうか?
トリガーを使って、作業用のテーブル作ってやればいい、
みたいなことを言われたのですが、トリガーは違うのでは?と思いながら
手法を悩んでいます。

ローカルデータベースのようなファイルを使ってやる方法になるのでしょうか?

アドバイスをお願いします。

投稿日時 - 2012-05-24 17:26:45

QNo.7493796

困ってます

質問者が選んだベストアンサー

SELECT TB1.name FROM TB1
INNER JOIN TB2 ON TB2.id=TB1.id
INNER JOIN TB3 ON TB3.id=TB1.id
WHERE TB2.keyno=100 AND TB3.data LIKE '%,0.1,%'
でどうでしょうか。

投稿日時 - 2012-05-24 18:16:22

お礼

早速のアドバイスありがとうございます!
こんなにすっきり書けるのですね。
もしも数値が、0.1ではなく範囲指定だった場合はダメでしょうか?

投稿日時 - 2012-05-25 09:04:00

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

まず、
>トリガーを使って、作業用のテーブル作ってやればいい、
>みたいなことを言われたのですが、
は、TB4を作る。
CREATE TABLE TB4
(
id number,
data1 varchar2(200)
data2 number(18,3)
data3 number(18,3)
);

1 aaa 0.1 0.2
2 bbb 0.3 0.4

TB3のInsert/Update/Deleteトリガーを作って、
TB4を更新する
って趣旨でしょうね。

それはさておき。

>ORA-01722:数値が無効です。
を防ぎたいなら、CAST(の後にNVLを入れるだけで対応はできます。
AND CAST(NVL(・・・,0) AS DECIMAL(2,1))
といった感じですね。(Nullなら0に置き換える処理です。)

でも、本当にそれでいいの?
## ま、その前に、ANo1/2をみていて、要件を小出しにするのはやめましょう~。
## と言いたいところですが。
(1)逆じゃないの?と思う点
TB2.keynoが100であり、TB3.dataのサイズ1が0.1のデータのTB1.nameを取りだす
これはいいのですが、
TB2.keynoが100であり、TB3.dataのサイズ1が0.15のデータのTB1.nameを取りだす
とかしたいのでは?
(つまり、問い合わせ時の値が0.1と0.2の間ならすべての場合で、
 TB3の1を対象にしたいということでは?)
(2)idって検索条件につかっていいの?
INNER JOIN TB3 ON TB3.id=TB1.id
という検索条件でいいのかな?
もしそうなら、TB1とTB3に分けている理由が良く分からない。
TB1の中にDATAを作ればしまいのように思えるため。
(ま、あえて分ける理由が思いつかないわけではないのですが。)
TB1の
1 sample1
に対して、TB3の複数のレコードが対応するんじゃないのかな?
という疑問。
(3)TB3のdata char(200)の中身は?
本当に、
1 aaa,0.1,0.2
という形で1個しかないの?
1 aaa,0.1,0.2,aab,0.3,0.5,aac,0.6,0.9
とか複数あるのでは?
と疑問に思ってしまいます。
## 大丈夫です。というならそれでいいけど、要求事項の小出しはやめてくださいね。

投稿日時 - 2012-05-27 02:25:37

お礼

アドバイスありがとうございます!

> TB3のInsert/Update/Deleteトリガーを作って、
> TB4を更新する
> って趣旨でしょうね。

Oracleに新たなテーブルを追加するのではなく、C++Builder上でというニュアンスでした。
そのため、検索するタイミングでトリガーという意味が分かりませんでした。
これは指示の間違いだったのかもしれません。

> を防ぎたいなら、CAST(の後にNVLを入れるだけで対応はできます。

ありがとうございます!

> ## ま、その前に、ANo1/2をみていて、要件を小出しにするのはやめましょう~。
> ## と言いたいところですが。

申し訳ありません。
実際にはテーブルの内容もテーブル数も膨大で
サンプル用の記述がよくなかったです。
これからこうならないよう注意します。
ありがとうございます。

> (1)逆じゃないの?と思う点
> (2)idって検索条件につかっていいの?

完全に私の質問用に作成した仮テーブルの内容が悪いせいで
ご迷惑おかけしました。
テーブルを分ける理由などは正直昔から存在しているデータベースで
誰が設計したのかもわからない状態です。
何かを増やすたびに、よく分からないものになった可能性もあります。

> (3)TB3のdata char(200)の中身は?
> 本当に、
> 1 aaa,0.1,0.2
> という形で1個しかないの?

はい、これは必ず1個と決まっています。

色々とアドバイス戴きありがとうございました。
お手数おかけした上に、色々と不可解な内容となったことをお詫びいたします。

投稿日時 - 2012-05-28 09:20:29

ANo.2

>もしも数値が、0.1ではなく範囲指定だった場合はダメでしょうか?
AND CAST(SUBSTR(TB3.data,INSTR(TB3.data,',')+1
,INSTR(TB3.data,',',1,2)-INSTR(TB3.data,',')-1)
AS DECIMAL(2,1)) BETWEEN 最小値 AND 最大値
です。
(結構大変ですので、フィールドを内容単位に分割される事を推奨します)

投稿日時 - 2012-05-25 10:02:11

お礼

ありがとうございます。
目的を達成出来そうです!

ただ、TB3.dataに数値の入っていないもの(カンマだけ)があり、
ORA-01722:数値が無効です。
が出てしまいます。

書式をすでにあるコーディングに合わせると、以下のようになってしまっています。
※ごちゃごちゃするのでTB2の条件を外しています

select TB1.name from TB1 where exists(
select * from TB3 where TB1.id=TB3.id and
cast(
substr(TB3.data,~,~) AS DECIMAL(2,1)
)
BETWEEN 最小値 and 最大値
);

このとき、TB3.dataにカンマだけのデータが存在すると
エラーとなっている気がします。

投稿日時 - 2012-05-25 14:10:50

あなたにオススメの質問