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

Oracle TABLE 移動 tablespace

變更連線
ALTER SESSION SET CURRENT_SCHEMA =Owner;
移動資料表，資料移動期間整個 table  lock 
ALTER TABLE   OBJECT_NAME   MOVE TABLESPACE  NEW;
重建Index
ALTER INDEX  INDEX_NAME REBUILD TABLESPACE NEWNDX ;

T-SQL Select 設定null 欄位型態

SELECT   CAST(NULL as VARCHAR2(100)) as  varcchar2,CAST(NULL as date) as  datetime FROM dual;

MS-SQL 欄位值 大小寫識別 T-SQL

最近發現 MS-SQL 字串欄位，大寫 和 小寫 會視為相同的值，資料比對誤判
只要在欄位名稱後 加上 Collate SQL_Latin1_General_CP1_CS_AS 大小就視為不同，
相反 SQL_Latin1_General_CP1_CI_AS 就視為相同

如下案例:
select
case when  'ABC' = 'abc'   then 'Yes' else 'NO' End  NO_CS_AS ,
case when  'ABC' Collate SQL_Latin1_General_CP1_CS_AS
         = 'abc' then 'Yes' else 'NO' End   CS_AS,
case when  'ABC' Collate SQL_Latin1_General_CP1_CI_AS
         = 'abc' then 'Yes' else 'NO' End   CI_AS

select
case when 'Abc'  = 'ABC'   then '字串模糊比對' else 'Abc <> ABC' end AS "資料庫預設",
case when 'Abc' Collate SQL_Latin1_General_CP1_CS_AS = 'ABC' Collate SQL_Latin1_General_CP1_CS_AS  then '字串大小寫比對' else 'Abc <> ABC' end AS "指定大小寫比對",
case when 'Abc' Collate SQL_Latin1_General_CP1_CI_AS = 'ABC' Collate SQL_Latin1_General_CP1_CI_AS  then '字串模糊比對' else 'Abc <> ABC' end as "指定模糊比對"

參考 http://technet.microsoft.com/zh-tw/library/ms180175(v=SQL.105).aspx