SQL中の自作関数

質問

SQL中の自作関数

次のような自作関数があって都道府県別に連番を取得しようと思います。
ADOとADO.NETでやってみるのですがADOだと正常に関数で処理されますが
ADO.NETの方は 実行時に "式に未定義関数'Get_Pref_Code'" があります、
とエラーになります。

ADO.NETの方は何がマズいのでしょうか?

　　Function Get_Pref_Code(ByVal Pref As String) As String
　　　　Dim RT As String
　　　　Select Case Pref
　　　　　　　　Case "北海道" : RT = "01"
　　　　　　　　Case "青森県" : RT = "02"
　　　　　　　　Case "岩手県" : RT = "03"
　　　　　　　　Case "宮城県" : RT = "04"
　　　　　　　　Case "秋田県" : RT = "05"
　　　　　　　　Case "山形県" : RT = "06"
　　　　　　　　Case "福島県" : RT = "07"
　　　　　　　　Case "茨城県" : RT = "08"
　　　　End Select
　　　　Get_Pref_Code = RT
　　End Function

ADO/ACCESS2002の場合:

　　mySQL = "UPDATE KEN_ALL SET KEN_ALL.Pref_Code = Get_Pref_Code([Pref])"
　　myCon.Execute mySQL

ADO.NET/VB2005の場合:

　　mySQL = "UPDATE KEN_ALL SET KEN_ALL.Pref_Code = Get_Pref_Code([Pref])"
　　myCmd = New OleDb.OleDbCommand(mySQL, myCon)
　　myCmd.ExecuteNonQuery()

テーブル名=KEN_ALL
更新フィールド=Pref_Code
評価フィールド=Pref

回答No.1

1050 円（@1050YEN） · ベストアンサー

今回質問者さんは、
「ADO.NETだと自作の関数を組み込んだSQL文が発行できない」
ということですが、
私もADO.NETに自作関数を組み込んだSQLを発行しようとしたことがあります。
実現できなかったので、クエリ化したりして対応をいたしました。

そこで、
「テーブルに存在する都道府県をID化するSQL文を発行したい」
というのであれば、関数から値を引っ張るのではなく、マスタテーブルを参照させて、それから値を引っ張るという方法があります。
むしろその方が、今後の事を考えると、良いと思います。

まず
※1.[都道府県マスタ]テーブルを作成

カラム1:都道府県[テキスト型] ←主キー
カラム2:Pref_Code[テキスト型]

続いて、
※2.[]テーブルをにデータを入力

[都道府県][Pref_Code]
北海道　01
青森県　02
岩手県　03
宮城県　04
秋田県　05
山形県　06
福島県　07
茨城県　08
・・・・・・・・・・
って感じ

それから
※3.以下のSQL文を発行
UPDATE KEN_ALL LEFT JOIN 都道府県マスタ ON KEN_ALL.Pref = 都道府県マスタ.都道府県
SET KEN_ALL.Pref_Code = [都道府県マスタ].[Pref_Code]

お礼

やっぱり、自作関数はダメですか・・

ADO.NetでダメというよりVB2005ではダメって感じですね。

VB2005上のADOでもやってみたのですが、怒られました。

回答No.2

miyuyu

ちょっと気になるのですがAC2002で動いていると言う
mySQL = "UPDATE KEN_ALL SET KEN_ALL.Pref_Code = Get_Pref_Code([Pref])"...は

mySQL = "UPDATE KEN_ALL SET KEN_ALL.Pref_Code = " & Get_Pref_Code(Pref)
ではないのですか？

補足

>mySQL = "UPDATE KEN_ALL SET KEN_ALL.Pref_Code = " & Get_Pref_Code(Pref)
ではないのですか？

これだとSQLの実行時でなくてSQLの作成時に関数が評価されてしまいますね。また、[Pref]はフィールで名であって、変数名じゃないので・・・・エラーになります。