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

質問

検索の仕方で質問があります。
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を取得しないことには検索出来ないと思います。

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

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

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

回答No.1

原沢 信道（@nharasawa） · ベストアンサー

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,%'
でどうでしょうか。

お礼

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

回答No.3

Siegrune

まず、
>トリガーを使って、作業用のテーブル作ってやればいい、
>みたいなことを言われたのですが、
は、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 aaa,0.1,0.2,aab,0.3,0.5,aac,0.6,0.9
とか複数あるのでは？
と疑問に思ってしまいます。
## 大丈夫です。というならそれでいいけど、要求事項の小出しはやめてくださいね。

お礼

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

> 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個と決まっています。

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

回答No.2

原沢 信道（@nharasawa）

＞もしも数値が、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 最大値
です。
（結構大変ですので、フィールドを内容単位に分割される事を推奨します）

お礼

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

ただ、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にカンマだけのデータが存在すると
エラーとなっている気がします。