Oracle PL/SQL JSON函數出現 ORA-40474: JSON 資料中包含無效的 UTF-8 位元組序列

 當 Oracle DB Big5 ZHT16MSWIN950 使用 Oracle JSON函數 ORA-40474: JSON 資料中包含無效的 UTF-8 位元組序列，處理方式資料編碼轉換完成後再轉回

with qa as (

select 'TableA' as TT,'DataA' as cc,Convert('中文資料A', 'UTF8' , 'ZHT16MSWIN950') CDA,Convert('中文資料A之A', 'UTF8' , 'ZHT16MSWIN950') CDB from dual
union
select 'TableA' as TT,'DataB' as cc,Convert('中文資料B', 'UTF8' , 'ZHT16MSWIN950') CDA,Convert('中文資料B之B', 'UTF8' , 'ZHT16MSWIN950') CDB from dual)

SELECT replace(Convert(

json_object('Table' value r.TT,

'Data' value (

SELECT json_arrayagg(json_object('CC' value cc, 'CDA' value CDA, 'CDB' value CDB)) FROM qa c WHERE c.TT=r.TT) )

, 'ZHT16MSWIN950', 'UTF8'),'^','') CJSON 

FROM  qa r group by r.TT;

產生結果

{"Table":"TableA","Data":[{"CC":"DataA","CDA":"中文資料A","CDB":"中文資料A之A"},{"CC":"DataB","CDA":"中文資料B","CDB":"中文資料B之B"}]}

T-SQL 字串日期有 "上午"，"下午" 的格式轉換成標轉日期格式

 WITH QQ AS (

    SELECT '2020/11/27 上午 11:58:44' AA

    UNION ALL

    SELECT '2020/11/27 下午 11:58:44' AA)

    SELECT AA 轉換前

    ,CONVERT(DATETIME,CASE WHEN CHARINDEX('上午',AA)>0 THEN REPLACE(AA, ' 上午','')+' AM'   WHEN CHARINDEX('下午',AA)>0 THEN REPLACE(AA, ' 下午','')+' PM'  END,120)   轉換為日期

    FROM QQ 

    轉換前 轉換日期

2020/11/27 上午 11:58:44 2020/11/27 11:58:44.0000  

2020/11/27 下午 11:58:44 2020/11/27 23:58:44.0000  

T-SQL 取第一天與最後一天

   

SELECT 

DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE())-6,0) N年第一天

,DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) 年第一天

,DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0) 季第一天

,DATEADD(M, DATEDIFF(M,0,GETDATE())-10,0) 年第2個月第一天

,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) 月第一天

,DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0) 周第一天

,DATEADD(MILLISECOND, -2, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) 月最後一天2359

,EOMONTH ( GETDATE(),0 ) 月最後一天

Oracle 單筆紀錄欄位與欄位取最大GREATEST ，取最小LEASE

 Oracle 使用 GREATEST(值[欄位]1, 值[欄位]2, ... 值[欄位]_n) 可以取得最大值，反之取最小LEASE

Oracle PL SQL 中使用: 與 & 變數

 Oracle PL SQL 中使用: 與 & 變數

with qq as (select 'A' col1,1 col2 from dual

union all select 'A' col1,2 col2 from dual

union all select 'B' col1,1 col2 from dual

union all select 'B' col1,2 col2 from dual

union all select 'C' col1,1 col2 from dual

union all select 'C' col1,2 col2 from dual)

select * from qq  where col1=:col1

單一變數放 字串A 時 select * from qq  where col1=:col1 結果為

COL1,COL2

A,1

A,2

單一變數放數值 2 時 select * from qq  where col2=:col2 結果為

COL1,COL2

A,2

B,2

C,2

當變數字串要在清單'A','B'時 select * from qq where col1 in (&col1) 

COL1,COL2

A,1

A,2

B,1

B,2

當變數數值要在清單1,2時 select * from qq where col2 in (&col2) 

COL1,COL2

A,1

A,2

B,1

B,2

C,1

C,2

Oracle 將資料列合併成一筆(xml_agg 同)

 方法一 SYS_CONNECT_BY_PATH

WITH QA AS

     (SELECT 'Row1' DROW, 'user1' EMP, 100 NUM  FROM DUAL

      UNION ALL

      SELECT 'Row2' DROW, 'user1' EMP, 90 NUM   FROM DUAL

       UNION ALL

      SELECT 'Row3' DROW, 'user1' EMP, 90 NUM   FROM DUAL

      UNION ALL

      SELECT 'Row4' DROW, 'user1' EMP, 80 NUM   FROM DUAL),

      QB AS (SELECT  EMP,NUM, COUNT(*) OVER (PARTITION BY  EMP  ) CNT,  ROW_NUMBER() OVER (PARTITION BY EMP  ORDER BY NUM)  SEQ    FROM QA )

           SELECT  EMP, SUBSTR(SYS_CONNECT_BY_PATH( NUM, ','), 2) COMBINE FROM QB

WHERE SEQ = CNT START WITH SEQ = 1 CONNECT BY PRIOR SEQ + 1 = SEQ AND PRIOR  EMP=EMP;     

方法二 Listagg

WITH QA AS 

     (SELECT 'Row1' DROW, 'user1' EMP, 100 NUM  FROM DUAL 

      UNION ALL 

      SELECT 'Row2' DROW, 'user1' EMP, 90 NUM   FROM DUAL 

       UNION ALL 

      SELECT 'Row3' DROW, 'user1' EMP, 90 NUM   FROM DUAL 

      UNION ALL 

      SELECT 'Row4' DROW, 'user1' EMP, 80 NUM   FROM DUAL) 

 SELECT  EMP,  LISTAGG(NUM, ',') WITHIN GROUP (ORDER BY NUM) AS  COMBINE FROM QA  group by EMP

Oracle group by 取沒有在 group by 的特定欄位列資料

with  test as (
           select '1' code, 'Get code 1'  name, 1 mpd, 600 amt,'AA' pd  from dual
union all  select '2' code, 'drop code 2'  name, 0 mpd, -600 amt,'AA' pd  from dual
union all  select '3' code, 'Gte code 3'  name, 1 mpd, 100 amt,'BB' pd  from dual 
     )
select   pd,sum(amt) as total,
         min(code) keep (dense_rank last order by mpd) as code,
         min(name) keep (dense_rank last order by mpd) as name
from     test group by pd