複数の文字列のsplit ⇒ 行処理するSQL （ORACLE）

先日、Oracleで１つにカンマ区切り文字列を行変換する例を書いた
文字列のsplit ⇒ 行処理するSQL （ORACLE） - Oboe吹きプログラマの黙示録

１つではなく、複数のカンマ区切り文字列から、TABLE を作成する方法は、以下のように、
TABLE(CAST(MULTISET( 区切りレベル CONNECT BY REGEXP_SUBSTR 対象を
各々に適用させれば良い。

例）

WITH prefcture_strings AS (
    SELECT '東京,神奈川,千葉,茨城' AS value FROM dual
), prfctcd_strings AS (
    SELECT '13,14,12,08' AS value FROM dual
), point_strings AS (
    SELECT '100,80,60,55' AS value FROM dual
), splits AS (
     SELECT lines.column_value AS ROWNO
    , TRIM(REGEXP_SUBSTR(prefcture_strings.value,'[^,]+', 1, lines.column_value))  AS PREFECTURE
    , TRIM(REGEXP_SUBSTR(prfctcd_strings.value,'[^,]+', 1, lines.column_value))    AS PRFCTCD
    , TRIM(REGEXP_SUBSTR(point_strings.value,'[^,]+', 1, lines.column_value))      AS POINT
    FROM prefcture_strings, prfctcd_strings, point_strings
        ,TABLE(CAST(MULTISET(SELECT LEVEL FROM dual
CONNECT BY REGEXP_SUBSTR(prefcture_strings.value, '[^,]+', 1, LEVEL) IS NOT NULL) AS sys.odciNumberList
     )) lines
)
SELECT ROWNO
       , PREFECTURE
       , PRFCTCD
       , POINT
FROM splits

FROM句で対象を並べ、REGEXP_SUBSTR で各カンマ区切り文字列 FROM dual を指定する。
splits テーブルSELECT結果は以下のようになる。

ROWNO	PREFECTURE	PRFCTCD	POINT
1	東京	13	100
2	神奈川	14	80
3	千葉	12	60
4	茨城	08	55

区切りの数が全て同じであることが前提である。

UNPIVOTで格納される値を日本語にする【Oracle】

以下のテーブルがある。
JOBROUTE テーブル

id	STATION	CAR	TRAIN	WALK
1	新宿	5	12	120
2	渋谷	7	18	130

このCAR, TRAIN, WALK 列をUNPIVOTする。

SELECT 
     id, STATION, M_TYPE, T_MINUTES
FROM JOBROUTE 
UNPIVOT(T_MINUTES FOR M_TYPE IN(CAR , TRAIN , WALK))

結果

id	STATION	M_TYPE	T_MINUTES
1	新宿	CAR	5
1	新宿	TRAIN	12
1	新宿	WALK	120
2	渋谷	CAR	7
2	渋谷	TRAIN	18
2	渋谷	WALK	130

UNPIVOT で作成される列名を日本語にしたい。
CAR ⇒ 車
TRAIN ⇒ 電車
WALK ⇒ 徒歩
というように。。。

安全で確実な方法は、SELECT句でCASE文を書く方法

SELECT 
      id
     ,STATION
     ,CASE M_TYPE
          WHEN 'CAR' THEN '車'
          WHEN 'TRAIN' THEN '電車'
          WHEN 'WALK' THEN '徒歩'
      END AS M_TYPE
     ,T_MINUTES
FROM JOBROUTE 
UNPIVOT(T_MINUTES FOR M_TYPE IN(CAR , TRAIN , WALK))

SELECTでCASE式を避ける方法、一旦サブクエリになってしまうが、

WITH TJOBROUTE AS (
    SELECT 
      id
     ,STATION
     ,CAR       AS "車"
     ,TRAIN     AS "電車"
     ,WALK      AS "徒歩"
     ,T_MINUTES
FROM JOBROUTE 
)
SELECT 
      id
     ,STATION
     ,M_TYPE
     ,T_MINUTES
FROM TJOBROUTE 
UNPIVOT(T_MINUTES FOR M_TYPE IN("車", "電車", "徒歩"))

ここで、注意しなければならないのは、
Oracle は、AS 句で日本語など２バイト文字を使う場合は、シングルクォートではなく、
ダブルクォーテーションを使うことである。

id	STATION	M_TYPE	T_MINUTES
1	新宿	車	5
1	新宿	電車	12
1	新宿	徒歩	120
2	渋谷	車	7
2	渋谷	電車	18
2	渋谷	徒歩	130

Oracle UNPIVOT で注意すべき点
NULLの値の列は、デフォルトでは、行に変換されず無視される。
したがって、NULLでも行を作成したければ、INCLUDE NULLS を指定しなければならない。

WITH TJOBROUTE AS (
    SELECT 
      id
     ,STATION
     ,CAR       AS "車"
     ,TRAIN     AS "電車"
     ,WALK      AS "徒歩"
     ,T_MINUTES
FROM JOBROUTE 
)
SELECT 
      id
     ,STATION
     ,M_TYPE
     ,T_MINUTES
FROM TJOBROUTE 
UNPIVOT INCLUDE NULLS (T_MINUTES FOR M_TYPE IN("車", "電車", "徒歩"))

PostgreSQL は NULLでも処理してくれるのでこの include nulls の指定は必要がない。

文字列のsplit ⇒ 行処理するSQL （PostgreSQL）

文字列のsplit ⇒ 行処理するSQL （ORACLE） - Oboe吹きプログラマの黙示録
を書いたので、PostgreSQL で同じことをしてみる。

方法は２通り。

string_to_array() を使う。

WITH strings AS (
    SELECT UNNEST(string_to_array('A,B,C', ',')) AS split_value
)
SELECT 
    row_number() OVER () AS position
  , split_value
  , COUNT(*) OVER()      AS splitlength
FROM strings

string_to_table() を使う

WITH strings AS (
    SELECT string_to_table('A,B,C', ',') AS  split_value
)
SELECT 
    row_number() OVER () AS position
  , split_value
  , COUNT(*) OVER()      AS splitlength
FROM strings

結果は、

position	split_value	splitlength
1	A	3
2	B	3
3	C	3

文字列のsplit ⇒ 行処理するSQL （ORACLE）

UNPIVOT に近い、わざわざSQLで行うことがナンセンスな気もしたが、
split 対象の文字列を mybatis などから動的パラメータとして指定して結果を他のテーブルと結合して
処理するＳＱＬなど応用範囲は広がると思った。

Oracle の場合のサンプル

’A,B,C' をカンマ区切りで分割、

WITH strings AS (
    SELECT
        'A,B,C' AS value
    FROM dual
)
, splits AS (
    SELECT
        lines.column_value AS position
        , REGEXP_SUBSTR(strings.value,'[^,]+', 1, lines.column_value) AS split_value
        , COUNT(*) OVER()  AS splitlength
    FROM strings
        ,TABLE(CAST(MULTISET(SELECT LEVEL FROM dual
 CONNECT BY REGEXP_SUBSTR(strings.value, '[^,]+', 1, LEVEL) IS NOT NULL) AS sys.odciNumberList
                   )
              ) lines
)
SELECT * FROM splits

結果、、

position	split_value	splitlength
1	A	3
2	B	3
3	C	3

WITH句を使ったINSERTの書き方

WITH句を使った検索クエリは、

WITH  xxxx AS (
    ～
)
SELECT * FROM xxxx

のように、WITH句の後で、SELECT ～ FROM with句で定義した名称
と書くものだが、
INSERT INTO ～ SELECT で使用する場合、
INSERT INTO の前に書くのか？、後ろに書くのか？

どうもデータベースによって違うようである。
PostgreSQL は、
マニュアル
https://www.postgresql.jp/docs/17/queries-with.html#QUERIES-WITH-MODIFYING
に書いてあるように、 INSERT INTO の前に書く
サンプル

WITH temp AS (
   SELECT 
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
   FROM TEST_USERS
   WHERE AGE > 29
) 
INSERT INTO CUSTOMERS (
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
)
SELECT 
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
FROM GUESTS a 
INNER JOIN temp b ON a.LAST_NAME = b.LAST_NAME

しかし、Oracle ではこれはエラーになってしまう。
WITH句はSELECTの前、
INSERT INTO と SELECT の間に書かないと Oracle では動かない。

INSERT INTO CUSTOMERS (
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
)
WITH temp AS (
   SELECT 
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
   FROM TEST_USERS
   WHERE AGE > 29
) 
SELECT 
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
FROM GUESTS a 
INNER JOIN temp b ON a.LAST_NAME = b.LAST_NAME

トリッキーな、SELECT * FROM dual

Oracleでは、

SELECT * FROM dual

は、列名＝'DUMMY' 値＝'X'の1行を返す。

PostgreSQL では、、、
dual は存在しないのでFROM句を省略するのだが、

SELECT * 

とすると、
　　SQLエラー [42601]: ERROR: テーブル指定のないSELECT *は無効です
になる
Oracleと同じことをするには、WITH句でdualを用意する

WITH dual AS(
   SELECT 'X' AS DUMMY
)
SELECT * FROM dual

これでOracleと同じことができる。

MySQLでは、

SELECT 1 FROM dual

は、列名＝'1' 値＝1 が返ってくるしFROMを省略して

SELECT 1

でも同じである。

ただ上に書いた * （アスタリスク）はどうなるかというと、、

SELECT *

や、

SELECT * FROM dual

は、
　　SQLエラー [1096] [HY000]: No tables used
となる。
でも、

 SELECT COUNT(*) AS cnt  FROM DUAL

や、

SELECT COUNT(*) AS cnt

は、きちんと COUNT = 1 が返ってくる

DBeaver のドライバの設定

DBeaver をインストールした環境でそうそう巡り合わないのだが、
ドライバ入手先へのネットワーク接続が制限された環境でDBeaver が使用できなくて
困ってしまうことがあります。
そういう場合は、なんとか JDBCドライバファイルを入手してPC上に置いて
それを使用するように設定します。
各DB接続で必要なドライバは、接続設定を開いて、

一般タブの右下にある「ドライバの設定を編集」ボタンで開いた画面の
[ライブラリ] タブを開き認識します。

これがデフォルトで、ドライバをネットワークから入手する設定なので.
ドライバ設定のJARファイル名をメモをしておきます。

メモしたら、これら全てを削除して、
Mavenのリポジトリhttps://mvnrepository.com/　などから
JAR ファイルをダウンロードして適当な場所に置き、
「ファイルを追加」でダウンロードしたJARを指定します。

これで、ドライバ入手先へのネットワーク接続が制限された環境でも使用できます