HANA常用语法（持续更新）

HANA常用语法梳理

DBA_JohnHuang

30791人浏览 · 2017-09-03 11:51:30

DBA_JohnHuang · 2017-09-03 11:51:30 发布

HANA常用语法梳理

1.SCHEMA
2.TABLE
3.COLUMN
4.INDEXES
5.常用SQL语句

1.SCHEMA

--创建schema:
--语法：CREATE SCHEMA <schema_name> [OWNED BY <user_name>]
/*OWNED BY：指定schema的拥有者，如果省略。当前用户将是这个shema的拥有者*/
create schema my_schema;
create schema my_schema OWNED BY system.
--删除schema:
--语法：DROP SCHEMA <schema_name> [<drop_option>]
drop_option： CASCADE | RESTRICT
/*默认的drop_option为：RESTRICT(限制约束)
RESTRICT：直接删除没有依赖的对象,如果对象有依赖关系，会抛出错误信息。
CASCADE：直接删除所有对象。*/
CREATE SCHEMA my_schema;
CREATE TABLE my_schema.t (a INT);
DROP SCHEMA my_schema CASCADE;
select * from tables  where schema_name='P1526659201'  --查询schema:P1526659201下的所有表

2.TABLE

HANA数据库可以存储ROW TABLE（行表）和COLUMN TABLE（列表），分别适用于不同的场景。

2.1 创建表

行存储表适用于场景：

一次处理一条记录的情况
应用需要访问完整记录或记录的大部分（即一条记录中的所有字段或大多数字段）
不需要压缩率
没有或很少的聚集、分组等复杂操作
表中的记录行数不是很多

列存储表适用场景：

通常只是在一个或少量列上执行计算操作
表在进行搜索时通常基于少量列上的值
表有很多列
表有很多行，并且通常进行的是列式操作（比如：聚集计算和where中字段值查找）
需要很高的压缩率

--行存储表示例:   
--例1
CREATE TABLE accounts
    ( act_no CHAR(18), 
      act_name NVARCHAR(100),
      balance DECIMAL(15,2),
      PRIMARY KEY (act_no)
    ) ;
--例2
CREATE TABLE R 
    (A INT PRIMARY KEY, 
     B NVARCHAR(10)
    ); 
    
--列存储表示例：
--例1
CREATE COLUMN TABLE tpch.nation
    (n_nationkey INT NOT NULL,
     n_name CHAR(25),
     n_regionkey INT,
     n_comment VARCHAR(152),
     PRIMARY KEY(n_nationkey)
    ) ;

--例2(分区，只适用于列表，分区键必须是主键的一部分)
CREATE COLUMN TABLE tpch.lineitem
    (l_orderkey INT NOT NULL,l_partkey INT,
     l_suppkey INT,l_linenumber INT NOT NULL,
     l_quantity DECIMAL(10,2),l_extendedprice DECIMAL(10,2),
     l_discount DECIMAL(10,2),l_tax DECIMAL(10,2),
     l_returnflag CHAR(1),l_linestatus CHAR(1),
     l_shipdate DATE,l_commitdate DATE,l_receiptdate DATE,
     l_shipinstruct CHAR(25),l_shipmode CHAR(10),
     l_comment VARCHAR(44),
     PRIMARY KEY INVERTED VALUE (l_orderkey,l_linenumber)
    )
    PARTITION BY HASH (l_orderkey,l_linenumber) PARTITIONS 4,
    RANGE (l_shipdate) (    
       PARTITION '2011/01/01'<= VALUES <'2011/04/01',
       PARTITION '2011/04/01'<= VALUES <'2011/07/01',
       PARTITION '2011/07/01'<= VALUES <'2011/10/01',
       PARTITION OTHERS) ; 

--HANA查看特定表的分区情况
select * from sys.M_CS_PARTITIONS where table_name =  'TABLE_NAME';

在上面的创建列表的例子中，我们指定的“UNIQUE”或“PRIMARY KEY”约束，HANA会自动创建相应的索引。这些索引分为两种类型(仅适用于列表)

INVERTED VALUE 适合于范围查询或like查询
INVERTED HASH 使用HASH对组合唯一键或组合主键进行编码和压缩。对于等值查询(点查询)，这种索引类型具有更好的性能；并且能够减少组合主键存储使用的内存数量。

如果不指定，缺省是 INVERTED VALUE。上面的INVERTED VALUE和INVERTED HASH 仅在UNIQUE和PRIMARY KEY约束中使用。下面是指定的示例

PRIMARY KEY INVERTED VALUE (l_orderkey,l_linenumber)
PRIMARY KEY INVERTED HASH (l_orderkey,l_linenumber)

参考链接：SAP HANA 学习系列——SQL开发(5) | SAP Blogs

HANA除了直接建表外，也可以通过已存在的表创建新表，示例如下：

/*依据某个已经存在的表创建另外的新表。HANA SQL提供了两类方法*/
--方法一：创建的表与源表数据类型、约束完全相同。例如：
CREATE TABLE T_ACCOUNTS LIKE ACCOUNTS WITH NO DATA      
--方法二：创建的表字段类型和NULL/NOT NULL属性相同
CREATE TABLE T_ACCOUNTS1 AS (SELECT * FROM  ACCOUNTS) WITH DATA

2.2 修改表

2.2.1 修改表名

--01.语法：RENAME TABLE <old_table_name> TO <new_table_name>
--02.描述：RENAME TABLE 语句在同一个Schema下，将表名修改为 new_table_name。
--03.示例：
CREATE TABLE mySchema.A (A INTPRIMARYKEY, B INT);
--显示模式 mySchema 下表名的列表：
SELECT TABLE_NAME FROM TABLES WHERE SCHEMA_NAME = 'MYSCHEMA';
--表 mySchema.A 重命名为 B：
RENAME TABLE mySchema.A TO B;--注：修改后B还是在mySchema里

2.2.2 修改表类型

HANA支持行表转换为列表，或者列表转换为行表。示例如下：

ALTER TABLE accounts COLUMN THREADS 10 BATCH 10000;  --行存储表转为列存储表
ALTER TABLE accounts  ROW THREADS 10;                --列存储表转为行存储表

参数说明：

THREADS：指定表转换时的并行执行线程数。线程数的最佳值是系统可用 CPU 内核数。如果未提供 THREADS，将使用 indexserver 中指定的 CPU 内核数的默认值。
BATCH：指定批量处理的行数。如果未指定 BATCH，将使用默认值 2000000。在每次达到批量处理的行数后将立即提交到列存储表中。BATCH 选项仅在从行转换为列存储时才能使用。

关于THREADS和BATCH两个参数，官方文档《SAP_HANA_SQL_and_System_Views_Reference_en》说明如下：

Specifies how many parallel execution threads should be used for the table conversion. The optimal value for the number of threads is the number of available CPU cores. If THREADS is not provided the default value of the number of CPU cores specified in the indexserver.ini file will be used.

Specifies the number of rows to be inserted in a batch. If BATCH is not specified the default value of 2,000,000 will be used. Inserts into column tables will be immediately committed after every <batch_size> records have been inserted. BATCH option can be used only when a table is converted from ROW to COLUMN
storage.

2.2.3 修改表约束

--删除/增加主键
ALTER TABLE "SCHEMA1"."TABLE1" DROP PRIMARY KEY;
ALTER TABLE "SCHEMA1"."TABLE1" ADD  PRIMARY KEY(TID, TNAME);

2.2.4 修改表分区

--创建非分区表
CREATE COLUMN TABLE "TEST"."TABLE1" (
  "WHENDATE" DAYDATE CS_DAYDATE,
  "COL1" VARCHAR(4),
  "COL2" VARCHAR(13),
  PRIMARY KEY ("WHENDATE","COL1")) UNLOAD PRIORITY 5 AUTO MERGE ;
   
--修改为分区表(使用WHENDATE作为分区) 
alter table TEST.TABLE1 partition by range (month(WHENDATE))(
  PARTITION '190001'<= values < '201801' ,
  PARTITION '201801'<= values < '201802' ,
  PARTITION '201802'<= values < '201803' ,
  PARTITION '201803'<= values < '201804' ,
  PARTITION '201804'<= values < '201805' ,
  PARTITION '201805'<= values < '201806' ,
  PARTITION '201806'<= values < '201807' ,
  PARTITION '201807'<= values < '201808' ,
  PARTITION OTHERS)

最后需要带PARTITION OTHERS，否则会出现如下报错信息：

Could not execute 'alter table ******* PARTITION BY ‘RANGE month(WHENDATE) …’ in 162 ms 210 µs .
SAP DBTech JDBC: [2048]: column store error: fail to alter partition: [2593] Error during split/merge operation;A specified Range partitioning does not have a rest part.,object=********Een

--删除分区
ALTER TABLE TEST.TABLE1 DROP PARTITION(month(WHENDATE)) '201704' <= VALUES < '201705';
--增加分区
ALTER TABLE TEST.TABLE1 ADD PARTITION (month(WHENDATE)) '201808' <= VALUES < '201809';
ALTER TABLE TEST.TABLE1 ADD PARTITION (month(WHENDATE)) '201809' <= VALUES < '201810';

注意：删除分区后重新创建，M_CS_PARTITIONS视图中该分区的PART_ID和PARTITION会在最后面

3.COLUMN

--增加或删除字段
ALTER TABLE "SCHEMA1"."TABLE1" ADD("CREATETIME" DATE NULL) ;
ALTER TABLE "SCHEMA1"."TABLE1" DROP (COL1);
--修改字段类型
ALTER TABLE "SCHEMA1"."TABLE1" ALTER ("TYPE" NVARCHAR (10) NULL); 

--修改字段名称
RENAME COLUMN <table_name>.<old_column_name> TO <new_column_name>;
--查询小写字段名信息
select schema_name,table_name,column_name from table_columns where column_name like_regexpr '[[:lower:]]';

4.INDEXES

--SAP HANA的索引都是保存在内存中。
--创建索引：
--语法：
CREATE [UNIQUE] [BTREE | CPBTREE] INDEX <index_name> 
       ON <table_name> (<column_name_order>, ...) [ASC | DESC]
--创建测试表：
create row table test_index (id INT,name nvarchar(10), remark nvarchar(10));
create index indextest1 on test_index(name);
CREATE CPBTREE INDEX indextest2 ON test_index(id, name DESC);

--创建唯一键索引：
create unique index indextest4 on test_index(id);
create unique index indextest3 on test_index(name,remark);

--删除索引：DROP INDEX <index_name>
drop index indextest2;--删除索引indextest2
--查询索引：
select * from indexes where table_name ='EMP';  --查询员工表中使用的索引
select * from index_columns  where table_name ='EMP'; --查询索引列
select * from m_rs_indexes  where table_name = 'EMP'; --查询索引的统计信息(B-tree and CPB-tree)
select * from fulltext_indexes where table_name = 'EMP'; --查询Fulltext 索引
select * from m_fulltext_queues; --查看fulltext 索引队列的状态

--修改索引名称
RENAME INDEX <old_index_name> TO <new_index_name>;

5.常用SQL语句

5.1 基本查询

--1.时间相关
--查看当前日期和时间
select current_date "date",current_time "time" from dummy; 
--查看当前日期零时零分
select to_timestamp(to_char(CURRENT_TIMESTAMP,'yyyy-mm-dd')) from dummy;

--2.查找雇员的姓名及雇员的年薪
select ename,sal*12 from emp;  
--3.像oracle一样任何含空值的数学表达式是空值：
select ename,sal*12+comm  from emp;
--4.其中||相当于把sal全部转换成字符串
select ename||sal from emp; 
--5.表示字符串的方法
select ename||' ename' from  emp; 
--6.如果字符串中包含有' 用''替换它
select ename||''' ename' from emp; 
--7.取出emp表的前5条记录
select top 5 * from emp; 
--8.取出emp表的全部记录
select all * from emp; 
--9.去除两个字段重复的数据
select distinct deptno,job from emp;
--10.取ASCII码
SELECT ASCII('Ant') "ascii" FROM DUMMY;

5.2 系统层面

--磁盘信息
SELECT
  D.HOST,
  LPAD(D.DEVICE_ID, 9) DEVICE_ID,
  D.USAGE_TYPE,
  D.PATH,
  D.FILESYSTEM_TYPE FILESYSTEM,
  LPAD(TO_DECIMAL(D.TOTAL_SIZE / 1024 / 1024 / 1024, 10, 2), 13) TOTAL_SIZE_GB,
  LPAD(TO_DECIMAL(D.USED_SIZE / 1024 / 1024 / 1024, 10, 2), 12) USED_SIZE_GB,
  LPAD(TO_DECIMAL(MAP(D.TOTAL_SIZE, 0, 0, D.USED_SIZE / D.TOTAL_SIZE * 100), 10, 2), 8) 
  USED_PCT
FROM
( SELECT                       /* MODIFICATION SECTION */
    '%' HOST,
    -1 MIN_USED_PCT
  FROM
    DUMMY
) BI,
  M_DISKS D
WHERE
  D.HOST LIKE BI.HOST AND
  ( BI.MIN_USED_PCT = -1 OR MAP(D.TOTAL_SIZE, 0, 0, D.USED_SIZE / D.TOTAL_SIZE * 100) >= BI.MIN_USED_PCT )
ORDER BY
  D.HOST,
  D.USAGE_TYPE, 
  D.PATH;

--磁盘空间分配和碎片信息
SELECT
  HOST,
  LPAD(PORT, 5) PORT,
  FILE_NAME,
  FILE_TYPE,
  LPAD(TO_DECIMAL(TOTAL_SIZE_GB, 10, 2), 13) TOTAL_SIZE_GB,
  LPAD(TO_DECIMAL(USED_SIZE_GB, 10, 2), 12)  USED_SIZE_GB,
  LPAD(TO_DECIMAL(TOTAL_SIZE_GB - USED_SIZE_GB, 10, 2), 14) UNUSED_SIZE_GB,
  LPAD(TO_DECIMAL(MAP(TOTAL_SIZE_GB, 0, 0, ( 1 - USED_SIZE_GB / TOTAL_SIZE_GB ) * 100), 10, 2), 17) FRAGMENTATION_PCT
FROM
( SELECT
    CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'HOST') != 0 
    THEN F.HOST          ELSE MAP(BI.HOST,      '%', 'ANY', BI.HOST)      END HOST,
    CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'PORT') != 0 
    THEN TO_CHAR(F.PORT) ELSE MAP(BI.PORT,      '%', 'ANY', BI.PORT)      END PORT,
    CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'FILE') != 0 
    THEN F.FILE_NAME     ELSE MAP(BI.FILE_NAME, '%', 'ANY', BI.FILE_NAME) END FILE_NAME,
    CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'TYPE') != 0 
    THEN F.FILE_TYPE     ELSE MAP(BI.FILE_TYPE, '%', 'ANY', BI.FILE_NAME) END FILE_TYPE,
    SUM(F.USED_SIZE) / 1024 / 1024 / 1024 USED_SIZE_GB,
    SUM(F.TOTAL_SIZE) / 1024 / 1024 / 1024 TOTAL_SIZE_GB,
    BI.MIN_UNUSED_SIZE_GB,
    BI.MIN_FRAGMENTATION_PCT
  FROM
  ( SELECT                    /* MODIFICATION SECTION */
      '%' HOST,
      '%' PORT,
      '%' FILE_NAME,
      'DATA' FILE_TYPE,            /* DATA, LOG */
      -1  MIN_UNUSED_SIZE_GB,
      -1  MIN_FRAGMENTATION_PCT,
      'NONE' AGGREGATE_BY          
      /* HOST, PORT, FILE, TYPE AND COMMA-SEPARATED COMBINATIONS, NONE FOR NO AGGREGATION */
    FROM
      DUMMY
  ) BI,
    M_VOLUME_FILES F
  WHERE
    F.HOST LIKE BI.HOST AND
    TO_CHAR(F.PORT) LIKE BI.PORT AND
    F.FILE_NAME LIKE BI.FILE_NAME AND
    F.FILE_TYPE LIKE BI.FILE_TYPE
  GROUP BY
    CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'HOST') != 0 
    THEN F.HOST          ELSE MAP(BI.HOST,      '%', 'ANY', BI.HOST)      END,
    CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'PORT') != 0 
    THEN TO_CHAR(F.PORT) ELSE MAP(BI.PORT,      '%', 'ANY', BI.PORT)      END,
    CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'FILE') != 0 
    THEN F.FILE_NAME     ELSE MAP(BI.FILE_NAME, '%', 'ANY', BI.FILE_NAME) END,
    CASE WHEN BI.AGGREGATE_BY = 'NONE' OR INSTR(BI.AGGREGATE_BY, 'TYPE') != 0 
    THEN F.FILE_TYPE     ELSE MAP(BI.FILE_TYPE, '%', 'ANY', BI.FILE_NAME) END,
    BI.MIN_UNUSED_SIZE_GB,
    BI.MIN_FRAGMENTATION_PCT
)
WHERE
  ( MIN_UNUSED_SIZE_GB = -1 OR TOTAL_SIZE_GB - USED_SIZE_GB >= MIN_UNUSED_SIZE_GB) AND
  ( MIN_FRAGMENTATION_PCT = -1 OR 
  MAP(TOTAL_SIZE_GB, 0, 0, ( 1 - USED_SIZE_GB / TOTAL_SIZE_GB ) * 100) 
  >= MIN_FRAGMENTATION_PCT )
ORDER BY
  HOST,
  PORT,
  FILE_NAME,
  FILE_TYPE;

5.3 数据库对象信息

--对象依赖情况
SELECT
  D.BASE_OBJECT_NAME OBJECT_NAME,
  D.BASE_OBJECT_TYPE OBJECT_TYPE,
  D.DEPENDENT_OBJECT_NAME DEP_OBJECT_NAME,
  D.DEPENDENT_OBJECT_TYPE DEP_OBJECT_TYPE
FROM
( SELECT                                             /* MODIFICATION SECTION */
    'HCASHIER7' BASE_OBJECT_NAME,
    'TABLE' BASE_OBJECT_TYPE,
    '%' DEPENDENT_OBJECT_NAME,
    '%' DEPENDENT_OBJECT_TYPE
  FROM
    DUMMY
) BI,
  OBJECT_DEPENDENCIES D
WHERE
  D.BASE_OBJECT_NAME LIKE BI.BASE_OBJECT_NAME AND
  D.BASE_OBJECT_TYPE LIKE BI.BASE_OBJECT_TYPE AND
  D.DEPENDENT_OBJECT_NAME LIKE BI.DEPENDENT_OBJECT_NAME AND
  D.DEPENDENT_OBJECT_TYPE LIKE BI.DEPENDENT_OBJECT_TYPE
ORDER BY
  D.BASE_OBJECT_NAME,
  D.BASE_OBJECT_TYPE,
  D.DEPENDENT_OBJECT_NAME,
  D.DEPENDENT_OBJECT_TYPE;

  
--表压缩情况
SELECT CURRENT_TIMESTAMP
  --,COLUMN_NAME AS "COLUMN",LOADED
  ,SUM(ROUND(UNCOMPRESSED_SIZE/1024/1024)) AS "UNCOMPRESSED MB"
  ,SUM(ROUND(MEMORY_SIZE_IN_MAIN/1024/1024)) AS "MAIN MB"
  ,SUM(ROUND(MEMORY_SIZE_IN_DELTA/1024/1024)) AS "DELTA MB"
  ,SUM(ROUND(MEMORY_SIZE_IN_TOTAL/1024/1024)) AS "TOTAL USED MB"
  --,ROUND(COMPRESSION_RATIO_IN_PERCENTAGE/100,2) AS "COMPR.RATIO"
FROM M_CS_COLUMNS
WHERE TABLE_NAME='XXX'-- AND LOADED='TRUE'
GROUP BY TABLE_NAME;

--表分区情况
SELECT * 
  FROM M_CS_PARTITIONS 
  WHERE 1=1 
  AND (SCHEMA_NAME,TABLE_NAME) IN 
  (SELECT SCHEMA_NAME,TABLE_NAME FROM M_CS_PARTITIONS 
	GROUP BY SCHEMA_NAME,TABLE_NAME HAVING --COUNT(1)=1
	COUNT(1)=13 AND COUNT(1)!=1--13是按月的,1是没有分区的
  )
  ORDER BY 1,2,3,4,5,6;

--表DELTA MERGE情况
SELECT
       CURRENT_TIMESTAMP,A.SCHEMA_NAME,A.TABLE_NAME
       ,SUM(A.MEMORY_SIZE_IN_TOTAL/1024/1024/1024) MEMORY_SIZE_IN_TOTAL_GB
       ,SUM(A.MEMORY_SIZE_IN_DELTA/1024/1024/1024) MEMORY_SIZE_IN_DELTA_GB
       ,MAX(A.LAST_MERGE_TIME) LAST_MERGE_TIME 
       ,MAX(B.AUTO_MERGE_ON) AUTO_MERGE_ON
       ,SUM(A.RECORD_COUNT) RECORD_COUNT
       ,'MERGE DELTA OF ' || A.SCHEMA_NAME || '.' || A.TABLE_NAME || ';' MERGE_SQL
       ,'ALTER TABLE ' || A.SCHEMA_NAME || '.' || A.TABLE_NAME || ' DISABLE AUTOMERGE;' --ENABLE
  FROM M_CS_TABLES A
 LEFT JOIN TABLES B ON A.SCHEMA_NAME=B.SCHEMA_NAME AND A.TABLE_NAME=B.TABLE_NAME
 WHERE 1=1
   AND A.SCHEMA_NAME = 'XXX'
   --AND A.TABLE_NAME='XXX'
  GROUP BY A.SCHEMA_NAME,A.TABLE_NAME;

SELECT 
	CURRENT_TIMESTAMP,* 
	FROM M_DELTA_MERGE_STATISTICS 
WHERE 1=1 AND SCHEMA_NAME='XXX' AND TABLE_NAME='XXX'
ORDER BY SCHEMA_NAME,TABLE_NAME;

--列加载情况
SELECT SCHEMA_NAME
  ,TABLE_NAME
  ,IS_DELTA_LOADED
  ,IS_DELTA2_ACTIVE
  ,LOADED
  ,LAST_MERGE_TIME 
FROM M_CS_TABLES 
  WHERE 1=1
  --AND IS_DELTA2_ACTIVE='TRUE'
  AND SCHEMA_NAME='XXX' AND TABLE_NAME='XXX';

--列索引情况
SELECT CURRENT_TIMESTAMP
  ,SCHEMA_NAME
  ,TABLE_NAME
  ,INDEX_NAME
  ,CONSTRAINT
  ,STRING_AGG(COLUMN_NAME,',') INDEX_COLUMN_STRING
FROM INDEX_COLUMNS
  WHERE SCHEMA_NAME='XXX'
  GROUP BY SCHEMA_NAME,TABLE_NAME,INDEX_NAME,CONSTRAINT
  ORDER BY SCHEMA_NAME,TABLE_NAME,INDEX_NAME,CONSTRAINT;

5.4 备份情况

--日志备份大小
SELECT   
--A.BACKUP_ID,A.HOST,A.SERVICE_TYPE_NAME,A.BACKUP_SIZE,A.DESTINATION_TYPE_NAME,
--B.ENTRY_TYPE_NAME,B.SYS_START_TIME,B.SYS_END_TIME,B.STATE_NAME
  B.ENTRY_TYPE_NAME
  ,TO_CHAR(B.SYS_START_TIME,'YYYY-MM-DD HH24') START_TIME
  ,ROUND(SUM(A.BACKUP_SIZE)/1024/1024/1024,2) BACKUP_SIZE_GB
FROM M_BACKUP_CATALOG_FILES A 
INNER JOIN M_BACKUP_CATALOG B
ON A.ENTRY_ID=B.ENTRY_ID 
WHERE B.SYS_START_TIME>=DATE'2020-01-01' AND ENTRY_TYPE_NAME='log backup'
GROUP BY B.ENTRY_TYPE_NAME,TO_CHAR(B.SYS_START_TIME,'YYYY-MM-DD HH24')
ORDER BY B.ENTRY_TYPE_NAME,TO_CHAR(B.SYS_START_TIME,'YYYY-MM-DD HH24');

--备份状态
SELECT ENTRY_TYPE_NAME
  ,BACKUP_ID
  ,SYS_START_TIME
  ,SYS_END_TIME
  ,STATE_NAME
  ,MESSAGE
FROM M_BACKUP_CATALOG 
  WHERE 1=1
  --AND ENTRY_TYPE_NAME = 'complete data backup' 
  --AND STATE_NAME = 'running' 
  AND SYS_START_TIME>TIMESTAMP'2018-11-07 00:00:00'
  AND STATE_NAME !='successful'
  ORDER BY SYS_START_TIME DESC;