- ベストアンサー
SQL2000でのユーザ定義関数作成に関する質問です。
- SQL2000でのユーザ定義関数作成に関して質問があります。開発環境はvb6, SQLサーバ2000です。
- 分類マスタと親テーブル、子テーブルを連結し、ユーザ定義関数を使用して会員ごとの分類別情報を集計したいと思っています。
- 具体的には、会員CDごとに魚類、野菜、牛肉、豚肉の情報を持った結果を得たいと考えています。
- urubou2008
- お礼率20% (6/29)
- SQL Server
- 回答数3
- ありがとう数3
- みんなの回答 （3）
- 専門家の回答
質問者が選んだベストアンサー
#1,#2です。補足です。 とりあえずお役に立てたなら何よりですが、「テーブル関数でもいい」ということは「SQL Serverにオブジェクトを作成してもいい」ということですよね。 テーブル関数がOKで、ストアドプロシージャはNGという理由が、特になければ、ストアドプロシージャの利用も検討してみてください。 SQL Serverは「ストアドプロシージャ内でSELECTした結果がレコードセットで受け取れる」という比較的珍しいDBMSです。
その他の回答 (2)
- jamshid6
- ベストアンサー率88% (591/669)
#1です。 必要なSQL文は以下の通りです。 SUMの部分が分類コードの数だけ必要なので「組み上げる」必要があるわけですが。 SELECT p.会員CD,p.会員名, SUM(CASE WHEN 分類CD=1 THEN 1 ELSE 0 END) [魚類], SUM(CASE WHEN 分類CD=2 THEN 1 ELSE 0 END) [野菜], SUM(CASE WHEN 分類CD=3 THEN 1 ELSE 0 END) [牛肉], SUM(CASE WHEN 分類CD=4 THEN 1 ELSE 0 END) [豚肉] FROM 親テーブル p LEFT OUTER JOIN 子テーブル c ON c.会員CD=p.会員CD GROUP BY p.会員CD,p.会員名 ORDER BY p.会員CD 呼び出す元はVBでもVBScriptでも何も変わらないのですが、前に書いた通り、 「戻りのカラム数が不定の場合、ユーザ定義関数では実現できません」。 ただ正直、使っている用語がかみ合っていないような気がしています。 ここで聞く以上、ユーザ定義関数って、SQL Serverのテーブル関数のことですよね？ まさかVBのユーザ定義関数じゃないですよね？
- jamshid6
- ベストアンサー率88% (591/669)
戻りのカラム数が不定のクエリはユーザ定義関数では実現できません。 が、以下の通り、ストアドプロシージャならば実現できます。 動的クエリを組み上げて実行します。 CREATE PROCEDURE GETMATRIX AS BEGIN SET NOCOUNT ON DECLARE @sql varchar(8000) SELECT @sql=ISNULL(@sql+',','') +'SUM(CASE WHEN 分類CD='+CONVERT(varchar,分類CD)+' THEN 1 ELSE 0 END) ['+分類名+']' FROM 分類マスタ ORDER BY 分類CD SET @sql='SELECT p.会員CD,p.会員名,'+@sql +' FROM 親テーブル p' +' LEFT OUTER JOIN 子テーブル c ON c.会員CD=p.会員CD' +' GROUP BY p.会員CD,p.会員名' +' ORDER BY p.会員CD' EXEC (@sql) END VBからADOで呼ぶなら、ADODB.CommandのCommandTextに"EXEC GETMATRIX"をセットするか、 CommandTypeにadCmdStoredProcを指定した上で、CommandTextに"GETMATRIX"とセットするかしてください。 もう一つの留意点は、中でsql文を組みあげていますが、この組み上げたSQL文が8000バイトを超えると エラーになります（分類マスタの項目数によります）。その場合は、少し工夫が必要になります。
お礼
完全なこちらの記入ミスなのですが、vbScriptを使用して、ASPにて使用したいと思っています・・・ 申し訳ありません。 質問ばかりで申し訳ないのですが、下記のようなことはできませんでしょうか？ 子供のテーブルのみを対象として -------------------------------------------------- 会員ID|魚類 |野菜 |牛肉 |豚肉 | -------------------------------------------------- 1 |1 |3 |1 |2 | -------------------------------------------------- 2 |4 |1 |0 |2 | -------------------------------------------------- 3 |0 |1 |3 |3 | -------------------------------------------------- のような結果をSQL文、もしくはユーザ定義関数を使用して実現することはできないでしょうか？
お礼
再度のご回答ありがとうございます。 ユーザ定義関数はSQL Serverのテーブル関数のことです。 ただ、一度しか作成した事がなかったもので、カラム数が不定の物は作成できないというのが理解できていませんでした。 上記で教えていただいたSQLのSUM部分をプログラム内で動的に作成し、教えていただいたSQLを元にがんばってみようと思います！ 本当にありがとうございました。