BASEのクエリで||で繋ぐ文字中にIIFとIS NULLを使用したい

日本語
, , ,
#1

郵便用住所表記の作成で壁にぶつかっています。英語圏向け、中国語圏向け、日本向けの３種類の違った表記法で出力しなければなりません。

前提１

BASEに煩く言われるので、FIREBIRDに移行しても最小限の修正で動かせるように準備しておきたいと思っています。ですがまだFIREBIRDにはできていません。停止する場所を探しながら修正を繰り返しています。私は素人と言っても過言でないビギナーです。

前提２

完全な多言語環境です。使用される場所は日本国内ではなく中国現地法人です。小さな会社ですが４か国の人で構成されています。社内の共通言語は北京語ですが、広東語、日本語、英語が飛びかいます。全ての資料は北京語、日本語、英語の３種類で作成されなければなりません。例えば全ての商品名には、日本語名・中国語名・英語名が並列で存在します。各人はそれぞれ母国語の各種オフィス製品を個人使用していますが、社内資料はリブレで統一されています。

前提３

要するにデータベースを外注できる環境ではありません。小さな会社の複雑で煩雑な多国語データベースを安価に作成してくれる会社は見つかりません。やむなく全てをコツコツと悩みながら自製しています。社内をリブレで統一したのも多言語環境での製品とバージョンの統一を安価に済ませるのが理由です。ひとつのデータベースを各国語バージョンのリブレで皆が使用しています。

前提４

郵便用住所の出力には、日本式住所表記・中国式住所表記・英語式住所表記が必要です。言語によって表記の順番が変わります。また中国式と英語式は日本式とまるで違い、道路名と道路番号で表記されます。この場合、道路名はひとつとは限りません。故に、住所テーブルには実に多くのフィールドがあります。中には日本人の私には想像すらつかなかった表記（ど真ん中とか。。）もあります。これらの個別の住所情報を、ひとつのテーブルから選び、相手国によって組み合わせなければなりません。

前提５

顧客データベースは、ランチェスター型のエリアマーケティング（シェア理論）を実施するために、非常に細かな単位でのエリアに分かれて登録されています。最も小さなエリア単位（小区）が集まって、次の大きさのエリア単位（中区）となり、それらが段階的に大きな集合となっていきます。そのそれぞれの単位で市場分析できるようになっています。道路名は、最も小さなエリア単位と紐づけられています。エリアごと＆道路ごとの顧客抽出も可能になっています。ゆえに郵便用住所は、繋がったひとつの列としては登録されていないのです。（最初に同時にやっとけばよかったのですが。。）

●悩んでいる問題

当然ながら、全てのフィールドが埋まっているわけではありません。それらの空白はNULLです。これまではCONCAT関数で綺麗に繋いで出力できていました。NULLのフィールドもそのまま空文字のように無視してくれていました。

ところがFIREBIRDではCONCAT関数が使えません。||で繋がなければならないようです。さらに、NULLがNULLのままだと、、、繋ぐと全てがNULLになってしまいます。

故に試作中のFIREBIRD版では、COALESCE関数で< '' >空文字に置き換えてから繋いでいます。

.....|| COALESCE ( "AVE-SML", '' ) || COALESCE ( "AVE-SML-NO", '' ) ||' '||COALESCE ( "AVE-MED", '' ) || COALESCE ( "AVE-MED-NO", '' ) ||....

これで、動くには動いてくれます。上記の真ん中の||' '||には半角スペースが入っています。道路名と道路名の間などに挿入しています。これを挿入しないと英文字表記は耐えません。

ところが、ここで頭を抱えてしまったのは、あまりにもテーブルの中の空白（NULL）フィールドが多いため、住所表記の途中でやたらと長い「半角スペースの集合」が増えてしまうことです。実は最初は||','||にしていたのですが、これだと「,」というようになってしまうので半角スペースに変更したのです。

道路名式の住所表記の場合、通常は最初に路地名と道路番地が記載されます。その次に、その路地に繋がる近辺の大通り名と路地への入口に該当する場所の道路番地が記載されます。面倒なのは場所によっては、この道路名が３つになったり１つになったりするのです。まるで規則性がありません。まれに小さな円形道路の真ん中だったりします。「〇〇レストラン斜め向かい」とかも平気であります。

要するにそれらに対応すべく、どうしてもフィールドの数が更に増えてしまいます。でも上記のコードだと、どうしてもNULLの出現ごとに半角スペースが出現します。多少なら気になりませんが、みるからに酷い体裁の住所表記になります。

●失敗している（動いてくれない）解決方法

前の項目がNULLだと「空文字」にして、NULLでない場合だけ半角スペースを挿入したいのです。これができれば半角スペースの連続を回避できます。ですがこれは、ひとつの置き換えしか選択できない<ISNULL>や<COALESCE>では解決できません。どうにかこれを解決できないかと、あれこれ悩みにたどり着きました。

.....|| COALESCE ( "AVE-SML", '' ) || COALESCE ( "AVE-SML-NO", '' ) ||IIF( "AVE-SML" IS NULL, '', " " ) ||COALESCE ( "AVE-MED", '' ) || COALESCE ( "AVE-MED-NO", '' ) ||....

ですが、これでは動いてくれません。

Access is denied: IIF in statement .....

と出てきます。

何か良い解決方法はないでしょうか？？

#2

あまりSQLに詳しくはないので自信なさげにひとりごと。

例えば

SELECT a + 3 FROM Table1;

とかやってa列に3を足した値を求めたりするための式が書けると思う

これと同様に、

SELECT COALESCE ( "AVE-SML" || ' ', '' ) FROM Table1;

みたいに先に計算したりとか出来ないのかなーって思わんこともない。

だって、AVE-SMLがNULLのとき、“AVE-SML” || ’ ’ の式の値はNULLなんでしょ？

で、COALESCEはNULLでない最初の値を持ってくるんだから上記の場合空文字列になりそうな気がするんだけど…

#3

あと、多分、最初の質問者の思惑通り

IIF( "AVE-SML" IS NULL, '', "AVE-SML" || ' ' )

も出来ると思う。 カラム名にハイフン等が含まれているときにこれを引き算として捉えられないように

『ダブルクオート』でくくることで「列名」を表現し、『シングルクオート』でくくることで文字列とする。

わけだけど、色んな言語経験していると、ダブルクオートもシングルクオートもどちらも文字列用だったり、ダブルクオートが文字列でシングルクオートが文字、みたいな言語があったりするけど、混同してない？

俺も最初このコメント書いたとき、C#に合わせて+演算子で文字列を連結しようとしてorzってなった

#4

まず最初に、私の投稿を見やすくしてくださってありがとうございます。

この手の記載方法は不得手でしたので助かります。





###●できました！
結論を先に書くと、できました！目から鱗です。記載して頂いた

 SELECT COALESCE ( "AVE-SML" || ' ', '' ) FROM Table1;

で全て綺麗に揃いました！ありがとうございます！大感謝！

COALESCEの入れ子で||を使用できるとは、思いもしませんでした。


###●ですが後味が悪いのです。
IIFでは動かなかったからです。この理由がまるで理解できていません。

Access is denied: IIF in statement .....

と出ます。

#5

###●動かないのは以下です

 SELECT 
 "TGT-NME-CN" AS "TGT-NME-CN",  
 
 "COUNTY-NME-CN" || ' ' || 
 "PROVINCE-NME-CN" || 
 "CITY-NME-CN" || ' ' || 
 
 IIF( "DIST-BIG-CN" IS NULL, '', "DIST-BIG-CN" || ' ' ) || 
 IIF( "DIST-MED-NME-CN" IS NULL, '', "DIST-MED-NME-CN" || ' ' ) || 
 IIF( "DIST-SML-NME-CN" IS NULL, '', "DIST-SML-NME-CN" ) || 
 IIF( "DIST-SML-NUM-CN" IS NULL, '', "DIST-SML-NUM-CN" || ' ' ) || 
 IIF( "AVE-NME-CN" IS NULL, '', "AVE-NME-CN" ) || 
 IIF( "AVE-NUM" IS NULL, '', "AVE-NUM" || ' ' ) || 
 IIF( "MST-NME-CN" IS NULL, '', "MST-NME-CN" ) || 
 IIF( "MST-NUM" IS NULL, '', "MST-NUM" ) 
 AS "POS-ADD" 
 
 FROM "TGT-AREA-TBL" 
 ORDER BY "COUNTY-NME-CN" ASC, "TGT-NME-CN" ASC

警告がIIFに関するものなので、

IIFの記述にミスがあるのだと思うのですが。。。



もしかしたらクエリでIIFは使えない？のかなと思ったり。。

あるいはIIFは||で繋げないのか？とか思ったり。。



いろんな思いつきでネットを調べてみるのですが、情報が見つけられません。

#6

追伸ですが。。

FIREBIRDって使いにくいですね。。
テーブル名とかのバイト制限とか、NULLの扱いも今までとかなり違うようで。
今まで使ってた関数では動かなかったり。。何これ状態で困惑してます。

テーブル名などの修正にも吐きそうなほど時間がかかりました。
もしかしてフィールド名（カラム名）にもバイト数制限があるのでは？とひやひやしてます。

そもそもネットで得られる情報が皆無に近い。
探せば少しだけあるにはありますが、私のような初心者には足りません。

困っているのは警告時に出てくるミスを表す？２種類のコード番号なのですが、
これがいったい何を表してるのか、まるでわからないのです。

もともとリブレのBASEからして情報が少なくて、
壁にぶつかり続けながら力まかせにやってるのですけれど。

どこに問題があるのかだけでもわかればヒントになるのですが、
このコード番号の一覧って、どこにあるのでしょうか？

そもそもFIREBIRDにせにゃいかんと重い腰を上げたのは、
日本語版と英語版で「早く変えろよ」的な警告が毎回出てくるようになって、
このままじゃどこかでFIREBIRDしか使えなくなるんじゃないかと感じたからなんですが。

リブレは言語によってFIREBIRDに対する状況が違うみたいで、
中国語版では何も警告など出てこないんです。

#7

Access is denied: IIF in statement …

俺の環境だとこのエラー、HSQLDBでしか出てこないんだよなぁ…

#8

2種類のコード番号

この番号、一応欲しいなぁ…

#9

すみません。２日ほど離れてました。



仰る通りです！

私自身が混乱してファイルを取り間違えてました。



HSQLDBでエラーが出ます。


 SQL ステータス: S1000
 エラーコード: -33
 Access is denied: IIF in statement...

それに気が付いて試したら、
FIREBIRD側ではエラーが出ませんでした。

本当に申し訳ありません。


で、どうなんですかね。。これって。。
どこかにそういう資料ってないもんでしょうか？

このままじゃ、ぶっつけ本番の手探りが続いてしまいます。。

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.