MW211 EXIT

devlog
MSSQL／区切り文字で結合(横)
2021年01月20日 
PHPのimplode()みたいに単純に文字列で結合する方法。
┌──────────────────────────────────────┐
│SELECT CONCAT_WS(',','A','B','C')                                           │
└──────────────────────────────────────┘
分類：MSSQL
MSSQL／PDOとOracleリンクサーバと論理型
2020年11月30日 
┌──────────────────────────────────────┐
│$sql = <<<___SQL___                                                         │
│SELECT [論理] FROM OPENQUERY(ORACLE, 'SELECT 0 AS "論理" FROM DUAL') AS [o] │
│___SQL___;                                                                  │
│$sth = PDO->prepare($sql);                                                  │
│$sth->execute();                                                            │
│$row = $sth->fetch(PDO::FETCH_ASSOC);                                       │
│if (!$row['論理']) {                                                        │
│    // 偽：偽のつもりで0を返却しているのでこちらを期待するのだが            │
│} else {                                                                    │
│    // 真：こちらになってしまう                                             │
│}                                                                           │
└──────────────────────────────────────┘
論理型はOracleでもMSSQLでも、「0」と「1」を用いるのが普通だ。
ところが、上記のようにPDOを用いて、リンクサーバ経由で
Oracleから偽のつもりで「0」を取得したら、PHP上では真となってしまった。

原因は以下の通り。
(1) PHPで数値型の「0」や「0.0」、文字列型の「0」は、偽(FALSE)扱いなのだが
    文字列型の「0.0」は真(TRUE)扱いとなってしまう
(2) PDOで値を取得すると、(基本的に)文字列型となる
(3) OPENQUERY()でOracleから0を取得すると、float型の「0.0」となってしまう
    整数ではなく小数のnumber型がOracleでは基本のため

よって、(2)と(3)の組み合わせにより、(1)の条件に適合してしまったようだ。

ということで、MSSQLの世界ではbit型で論理型の代用をするので
SQL(MSSQL)上で以下のように変換してから、PDOで読み込むのがよいようだ。
┌──────────────────────────────────────┐
│SELECT CONVERT([bit], [論理]) AS [論理]～                                   │
└──────────────────────────────────────┘
分類：PDO、MSSQL
MSSQL／単純なCSV
2020年10月06日 
一列をすべてCSVにする方法。
┌──────────────────────────────────────┐
│WITH [表] (                                                                 │
│         [列]                                                               │
│     ) AS (                                                                 │
│         SELECT [列]                                                        │
│             FROM (                                                         │
│                 VALUES ('A'),('B'),('C')                                   │
│             ) AS [表]([列])                                                │
│     )                                                                      │
│SELECT REPLACE((SELECT [表].[列] AS [data()]                                │
│                    FROM [表]                                               │
│                    FOR XML PATH ('')       ), ' ', ',') AS [列たち];       │
└──────────────────────────────────────┘
分類：MSSQL
MSSQL／MERGE INTOで差分がある時だけ更新
2020年09月25日 
USINGの前にNOT EXISTSで同一データを除外。
(後で除外するとNOT MATHCED扱いとなるので注意)
同一判定をする時にはNULLに注意(NULLのままでは不一致になるので空文字に置換)。
┌──────────────────────────────────────┐
│MERGE INTO [出力Ｄ]                                                         │
│    USING (                                                                 │
│        SELECT [キー],                                                      │
│               [データ],                                                    │
│               GETDATE() AS [更新日時]                                      │
│            FROM [入力Ｄ]                                                   │
│            WHERE NOT EXISTS (                                              │
│SELECT *                                                                    │
│    FROM [出力Ｄ]                                                           │
│    WHERE ISNULL([出力Ｄ].[キー]  , '') = ISNULL([入力Ｄ].[キー]  , '')     │
│      AND ISNULL([出力Ｄ].[データ], '') = ISNULL([入力Ｄ].[データ], '')     │
│                  )                                                         │
│    ) AS [入力Ｄ]                                                           │
│      ON  [入力Ｄ].[キー] = [出力Ｄ].[キー]                                 │
│    WHEN MATCHED THEN                                                       │
│        UPDATE SET [データ]   = [入力Ｄ].[データ],                          │
│                   [更新日時] = [入力Ｄ].[更新日時]                         │
│    WHEN NOT MATCHED BY TARGET THEN                                         │
│        INSERT (                                                            │
│                [キー],                                                     │
│                [データ],                                                   │
│                [更新日時]                                                  │
│            ) VALUES (                                                      │
│                [入力Ｄ].[キー],                                            │
│                [入力Ｄ].[データ],                                          │
│                [入力Ｄ].[更新日時]                                         │
│            );"                                                             │
└──────────────────────────────────────┘
こうすると、更新日時の変更を差分があった時に限定できる。
分類：MSSQL
MSSQL／所有者を確認
2020年07月30日 
データベースの所有者を確認するSQL文。
┌──────────────────────────────────────┐
│SELECT [データベースDB].[name] AS [データベース],                           │
│       [プリンシパルDB].[name] AS [所有者]                                  │
│    FROM [sys].[databases] AS [データベースDB]                              │
│        LEFT JOIN [sys].[server_principals] AS [プリンシパルDB]             │
│          ON [データベースDB].[owner_sid] = [プリンシパルDB].[sid]          │
│    ORDER BY [データベースDB].[database_id] ASC;                            │
└──────────────────────────────────────┘
分類：MSSQL
MSSQL／ユーザ権限
2020年07月28日 
階層関係は以下の通り
DBサーバ→インスタンス→ログイン→ユーザ(→ロール)→データベース

詳細は以下の通り(●■は読取専用ユーザの設定を想定)

ログイン
├○Windows認証
└●SQLServer認証

サーバロール
├□sysadmin           …全部
├□serveradmin        …サーバ
├□securityadmin      …ロール
├□processadmin       …プロセス
├□setupadmin         …リンクサーバ
├□bulkadmin          …BULK INSERT
├□diskadmin          …ディスクファイル
├□dbcreator          …データベース更新
└■public             …

データベース
├□master             …システムデータベース
├□model              …  〃
├□msdb               …  〃
├□tempdb             …  〃
└■(ユーザDB)

固定データベースロール
├□db_owner           …全部
├□db_securityadmin   …ロール
├□db_accessadmin     …アクセス
├□db_backupoperator  …バックアップ
├□db_ddladmin        …定義(CREATE,ALTER)
├□db_datawriter      …更新(INSERT,UPDATE,DELETE)
├■db_datareader      …参照(SELECT)
├■db_denydatawriter  …更新の禁止
├□db_denydatareader  …参照の禁止
└■public
※msdbの場合はさらに以下もある
├□db_ssisadmin
├□db_ssisltduser
├□db_ssisoperator
├□dc_admin
├□dc_operator
├□dc_proxy
├□DatabaseMailUserRole
├□PolicyAdministratorRole
├□ServerGroupAdministratorRole
├□ServerGroupReaderRole
├□SQLAgentOperatorRole
├□SQLAgentReaderRole
├□SQLAgentUserRole
├□TargetServersRole
├□UtilityCMRReader
├□UtilityIMRReader
└□UtilityIMRWriter
分類：MSSQL
MSSQL／リンクサーバーでエラー
2020年02月20日 
【現象】
  リンクサーバを用いて、Oracleの内容をMSSQLに引っぱってくる場合
  テーブル項目にtimestamp型の列が含まれていると以下エラーとなる
  →SELECTでその列を指定しなくともエラーが発生する
┌──────────────────────────────────────┐
│メッセージ 7354、レベル 16、状態 1、行 1                                    │
│リンク サーバー "■" の OLE DB プロバイダー "OraOLEDB.Oracle" により、      │
│無効なメタデータが列 "(timestamp型列)" に指定されました。                   │
│The data type is not supported.                                             │
└──────────────────────────────────────┘

【対策】
  OPENQUERYを用いる
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM OPENQUERY(■, 'SELECT * FROM "表"');                               │
└──────────────────────────────────────┘
分類：MSSQL
MSSQL／一時表の削除方法
2020年01月22日 
事前に存在チェックをしてから削除する方法。
┌──────────────────────────────────────┐
│IF OBJECT_ID(N'tempdb..#一時表', N'U') IS NOT NULL                          │
│BEGIN                                                                       │
│    DROP TABLE #一時表;                                                     │
│END;                                                                        │
└──────────────────────────────────────┘
分類：MSSQL
MSSQL／大文字小文字の区別
2019年10月28日 
MSSQLだと、大文字と小文字を基本的に区別しない。
それは、デフォルトだと「Japanese_CI_AS」だからである。

区別にするには「COLLATE Japanese_CS_AS」オプションをつければよい。

まず、各オプション値については、以下の通り。
┌──────────────────────────────────────┐
│「Japanese_CI_AI_KI_WI」～「Japanese_CS_AS_KS_WS」の組み合わせもしくは      │
│「Japanese_BIN」、「Japanese_BIN2」が設定可能                               │
│┌─┬────────────┐┌─┬────────────┬───┐    │
││S │区別する     (Sensitive)││C │大文字と小文字    (Case)│a    A│    │
│├─┼────────────┤├─┼────────────┼───┤    │
││I │区別しない (Insensitive)││A │濁点や半濁点    (Accent)│か  が│    │
│└─┴────────────┘├─┼────────────┼───┤    │
│                                │K │ひらがなとカタカナ(Kana)│あ  ア│    │
│                                ├─┼────────────┼───┤    │
│                                │W │半角と全角       (Width)│a   ａ│    │
│                                └─┴────────────┴───┘    │
└──────────────────────────────────────┘

次に「COLLATE」句の使い方は、以下の通り。

表生成時に定義してしまう場合
┌──────────────────────────────────────┐
│CREATE TABLE [表] (                                                         │
│    [文字列]  [nvarchar](16) COLLATE Japanese_CS_AS NULL,                   │
│);                                                                          │
└──────────────────────────────────────┘

WHERE文にて作用させる場合
┌──────────────────────────────────────┐
│SELECT *                                                                    │
│    FROM [TESTDB].[dbo].[T_TEST2]                                           │
│    WHERE [文字列] = 'a' COLLATE Japanese_CS_AS;                            │
└──────────────────────────────────────┘

DISTINCT句にて作用させる場合
┌──────────────────────────────────────┐
│SELECT DISTINCT [文字列] COLLATE Japanese_CS_AS AS [文字列]                 │
│    FROM [TESTDB].[dbo].[T_TEST2]                                           │
└──────────────────────────────────────┘
分類：MSSQL
MSSQL／週単位で集計
2019年10月24日 
日付ごとのデータを週単位で集計する場合は、
集計キーとなる列(同一週内で同一の値となるもの)を作り、それでGROUP BYすればよい。
┬──────────────────────────────────────┬
│毎週日曜日を起点する場合                                                    │
┴──────────────────────────────────────┴
【集計キーを起点日からの週目とする方法】
┌──────────────────────────────────────┐
│SELECT [日付],                                                              │
│       DATEDIFF(week, '2019-09-29', [日付]) AS [週目]                       │
│    FROM (                                                                  │
│        VALUES ('2019-10-01'),('2019-10-02'),('2019-10-03'),('2019-10-04'), │
│               ('2019-10-05'),('2019-10-06'),('2019-10-07'),('2019-10-08'), │
│               ('2019-10-09'),('2019-10-10'),('2019-10-11'),('2019-10-12'), │
│               ('2019-10-13'),('2019-10-14'),('2019-10-15'),('2019-10-16'), │
│               ('2019-10-17'),('2019-10-18'),('2019-10-19'),('2019-10-20'), │
│               ('2019-10-21'),('2019-10-22'),('2019-10-23'),('2019-10-24'), │
│               ('2019-10-25'),('2019-10-26'),('2019-10-27'),('2019-10-28'), │
│               ('2019-10-29'),('2019-10-30'),('2019-10-31')                 │
│    ) AS [表]([日付])                                                       │
│    ORDER BY [日付] ASC;                                                    │
└──────────────────────────────────────┘
  ※起点日は日～土曜日のいずれを指定しても同じ結果になる
────────────────────────────────────────
【集計キーを年頭からの週目とする方法】
┌──────────────────────────────────────┐
│SELECT [日付],                                                              │
│       DATEPART(week, [日付]) AS [週目]                                     │
│    FROM (                                                                  │
│        VALUES ('2019-10-01'),('2019-10-02'),('2019-10-03'),('2019-10-04'), │
│               ('2019-10-05'),('2019-10-06'),('2019-10-07'),('2019-10-08'), │
│               ('2019-10-09'),('2019-10-10'),('2019-10-11'),('2019-10-12'), │
│               ('2019-10-13'),('2019-10-14'),('2019-10-15'),('2019-10-16'), │
│               ('2019-10-17'),('2019-10-18'),('2019-10-19'),('2019-10-20'), │
│               ('2019-10-21'),('2019-10-22'),('2019-10-23'),('2019-10-24'), │
│               ('2019-10-25'),('2019-10-26'),('2019-10-27'),('2019-10-28'), │
│               ('2019-10-29'),('2019-10-30'),('2019-10-31')                 │
│    ) AS [表]([日付])                                                       │
│    ORDER BY [日付] ASC;                                                    │
└──────────────────────────────────────┘
  ※年を跨ぐとリセットされるので年末年始の識別が難解
┬──────────────────────────────────────┬
│毎週任意の曜日を起点する場合                                                │
┴──────────────────────────────────────┴
【集計キーを基準日とする方法】(下記例では毎週月曜日を基準日としている)
┌──────────────────────────────────────┐
│SELECT [日付],                                                              │
│       CONVERT(date,                                                        │
│               DATEADD(                                                     │
│                   day,                                                     │
│                   -1 * (                                                   │
│                       DATEPART(weekday,                                    │
│                                DATEADD(day, -1 * 2 + 1, [日付]))           │
│                   ) + 1,                                                   │
│                   [日付])                                                  │
│       ) AS [週集計日]                                                      │
│    FROM (                                                                  │
│        VALUES ('2019-10-01'),('2019-10-02'),('2019-10-03'),('2019-10-04'), │
│               ('2019-10-05'),('2019-10-06'),('2019-10-07'),('2019-10-08'), │
│               ('2019-10-09'),('2019-10-10'),('2019-10-11'),('2019-10-12'), │
│               ('2019-10-13'),('2019-10-14'),('2019-10-15'),('2019-10-16'), │
│               ('2019-10-17'),('2019-10-18'),('2019-10-19'),('2019-10-20'), │
│               ('2019-10-21'),('2019-10-22'),('2019-10-23'),('2019-10-24'), │
│               ('2019-10-25'),('2019-10-26'),('2019-10-27'),('2019-10-28'), │
│               ('2019-10-29'),('2019-10-30'),('2019-10-31')                 │
│    ) AS [表]([日付])                                                       │
│    ORDER BY [日付] ASC;                                                    │
└──────────────────────────────────────┘
────────────────────────────────────────
分類：MSSQL
前へ 1 2 3 4 5 6 7 8 9 10 11 12 13 次へ