愚者の経験

「また今度」はほとんどこない

カテゴリーアーカイブ: SQLServer

[SQL Server][Access]日時のフィールドで苦労した話

Accessで時刻を入力するのは若干面倒くさい。最終的に使いたい項目が日時だとしても
「日付」と「時刻」の別にフィールドを設けることが多いです。以下長文ごめんなさい。

テキストを1つにすると日付だけ既定値で入れられることが出来たとしても
時刻を続けて入力するにはキャレットを移動させるしかなく、F2キーやマウスをクリックしたり矢印キーを
使用するのを避けられないのが主な理由です。(テキスト毎にEnterイベントでSelStart入れればそんなもんですがそれもちょっと…)
他の理由としては自前のカレンダーや時刻の入力フォームが別にある場合該当のテキストへの入力が複雑になるということもあります。

さらに大きな問題としてOSの時刻の表示が「H:mm」になっているせいで定型入力が「00:00」系で例えば「09:00」
と入力しても再表示時に「9:00」となって、フォーカスすると「9_:00」となって出るときにエラーになって安定しなかったり、
時刻に24時以上を打ち込みたい場合はどうしようもないという理由で最近は時刻は文字列型にしています。
(エラーの方に関しては私が体験したもので必ずでるというわけではないです。今やってみても再現出来ない…。)

そんなこんなでフィールドを2つに割ったとして今度は利用する際に毎回「[日付]+cdate(nz([時刻],”00:00″))=……」とか
しなければいけなくなるのですが、SQLServerを利用している場合は「計算列」という機能があるので
これを使うんですがちょっと問題があります。

24時以上を入力しない場合は計算列の数式に「[日付]+cast(isnull([時刻],’00:00′) as datetime)」とやればいいんですが
「列’xxxx’の数式を検証中にエラーが発生しました。変更を取り消しますか?」と言われてしまいます。
うーんあってるはずなのに…多少書き方を変えても同じです。
どうすればいいかというと上記警告を無視して(上記ダイアログを[いいえ]で突破する)、テーブルの保存時にも似たようなことを
警告されますが再度無視し、テーブルを見ると…ちゃんと出来てますorz
この時castで書いたはずの型変換はconvert(datetime,isnull([時刻],’00:00′))に書き換わっていました。
これで書けばいいのか~とconvertで書いてもやっぱりエラー笑

「悩みまくったのは一体何だったんだ」と思いました。インデックス貼るまでは。
続いてアプリがこなれて仕様も固まってきた頃、速度改善のためにインデックスを作成しようと思って
上記の計算列を選ぶと「インデックスの作成に失敗しました」という趣旨のエラー発生。
「あ~計算列はインデックスキー列に含めないのね」と勝手に思って仕方なく付加列に追加…
出来ません!再度エラーが出ます。
なんで?と思って調べたら、以下の情報がヒット
https://msdn.microsoft.com/ja-jp/library/ms189292.aspx
「決定的」であるかどうかが重要で文字列型を日付にconvertする時は値が決定的になる第三引数が必要だそうです。
なので「convert(datetime,isnull([時刻],’00:00′),108)」とすると保存時にエラーは出ますが、インデックスは使えるようになりました。

うーん。言いたいことは分かるんですがエラー出るのは気持ち悪い。とりあえず24時対応しましたところ
(([日付]+CONVERT([int],left(isnull([時刻],’00:00′),(2)))/(24))+CONVERT([datetime],(CONVERT([varchar](2),CONVERT([int],left(isnull([時刻],’00:00′),(2)))%(24))+’:’)+right(isnull([時刻],’00:00′),(2)),(108)))
これだとエラー出ないんですよね…不思議。

[SQL Server][SQL][Tips]pivotを使ってAccessのクロス集計クエリっぽいことをする2

前回の続きというか応用

テーブル用意(前回と同じです)
pivott

Q.「いやね、金額と個数の両方合計してだしてくれない?」とか言われたらどうしますか?
A.イラッとする
見てのとおりpivotでは集計項目は1つしか選べません。なので同じデータを2回selectして
項目を変えればもちろん取得出来ます。

	select Y.*
		,(select top 1 A.売上先名称 from dbo.mtbl_売上先 as A where A.売上先ID=Y.売上先ID) as 売上先名称
	from (
		select A.売上先ID,B.商品名称,sum(A.金額) as 金額計
		from dbo.tbl_売上明細 as A
			inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
			left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
		group by A.売上先ID,C.分類名称,B.商品名称
	) as X
	pivot (
		max(X.金額計) for X.商品名称 in 
			(みかん,りんご,トマト,きゅうり)
	) as Y

	select Y.*
		,(select top 1 A.売上先名称 from dbo.mtbl_売上先 as A where A.売上先ID=Y.売上先ID) as 売上先名称
	from (
		select A.売上先ID,B.商品名称,sum(A.個数) as 個数計--変更部分
		from dbo.tbl_売上明細 as A
			inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
			left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
		group by A.売上先ID,C.分類名称,B.商品名称
	) as X
	pivot (
		max(X.個数計) for X.商品名称 in 
			(みかん,りんご,トマト,きゅうり)
	) as Y

あとは2つのビューを「inner join」なり「union all」なりすればいいのですが
「selectのコスト重すぎ…2回もできない…」という方、「inner join」を使う代わりにこんな方法もあります。

	select Y.*
	from (
                -- ※※※※※※※※※※※※
		select D.売上先ID
			,D.商品名称+E.Col as Col,E.Val
		from (
			select A.売上先ID,B.商品名称,sum(A.個数) as 個数計,sum(A.金額) as 金額計
			from dbo.tbl_売上明細 as A
				inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
				left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
			group by A.売上先ID,C.分類名称,B.商品名称
		) as D
			cross apply (
				select '個数',D.個数計 union all
				select '金額',D.金額計
			) as E (Col,Val)
                -- ※※※※※※※※※※※※
	) as X
	pivot (
		max(X.Val) for X.Col in
			(みかん個数,りんご個数,トマト個数,きゅうり個数
			,みかん金額,りんご金額,トマト金額,きゅうり金額)
	) as Y

なんじゃこりゃぁ…とか思いますが私も最初はそうでした(笑)当然(?)ミソはcross applyの部分です。
この部分で「項目名」と「値」のセットをそれぞれunionします。
今回の場合、集計項目が「個数」と「金額」ですので、
項目名に’個数’というリテラル文字、値に「個数計=sum(個数)」
項目名に’金額’というリテラル文字、値に「金額計=sum(金額)」
となりこれらをunionし、cross applyします。

この時点でどうなっているかというと※の間のselectを実行すればわかりますが
売上先ID、Col、Val
10、りんご個数、sum(個数)
10、りんご金額、sum(金額)

というような形になって一行のデータを個数と金額の2行に分けています。
あとはpivotで(みかん,りんご,トマト,きゅうり)だったのを
(みかん個数,りんご個数,トマト個数,きゅうり個数,みかん金額,りんご金額,トマト金額,きゅうり金額)に
変更して値を集めているというわけです。機会があったらぜひ利用してみてください。

参考URL:http://stackoverflow.com/questions/18662012/sql-server-pivot-on-multiple-columns

[SQL Server][SQL][Tips]pivotを使ってAccessのクロス集計クエリっぽいことをする1

Accessでいうところの「クロス集計クエリ」ですがSQL Serverだと「pivot」で実現は可能です。
書き方覚えられないので毎回ネットで調べてますが(笑)

テーブル用意
pivott

	select Y.*
		,(select top 1 A.売上先名称 from dbo.mtbl_売上先 as A where A.売上先ID=Y.売上先ID) as 売上先名称
	from (
		select A.売上先ID,B.商品名称,sum(A.金額) as 金額計
		from dbo.tbl_売上明細 as A
			inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
			left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
		group by A.売上先ID,C.分類名称,B.商品名称
	) as X
	pivot (
		max(X.金額計) for X.商品名称 in 
			(みかん,りんご,トマト,きゅうり)
	) as Y

pivotを使う際、直前のselectで必要項目を絞ってなくてもgroup byの様に怒られないです(笑)
ですがその代わりに集計もしてくれません…ちゃんと指定しましょう。

	select Y.売上先ID,Y.きゅうり,Y.みかん,Y.りんご,Y.トマト
		,(select top 1 A.売上先名称 from dbo.mtbl_売上先 as A where A.売上先ID=Y.売上先ID) as 売上先名称
	from (
		select A.売上先ID,B.商品名称,sum(A.金額) as 金額計,sum(A.個数) as 個数計
		from dbo.tbl_売上明細 as A
			inner join dbo.mtbl_商品 as B on B.商品ID=A.商品ID
			left join dbo.mtbl_商品分類 as C on C.分類ID=B.分類ID
		group by A.売上先ID,C.分類名称,B.商品名称
	) as X
	pivot (
		max(X.金額計) for X.商品名称 in 
			(みかん,りんご,トマト,きゅうり)
	) as Y

pivotの中を『sum(金額) for 商品名称 in (みかん,りんご,トマト,きゅうり)』みたいに書いたほうが見た目
わかりやすいと思うかもしれませんが、私の頭がついていけないので行をまとめてからpivotすると決めています笑

[Access][ODBC]新規レコード確定時に入力データが別レコードに化ける

これを最初に見た時「ん?」となりました。以下の状況で発生します。
0.環境はSQL SERVER 2012または2008 R2とAccess2010のODBC接続(他でも起きるかはわかりません。確認出来たのはこの組み合わせ)
1.入力するテーブル[テーブルA]の主キーがidentityである
2.入力するテーブルのafter insertトリガーが設定してありその中でidentity列があるテーブル[テーブルB]にinsertを行う。
3.テーブルBの新規identityがテーブルAの主キーデータとして存在している
この状況で発生します。なんとなくこう書いただけでどうなるか検討がつく方は素晴らしいです。

テーブルAに新規にレコードを入力すると
1.テーブルA入力中は(新規)になっている(Bの次のidentityは1)
odbc1
odbc2
2.入力確定時にidentityを取得しに行く
3.入力確定時にテーブルBにinsertしているので取得するidentityはテーブルBのもの
4.テーブルAの(新規)の部分がテーブルBのidentityになりその値がテーブルAの主キーとしてデータが取得される
確定前
odbc3
確定後、レコードがidentity1が1のレコードに化ける(identity1に1が2行ある…)
odbc4
テーブルBは1が挿入されています。
odbc5
テーブルAはリクエリすると元に(?)戻る
odbc6

・回避方法
入力テーブルかトリガーのidentityを使わない…しかないのか?

テストテーブル

CREATE TABLE [dbo].[テーブルA](
	[identity1] [int] IDENTITY(1,1) NOT NULL,
	[value1] [int] NULL,
	[value2] [nvarchar](50) NULL,
 CONSTRAINT [PK_テーブルA] PRIMARY KEY CLUSTERED 
(
	[identity1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go
CREATE TABLE [dbo].[テーブルB](
	[identity2] [int] IDENTITY(1,1) NOT NULL,
	[value] [int] NULL,
 CONSTRAINT [PK_テーブルB] PRIMARY KEY CLUSTERED 
(
	[identity2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

テストトリガー

CREATE TRIGGER [dbo].[utr_afiテーブルA]
   ON [dbo].[テーブルA]
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;

	insert dbo.テーブルB(value)
	select 1

元に戻したいときはこれで
DBCC CHECKIDENT (テーブルB,RESEED ,0)

[SQL Server][SQL][Tips]cteを使おう!としてみたが難しい。その3

これを使ってSplitできるんじゃないと思って考えました。
開始位置と終了位置を返すselect文をcteで
初期のアンカーメンバーを1と最初の区切り文字の場所として
区切り文字+1と次の区切り文字を区切り文字がなくなるまで再帰します。
そうしてできたクエリでsubstringすれば一発でSplitの出来上がり。
と思ったのですがすでにやってる人いましたのでそれを載せます。
参考URL:http://www.wisesoft.co.uk/scripts/t-sql_cte_split_string_function.aspx

CREATE FUNCTION fnSplitString(@str nvarchar(max),@sep nvarchar(max))
RETURNS TABLE
AS
RETURN
	WITH a AS(
		SELECT CAST(0 AS BIGINT) as idx1,CHARINDEX(@sep,@str) idx2
		UNION ALL
		SELECT idx2+1,CHARINDEX(@sep,@str,idx2+1)
		FROM a
		WHERE idx2>0
	)
	SELECT SUBSTRING(@str,idx1,COALESCE(NULLIF(idx2,0),LEN(@str)+1)-idx1) as value
	FROM a

やはり目のつけどころが皆さん違いますね。
これは作業用のテーブルにinsertとかしませんので個人的には相当スッキリします笑

[SQL Server][SQL][Tips]cteを使おう!としてみたが難しい。その2

cteに機能の再帰ですが実際何に使えるのかというとほとんど
・連続データの作成
になると思います。マスタなどに親コードみたいなみたいなものが
存在し、順番に追って最上位を取得するという場合にも再帰は役に立ちます。
が、あまりそのような状況に巡り合わないのでそれの説明は省きます。

with cte(inc) as (
	select 1
	union all
	select inc+1
	from cte
	where inc<30
)
select *
from cte

これで1~30までの連続データができます。
年間カレンダーを作るときなどにも使えますね。
注意しなければならない点としては
・100を超える再帰をする場合そのままではエラーになるのでoption (maxrecursion N)が必要

with cte(inc) as (
	select 1
	union all
	select inc+1
	from cte
	where inc<200
)
select *
from cte
option (maxrecursion 0)

・パラメータ等使って回転数を制御した場合等で0行を返すことはできない

declare @maxinc int
set @maxinc=0;
with cte(inc) as (
	select 1
	union all
	select inc+1
	from cte
	where inc<@maxinc
)
select *
from cte

こういうのですね…初期アンカーがある(select 1)ので
1行は必ず返ります。最後のselectにwhere句つけてください。

[SQL Server][SQL][Tips]cteを使おう!としてみたが難しい。その1

SQL Serverをお使いの方「cte」をご存知だろうか?

この「cte」自体は
「テーブルに別名をつけることができ、1ステートメント内であれば何度も参照できる」
というものです。Common Table Expression(共通テーブル式)
(例)

;with cte (nb,txt) as (
    select 0,'aaa' union all
    select 1,'bbb' union all
    select 2,'ccc' union all
    select 3,'111'
)
select count(*) as countcte
	,(select max(nb) from cte) as maxnb
	,(select min(nb) from cte) as minnb
	,(select max(txt) from cte) as maxtxt
	,(select min(txt) from cte) as mintxt
from cte

ぱっと思いつく用途としては「自己結合の簡略化」です。
複雑なクエリで累計するときなど自己結合が必要な場合にSQLが冗長にならずに済む可能性があります。
これはAccessの参照クエリ、SQL Serverのビューを一時的に作るような使い方になりますので
それほど難しい使い方ではないのですが、cteにはほかにも独自(?)の機能があります。

それが「再帰」です。
調べても「なるほど、わからん」という感じにしかならないです…。少しわかりかけてきたので
他のかたのために、主に自分のためにメモしておきます。

[SQL Server]ユーザーソース一覧を作る

バックエンドにSQLServerで開発しているときに
「ストアドとかビューとか関数とか何個あるか数えたい」
「あの列をSelectしてるストアドってどれ?」
「最近に修正したオブジェクトってなんだっけ?」
とか思うときがあります。

そうゆうときにあると非常に便利だと思うのが以下のビューです。
ストアド、ビュー、ユーザー定義関数のCreate文をデータにもってきてますので
Likeとかで検索できます。(definition列)

「それじゃテーブルも取得したいよ」と思ったのが運の尽き…
テーブルのCreate文って簡単に取得できないんですね…
後半はかなりやっつけなのであんまりあてにしないでください笑
PrimaryKey以外のIndex?
sys.indexesから頑張って取得してください…もう疲れました…

たぶんあっている
・列名
・データ型
・identity(seed,increment)
・rowguid
・計算列
・Null許可
・既定値

自信なし
・PrimaryKey

表示なし
・PrimaryKey以外のIndex
・付加列(当然です笑)

	select O.name
		,O.type_desc
		,O.create_date
		,O.modify_date
		,M.definition COLLATE Japanese_Bin as [definition]
	from sys.objects as O
		inner join sys.sql_modules as M on M.object_id=O.object_id
	where O.type_desc in 
			('SQL_STORED_PROCEDURE'
			,'SQL_SCALAR_FUNCTION'
			,'SQL_TABLE_VALUED_FUNCTION'
			,'VIEW')
	union all
	select T.name
		,T.type_desc
		,T.create_date
		,T.modify_date
		,'CREATE TABLE ['+D.TABLE_SCHEMA+'].['+T.name+']'+char(13)+char(10)+'('
			+(
				-- 列情報
				select '['+S.COLUMN_NAME+']'
					+coalesce
						((
							select ' AS '+P.definition+','
							from sys.computed_columns as P
							where P.object_id=C.object_id
								and P.column_id=C.column_id)
						,'['+S.DATA_TYPE+']'+isnull('('+cast(S.CHARACTER_MAXIMUM_LENGTH as varchar)+')','')
							+isnull((
							select ' IDENTITY('+cast(I.seed_value as varchar)
								+','+cast(I.increment_value as varchar)+')'
							from sys.identity_columns as I
							where I.object_id=C.object_id
								and I.column_id=C.column_id),'')
							+case when C.is_rowguidcol=1 then ' ROWGUIDCOL' else '' end
							+case when S.IS_NULLABLE='YES' then ' NULL,' else ' NOT NULL,' end)+char(13)+char(10)
				from information_schema.columns as S
					inner join sys.columns as C on C.name=S.COLUMN_NAME and C.object_id=T.object_id
				where TABLE_NAME=T.name
				order by ORDINAL_POSITION
				for xml path(''),type).value('.','varchar(max)')
			+isnull((
				-- PrimaryKey
				select ' CONSTRAINT ['+X.name+'] PRIMARY KEY '+X.type_desc+'('
					+replace((
						-- キー情報
						select '['+(select name from sys.columns
									where object_id=T.object_id and column_id=XC.column_id)+'] '
							+case when XC.is_descending_key=1 then 'DESC' else 'ASC,' end
						from sys.index_columns as XC
						where XC.object_id=T.object_id
						order by XC.index_column_id
						for xml path(''),type).value('.','varchar(max)')+') WITH ',',)',')')
					+'(PAD_INDEX='			+case when X.is_padded			=0 then 'OFF' else 'ON' end
					+',IGNORE_DUP_KEY='		+case when X.ignore_dup_key		=0 then 'OFF' else 'ON' end
					+',ALLOW_ROW_LOCKS='	+case when X.allow_row_locks	=0 then 'OFF' else 'ON' end
					+',ALLOW_PAGE_LOCKS='	+case when X.allow_page_locks	=0 then 'OFF' else 'ON' end
					+',STATISTICS_NORECOMPUTE=OFF) ON [PRIMARY]) ON [PRIMARY]'
				from sys.indexes as X
				where X.object_id=T.object_id
					and X.is_primary_key=1),'')
			+char(13)+char(10)+'GO'+char(13)+char(10)
			+isnull((
				-- 既定値の設定
				select 'ALTER TABLE ['+D.TABLE_SCHEMA+'].['+T.name+'] ADD CONSTRAINT '
					+'['+F.name+'] DEFAULT '+F.definition+' FOR '
					+'['+(select A.name from sys.columns as A
							where A.object_id=T.object_id and A.column_id=F.parent_column_id)+'] '
					+char(13)+char(10)+'GO'+char(13)+char(10)
				from sys.default_constraints as F
				where F.parent_object_id=T.object_id
				order by F.parent_column_id
				for xml path(''),type).value('.','varchar(max)'),'')
	from sys.tables as T
		inner join information_schema.tables as D on D.TABLE_NAME=T.name

[SQL]SQL Serverで値比較の落とし穴

かなり久しぶりにブログに記事を書く気がします。

別の人が書いたSQLを見て直していたんですが期待した結果が
返ってこないので調べてみたところ…
なんと「0=”」はTrueになることが判明。(なんかでも前も同じ事あったような…)
なので以下の様なちょっと不思議なSQLも書くことができます。

	select case when ''=0 then 1 end as are
		,case when 0='0' then 1 end as oya
		,case when '0'='' then 1 end as un

照合順序の辺りで解決できるのかもしれませんが、腑に落ちないのは私だけ?
とりあえず暗黙の型変換には気をつける。cast(” as int)は0!!

[SQL]動的にin句を変化させるような時の代替案

ふと気づいた。

        select *
        from table1
        where id in(@param)

というような形で「@paramに動的な複数値をカンマ区切りで渡したい」と
いう要望は結構ある。しかしこの状態では期待した結果にならないので
executeやsp_executesql(動的SQL実行)を使う

        execute ('select * from table1 where id in('+@param+')')

これは期待通りの結果になるのだが、いかんせんAccessと相性が悪いため
(カラム名が解決できないのでコントロールソースが手入力になるなど)SQLCLRを使って
カンマ区切りをテーブルにして返す関数(Split)を作成すると良いと最初は思いました。

苦労してSQLCLRを調べたりしたんですがそんなことしなくてもできます。
もちろん動的実行しなくてもできます。こうすればよかったんです…。

        select *
        from table1
        where ','+@param+',' like '%,'+cast(id as varchar)+',%'

なんでもっと早く気づかなかった自分…orz
多分インデックスも効くんじゃないかな。