社内SEの徒然なる日記

SQL(Oracle)で按分計算(差額も考慮)する方法の確認(前編)

■ 按分計算

あるシステム開発で、支店別の支払金額から按分比率を算出して、その比率に応じて手数料を按分するという要件が出てきました。

こういう計算は、結構面倒な要素が出てくるのでプログラム側で計算した方が良いかもしれませんが、今回はSQL1回で計算してみます。

計算の前提環境です。

1.取引先に対する合計支払金額が期日別に存在する。
2.支払金額は支店別、期日別に確定している。
3.手数料は取引先別期日別の合計金額がある

はい、取引先別期日別に按分するんです。

Aテーブル(PrimaryKey:管理番号、支店、期日)
管理番号  支店  期日    金額
10000 10 2/10 10,000
10000 20 2/10 90,000
20000 10 5/15 50,000


Bテーブル(PrimaryKey:管理番号、期日、連番)
管理番号  期日  連番  手数料
10000 2/10 1 400
10000 2/10 2 200
10000 2/10 3 100
10000 2/10 4 300


管理番号というのは、1支払単位(支払日&支払先)を表す番号です。

AテーブルとBテーブルの関係は、管理番号と期日で結合すると1:N。なのでBテーブルにデータがある時は、必ずAテーブルにもデータが存在するとします。

■ SQL

まず、結果をどうぞ。
SELECT
Z.管理番号
,Z.支店
,Z.期日
,Z.按分金額
+
CASE WHEN Z.連番 = 1
THEN
Z.按分合計 - SUM (Z.按分金額)
OVER (PARTITION BY
Z.管理番号
,Z.期日
)
ELSE 0
END
AS 最終按分金額
FROM
(
SELECT
A.管理番号
,A.支店
,A.期日
,NVL(B.按分合計 ,0) AS 按分合計
,ROUND(
RATIO_TO_REPORT (A.金額)
OVER (PARTITION BY
A.管理番号
,A.期日
)
* NVL(B.按分合計, 0), 0) AS 按分金額
,ROW_NUMBER ()
OVER (PARTITION BY
A.管理番号
,A.期日
ORDER BY
A.金額 DESC
,A.支店 ASC
) AS 連番
FROM
A
,( SELECT
管理番号
,期日
,SUM(B.手数料) AS 按分合計
FROM
B
GROUP BY
管理番号
,期日
) B
WHERE
A.管理番号 = B.管理番号(+)
AND A.期日 = B.期日(+)
) Z


もちろん、事前に動かして検証したのですが、ブログ用に項目名を変えたり小細工したので、このSQL自身は動かしていません。もしかすると構文エラーとか発生するかもです。

次回は、このSQLの構成要素を一つずつ分解しながら解説してみようと思います。今回は、概要だけ書いときます。

■ 簡易解説

まず、Bテーブルを按分単位である管理番号、期日で集計した手数料の合計金額を作ります(按分合計)。これを内部テーブルとします(内部Bテーブル)。そして、内部Bテーブルと、Aテーブルを管理番号、期日で外部結合します。

Oracleの分析関数「RATIO_TO_REPORT」を使用して、Aテーブルの管理番号、期日を集計単位とした合計金額に対する、管理番号、期日、支店の金額の比率を取得します(按分比率)。

取得した按分比率と内部テーブルの按分合計を掛け算して、ROUND関数で小数点以下四捨五入します(按分金額)。

さらに、分析関数「ROW_NUMBER」を使用して、Aテーブルの管理番号、期日を集計単位として、金額の降順、支店の昇順に連番を採番します。

ここまでの結果から、AテーブルのPrimaryKey(管理番号、支店、期日)と、内部Bテーブルの按分合計、取得した按分金額、連番 で内部テーブルを作成します(内部Zテーブル)。

内部Bテーブルから取得した按分合計から、管理番号、期日を集計単位として按分金額の合計を減算します(按分差額)。

連番が1のデータに対してのみ、按分金額に按分差額 を加算します。連番が1以外のデータは、按分金額をそのまま使います(最終按分金額)。

内部Zテーブルから、AテーブルのPrimaryKey(管理番号、支店、期日)と、取得した最終按分金額を取得します。

■ 後書き

実際には、Aテーブルに対して按分した金額を更新するという案件でした。なので、UPDATE文はこうなるのかな?

UPDATE A
SET 金額 = 金額 -
(
SELECT NVL(ZZ.最終按分金額, 0)
FROM ( 内部Zテーブル ) ZZ
WHERE ZZ.管理番号 = A.管理番号
AND ZZ.支店 = A.支店
AND ZZ.期日 = A.期日
)


・・・これこそ、何もテストしてないけど、本当に動くかな?

次回:SQL(Oracle)で按分計算(差額も考慮)する方法の確認(中編)

最新の記事:http://harikofu.blog.fc2.com/
投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end — スポンサードリンク

PageTop

ExcelVBA&ADOでOracleからデータ抽出で文字化け(解決編)

■ キャラクタセット

Oracle関係で文字化けした時の基本は、サーバ側とクライアント側で文字コードが違っていないか確認する事らしいです。

まぁ、oo4oでは上手く行くので大丈夫だとは思いますが、念のために確認してみます。

まず、サーバ側を確認するため下記のSQL文を発行します。
SELECT  *  FROM  V$NLS_PARAMETERS
WHERE parameter = 'NLS_CHARACTERSET'


結果は「JA16SJIS」。
PARAMETER	VALUE
NLS_CHARACTERSET JA16SJIS


クライアント側は、レジストリの¥HKEY_LOCAL_MACHINE¥SOFT¥WARE¥ORACLE¥KEY_OraClient11g_home1 の、NLS_NANG の値を確認。

設定されていたのは、これです。

JAPANESE_JAPAN.JA16SJISTILDE


うん、やっぱり良さそうです。

■ プロバイダ

今度は、ADO側のプロバイダを確認します。

'ADODB.Connectionオブジェクト
Dim mAdoCn As Object

'ADOコネクションの作成
Set mAdoCn = CreateObject("ADODB.Connection")

'接続先データベースにOracleを指定
mAdoCn.Provider = "OraOLEDB.Oracle" 'Oracle純正


使っているのは、Oracle純正の「OraOLEDB.Oracle」。うっかりMicrosoft製を使ったとかって訳でもなさそうです。

■ 環境ってよりは・・・

こうなると、環境っていうよりはもっと小さなレベルで問題が起きていると考えた方が良い気がします。そもそも、文字コードのレベルで間違えているのなら、〜だけでなく、もっと色々な文字が化けるような気がします。

そういった観点で調べていくと、どうやら文字コード変換の色々な絡みで〜が?になってしまう問題にたどり着きました。

どうすれば良いかっていうと、VBA側で特定の文字コードを強制的に〜に置き換える方法が良いそうです。

こんな感じかな?



'読み取り専用でファイルを開きます。このファイルに書き込むことはできません。
Public Const ForReading As Integer = 1
'ファイルを開き、ファイルの末尾から書き込みます。
Public Const ForAppending As Integer = 8
'システムの既定値を使ってファイルを開きます。
Public Const TristateUseDefault As Double = -2
'Unicode としてファイルを開きます。
Public Const TristateTrue As Double = -1
'ASCII ファイルとしてファイルを開きます。
Public Const TristateFalse As Double = 0

Sub TEST()

'----- 省略 Start --------
'
' Oracleに接続して、SQL(SELECT)文を実行。
' 実行結果を、myRs(レコードセット)に設定する。
'
'----- 省略 End ----------


    'ファイルシステムオブジェクトの宣言
    Dim myFs As Object
    Set myFs = CreateObject("Scripting.FileSystemObject")
    Dim myOutFile As Object

    '出力ファイルの作成とオープン
    myFs.CreateTextFile ("D:¥CSVTEST.csv")
    Set myOutFile = myFs.OpenTextFile("D:¥CSVTEST.csv", ForAppending, False, TristateFalse)

    'データ全件出力
    Do Until myRs.EOF

        '出力
        'myOutFile.WriteLine (myRs.Fields.Item(0).Value)
        myOutFile.WriteLine (Replace(myRs.Fields.Item(i).Value, ChrW(12316), "〜"))

        'カーソルを次行に変更します。
        myRs.MoveNext
    Loop
End Sub


この辺り、文字コードの結構複雑な話があるようです。一通り目を通したのですが、他人に説明できるほど理解しきれていないので省略させて下さい。

■ 後書き

この現象、そういえば、昔何かの記事で読んだ気がします。当時は「へぇ、そうなんだ」くらいの気持ちで読み流したのですが、まさか自分が遭遇するとは思いませんでした。

さて、これで問題解決って言いたかったのですが、結局は接続方法をoo4oに戻すことにしました。

実際にデータを投入して動かしてみると、取得結果がNullだった時の扱いとか色々なことが、oo4oADOで微妙に動作が違うってことが分かってきて、少し腰を据えて取り掛からないとダメだって事が判明しました。

ま、いつか納得するものが出来たら記事にしましょうか。

問題編:ExcelVBA&ADOでOracleからデータ抽出で文字化け(問題編)

投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end --- スポンサードリンク

PageTop

ExcelVBA&ADOでOracleからデータ抽出で文字化け(問題編)

■ VBA&ado

Excelからoo4oでOracleに接続して色々出来るようになった(oo4oでExcelから接続してみた(接続・切断))のですが、oo4oはOracleに接続するためのものなので、他のRDB(SQLServer、Access)などに接続しようとすると、ちょっと具合が良くないです。

そこで、oo4oの部分をADOに置き換えてみました。それ自体は上手く行って、SELECT文を発行してデータを抜けるところまできました。

んで、SQL文を実行して、取得したデータをCSVファイルに出力する処理を作ったのですが、テキストファイルへの書き出しで異常終了してしまいます。
ExcelVBA&ADOでOracleからデータ抽出で文字化け - 1

はて?

■ 状況

ソースはこんな感じ。ADOでOracleに接続してデータを抽出する部分は今回の記事と関係ないので端折りました。



'読み取り専用でファイルを開きます。このファイルに書き込むことはできません。
Public Const ForReading As Integer = 1
'ファイルを開き、ファイルの末尾から書き込みます。
Public Const ForAppending As Integer = 8
'システムの既定値を使ってファイルを開きます。
Public Const TristateUseDefault As Double = -2
'Unicode としてファイルを開きます。
Public Const TristateTrue As Double = -1
'ASCII ファイルとしてファイルを開きます。
Public Const TristateFalse As Double = 0

Sub TEST()

'----- 省略 Start --------
'
' Oracleに接続して、SQL(SELECT)文を実行。
' 実行結果を、myRs(レコードセット)に取得する。
'
'----- 省略 End ----------


    'ファイルシステムオブジェクトの宣言
    Dim myFs As Object
    Set myFs = CreateObject("Scripting.FileSystemObject")
    Dim myOutFile As Object

    '出力ファイルの作成とオープン
    myFs.CreateTextFile ("D:¥CSVTEST.csv")
    Set myOutFile = myFs.OpenTextFile("D:¥CSVTEST.csv", ForAppending, False, TristateFalse)

    'データ全件出力
    Do Until myRs.EOF

        '出力
        myOutFile.WriteLine (myRs.Fields.Item(0).Value)

        'カーソルを次行に変更します。
        myRs.MoveNext
    Loop
End Sub



すると、テキストファイルに書込みするWriteLineでエラーが発生します。

何を書き込もうとしているかと思ってイミディエイトウィンドウに書き出してみました。
ExcelVBA&ADOでOracleからデータ抽出で文字化け - 2

気になるのが、最後の?です。この時点で、文字化けの疑いが濃厚になってきました。実際に、別のツールで見ると、こういうデータになっています。

小樽地区 H26年11月〜


どうやら、〜が文字化けしているみたいです。

■ 暫定対応

VBAは、内部的にはUNICODEで処理をしていると聞いたことがあります。んで、Oracle側の文字コードはshift-jis。この辺りが原因かと思って、StrConv辺りで色々と変換してみたのですが、余計ひどく文字化けする有様です。

色々と思う所はあったのですが、この時は急ぎでした。なので、Oracleへの接続方法を実績のある方法をoo4oに変えてみたのですが、こちらでは文字化けしません。

・・・うーん、なんだろ?

■ 後書き

全てが終わった後で思ったのですが、WriteLineで異常終了するってことは、ファイルに書き込めないってことですよね。

出力ファイルを開く時にASCIIを指定していたいのですが、Unicodeにした所、異常終了はしなくなりました。


Set myOutFile = myFs.OpenTextFile("D:¥CSVTEST.csv", ForAppending, False, TristateTrue)



ただ、エディタによっては文字化けしたままだったりと、微妙な結果です。やっぱり、抜本的な原因を突き止めないとダメですね。

解決編:ExcelVBA&ADOでOracleからデータ抽出で文字化け(解決編)

投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end --- スポンサードリンク

PageTop

javaの変数の先頭が数字はダメよ

■ 思考

新人君に、画面を一つ作って貰っています。

JSP/Servletの仕組みなので、HTMLで画面イメージを作成して、そこにJavaのソースを埋め込んでいきます。そこそこ順調に進んでいると見ていたのですが、何やら画面を睨みながら唸っています。

・・・唸るような段階ではないと思うし、多少のことは自力で何とかして欲しいのですが、数時間放置しても様子が変わらないので、止む無く声をかけることにしました。

聞くところによると、Javaのソースを埋め込んだ場所でエラーが出ていて解決出来ないらしいです。

■ 命名規約

まぁ、原因はタイトルに既に書いてますよね。Javaでは変数の先頭に数字を使ってはいけないのですよ。

学生時代の履歴書によると、学習内容にJavaと書いていたのですが、この有様ではまともに学んでないってことでしょうね。

もう、なんだかなぁ。ですよ。

■ 後書き

正直、学生が学んでくるレベルに期待していなかったので予想通りではあります。

だから上司には、募集したい人間像を聞かれた時に、「システムの知識が0でも良いので、そこそこの大学をでている子」と言ったんですよね。

0から鍛える覚悟なので、基礎学力があれば十分だと思ったんです。今となっては、どうしようもない話ですけどね。

投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end — スポンサードリンク

PageTop

PowerShellでファイル削除(読取専用)

■ Remove-Item

Remove-Itemコマンドレットでファイル(正確にはフォルダ)を削除しようとしたのですが、何故かエラーになります。

Remove-Item 'Z:¥backup¥20150721_093150' -Recurse

Remove-Item : 項目 Z:¥backup¥20150721_093150¥format を削除できません: 操作を実行するた
めに十分なアクセス許可がありません。
発生場所 D:¥Backup.ps1:113 文字:24
+ Remove-Item <<<< 'Z:¥backup¥20150721_093150' -Recurse # -
Recurseをオプションを付加して、子要素も纏めて削除
+ CategoryInfo : PermissionDenied: (format:FileInfo) [Remove-Item
]、IOException
+ FullyQualifiedErrorId : RemoveFileSystemItemUnAuthorizedAccess,Microsoft
.PowerShell.Commands.RemoveItemCommand


アクセス許可が無いというから、アクセス権の設定に問題でもあるかと思ったのですが、PowerShellを実行したのと同じユーザーで、手動で削除すると特に問題なく削除できます。

■ 読取専用

よくよく調べると、原因はコレだったようです。
PowerShellでファイル削除(読取専用)

指定したフォルダ配下の全データを削除したかったので -Recurse オプションを付けていたのですが、どうやら中に属性が読み取り専用になっているファイルがあったようなのです。そして、Remove-Itemコマンドレットって標準だと読み取り専用ファイルは削除してくれないみたいです。

読み取り専用のファイルを削除する場合は、-Force ってオプションを付ければ良いようです。
Remove-Item 'Z:¥backup¥20150721_093150' -Recurse -Force


■ 後書き

随分前に、PowerShellでファイルを操作する方法を書いた記事(powerShellでファイル操作をしてみた)を投稿したのですが、これが抜けてましたね。

これ、-Recurse オプション(子要素も削除する)もそうなのですが、手動で削除した時の同じ動作を標準として、それとは別の動作をさせたい場合にオプションを付けるって方が分かりやすいと思うんですけどね。

そして、今回の記事を書くために過去の投稿履歴を調べていると、こんな記事(PowerShellで再帰的なファイル削除の検証)が出てきました。

・・・って、同じこと調べてるじゃん!

投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end --- スポンサードリンク

PageTop