Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

Database Keywords

Updated on 2023-03-17 GMT+08:00

DSC supports GaussDB(DWS) keywords, such as NAME, LIMIT, OWNER, KEY, and CAST. These keywords must be enclosed in double quotation marks.

Gauss Keywords (NAME, VERSION, LABEL, POSITION)

The keywords NAME, VERSION, LABEL, and POSITION are changed to ASKeyword.

Input – NAME, VERSION, LABEL, POSITION

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT id, NAME,label,description
        FROM (SELECT a.id             id,
                     b.NAME           NAME,
                     b.description    description,
                     b.default_label  label,
                     ROWNUM           ROW_ID
                FROM CTP_ITEM A
                LEFT OUTER JOIN CTP_ITEM_NLS B ON A.ID = B.ID
                                              AND B.LOCALE = i_language
               ORDER BY a.id ASC)
       WHERE ROW_ID >= to_number(begNum)
         AND ROW_ID < to_number(begNum) + to_number(fetchNum);
 
SELECT DISTINCT REPLACE(VERSION,' ','') ID, VERSION TEXT
        FROM (SELECT T1.SOFTASSETS_NAME, T2.VERSION
                FROM SPMS_SOFT_ASSETS T1, SPMS_SYSSOFT_ASSETS T2
               WHERE T1.SOFTASSETS_ID = T2.SOFTASSETS_ID)
          WHERE SOFTASSETS_NAME = I_SOFT_NAME;
 
SELECT COUNTRY, AMOUNT
          FROM (SELECT '' COUNTRY || '' AMOUNT, '1' POSITION
                  FROM DUAL )
         ORDER BY POSITION;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SELECT id,NAME,label,description FROM (
 SELECT a.id id,b.NAME AS NAME,
 b.description description
 ,b.default_label AS label,
 ROW_NUMBER( ) OVER( ) ROW_ID
 FROM CTP_ITEM A LEFT OUTER JOIN 
 CTP_ITEM_NLS B
 ON A.ID = B.ID AND 
 B.LOCALE = i_language
 ORDER BY a.id ASC) WHERE
 ROW_ID >= to_number( begNum )
 AND 
 ROW_ID < to_number( begNum ) + to_number( fetchNum )
;
 
SELECT
  DISTINCT REPLACE( VERSION ,' ' ,'' ) ID
     ,VERSION AS TEXT
     FROM
        (
         SELECT
         T1.SOFTASSETS_NAME
         ,T2.VERSION
         FROM
        SPMS_SOFT_ASSETS T1
       ,SPMS_SYSSOFT_ASSETS T2
        WHERE
        T1.SOFTASSETS_ID = T2.SOFTASSETS_ID
       )
      WHERE SOFTASSETS_NAME = I_SOFT_NAME ;
   
 
   
SELECT COUNTRY ,AMOUNT
FROM ( SELECT '' COUNTRY || '' AMOUNT
        ,'1' AS POSITION
          FROM
         DUAL
       )
     ORDER BY
     POSITION
;

TEXT & YEAR

Input – TEXT, YEAR

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SELECT 
  NAME, 
  VALUE, 
  DESCRIPTION TEXT, 
  JOINED YEAR, 
  LIMIT 
FROM 
  EMPLOYEE;
  
SELECT 
  NAME, 
  TEXT, 
  YEAR, 
  VALUE, 
  DESCRIPTION, 
  LIMIT 
FROM 
  EMPLOYEE_DETAILS;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SELECT 
  "NAME", 
  VALUE, 
  DESCRIPTION AS TEXT, 
  JOINED AS YEAR, 
  "LIMIT" 
FROM 
  EMPLOYEE;
 
SELECT 
  "NAME", 
  "TEXT", 
  "YEAR", 
  VALUE, 
  DESCRIPTION, 
  "LIMIT" 
FROM 
  EMPLOYEE_DETAILS;

NAME and LIMIT

Input: GaussDB(DWS) keywords NAME and LIMIT

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
CREATE TABLE NAME
      ( NAME VARCHAR2(50) NOT NULL
      , VALUE VARCHAR2(255)
      , DESCRIPTION VARCHAR2(4000)
      , LIMIT NUMBER(9)
      )
  /*TABLESPACE users*/
  pctfree 10   initrans 1   maxtrans 
  255  
     storage ( initial 256K next 256K 
     minextents 1 maxextents 
     unlimited ); 
   
 SELECT NAME, VALUE, DESCRIPTION, LIMIT
   FROM NAME;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TABLE "NAME"
         ( "NAME" VARCHAR2 (50) NOT NULL
         , VALUE VARCHAR2 (255)
         , DESCRIPTION VARCHAR2 (4000)
         , "LIMIT" NUMBER (9)
         ) 
      /*TABLESPACE users*/ 
      pctfree 10 initrans 1 maxtrans 255 
      storage ( initial 256 K NEXT 256 K minextents 1 
      maxextents unlimited );

 SELECT "NAME", VALUE, DESCRIPTION, "LIMIT"
   FROM "NAME";

OWNER

Bulk Operations

Input: Use SELECT to query the GaussDB(DWS) keyword OWNER

1
2
3
4
SELECT
          owner
     FROM
          Test_Col;

Output

1
2
3
4
SELECT
          "OWNER"
     FROM
          Test_Col;

Input: Use DELETE to query the GaussDB(DWS) keyword OWNER

1
2
3
DELETE FROM emp14
     WHERE
          ename = 'Owner';

Input

1
2
3
DELETE FROM emp14
     WHERE
          ename = 'Owner'

KEY

Blogic Operations

Input: GaussDB(DWS) keyword KEY

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE
     OR REPLACE FUNCTION myfct RETURN VARCHAR2 parallel_enable IS res VARCHAR2 ( 200 ) ;
     BEGIN
          res := 100 ;
          INSERT INTO emp18 RW ( RW.empno ,RW.ename ) SELECT
               res ,RWN.ename KEY
          FROM
               emp16 RWN ;
               COMMIT ;
          RETURN res ;
END ;
/

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE
     OR REPLACE FUNCTION myfct RETURN VARCHAR2 IS res VARCHAR2 ( 200 ) ;
     BEGIN
          res := 100 ;
          INSERT INTO emp18 ( empno ,ename ) SELECT
               res ,RWN.ename "KEY"
          FROM
               emp16 RWN ;
               /* COMMIT; */
          null ;
          RETURN res ;
END ;

Range, Account and Language

When Gauss keywords are used as aliases for any column in the SELECT list without defining the AS keyword, the AS keyword to define the aliases.

Input

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE
     OR REPLACE /*FORCE*/
     VIEW SAD.FND_TERRITORIES_TL_V (
          TERRITORY_CODE
          ,TERRITORY_SHORT_NAME
          ,LANGUAGE
          ,Account
          ,Range
          ,LAST_UPDATED_BY
          ,LAST_UPDATE_DATE
          ,LAST_UPDATE_LOGIN
          ,DESCRIPTION
          ,SOURCE_LANG
          ,ISO_NUMERIC_CODE
     ) AS SELECT
               t.TERRITORY_CODE
               ,t.TERRITORY_SHORT_NAME
               ,t.LANGUAGE
               ,t.Account
               ,t.Range
               ,t.LAST_UPDATED_BY
               ,t.LAST_UPDATE_DATE
               ,t.LAST_UPDATE_LOGIN
               ,t.DESCRIPTION
               ,t.SOURCE_LANG
               ,t.ISO_NUMERIC_CODE
          FROM
               fnd_territories_tl t
     UNION
     ALL SELECT
               'SS' TERRITORY_CODE
               ,'Normal Country' TERRITORY_SHORT_NAME
               ,NULL LANGUAGE
               ,NULL Account
               ,NULL Range
               ,NULL LAST_UPDATED_BY
               ,NULL LAST_UPDATE_DATE
               ,NULL LAST_UPDATE_LOGIN
               ,NULL DESCRIPTION
               ,NULL SOURCE_LANG
               ,NULL ISO_NUMERIC_CODE
          FROM
               DUAL ;

Output

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE
     OR REPLACE /*FORCE*/
     VIEW SAD.FND_TERRITORIES_TL_V (
          TERRITORY_CODE
          ,TERRITORY_SHORT_NAME
          ,LANGUAGE
          ,CREATED_BY
          ,CREATION_DATE
          ,LAST_UPDATED_BY
          ,LAST_UPDATE_DATE
          ,LAST_UPDATE_LOGIN
          ,DESCRIPTION
          ,SOURCE_LANG
          ,ISO_NUMERIC_CODE
     ) AS SELECT
               t.TERRITORY_CODE
               ,t.TERRITORY_SHORT_NAME
               ,t.LANGUAGE
               ,t.CREATED_BY
               ,t.CREATION_DATE
               ,t.LAST_UPDATED_BY
               ,t.LAST_UPDATE_DATE
               ,t.LAST_UPDATE_LOGIN
               ,t.DESCRIPTION
               ,t.SOURCE_LANG
               ,t.ISO_NUMERIC_CODE
          FROM
               fnd_territories_tl t
     UNION
     ALL SELECT
               'SS' TERRITORY_CODE
               ,'Normal Country' TERRITORY_SHORT_NAME
               ,NULL AS LANGUAGE
               ,NULL CREATED_BY
               ,NULL CREATION_DATE
               ,NULL LAST_UPDATED_BY
               ,NULL LAST_UPDATE_DATE
               ,NULL LAST_UPDATE_LOGIN
               ,NULL DESCRIPTION
               ,NULL SOURCE_LANG
               ,NULL ISO_NUMERIC_CODE
          FROM
               DUAL ;

Primary Key and Unique Key

If primary and unique keys are declared on table creation, only the primary key needs to consider for migration.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
create table SD_WO.WO_DU_TRIGGER_REVENUE_T
(
  TRIGGER_REVENUE_ID NUMBER not null,
  PROJECT_NUMBER     VARCHAR2(40),
  DU_ID              NUMBER,
  STANDARD_MS_CODE   VARCHAR2(100),
  TRIGGER_STATUS     NUMBER,
  TRIGGER_MSG        VARCHAR2(4000),
  BATCH_NUMBER       NUMBER,
  PROCESS_STATUS     NUMBER,
  ENABLE_FLAG        CHAR(1) default 'Y',
  CREATED_BY         NUMBER,
  CREATION_DATE      DATE,
  LAST_UPDATE_BY     NUMBER,
  LAST_UPDATE_DATE   DATE
)
;  

alter table SD_WO.WO_DU_TRIGGER_REVENUE_T
  add constraint WO_DU_TRIGGER_REVENUE_PK primary key (TRIGGER_REVENUE_ID);
alter table SD_WO.WO_DU_TRIGGER_REVENUE_T
  add constraint WO_DU_TRIGGER_REVENUE_N1 unique (DU_ID, STANDARD_MS_CODE);

Output

CREATE
     TABLE
          SD_WO.WO_DU_TRIGGER_REVENUE_T (
               TRIGGER_REVENUE_ID NUMBER NOT NULL
               ,PROJECT_NUMBER VARCHAR2 (40)
               ,DU_ID NUMBER
               ,STANDARD_MS_CODE VARCHAR2 (100)
               ,TRIGGER_STATUS NUMBER
               ,TRIGGER_MSG VARCHAR2 (4000)
               ,BATCH_NUMBER NUMBER
               ,PROCESS_STATUS NUMBER
               ,ENABLE_FLAG CHAR( 1 ) DEFAULT 'Y'
               ,CREATED_BY NUMBER
               ,CREATION_DATE DATE
               ,LAST_UPDATE_BY NUMBER
               ,LAST_UPDATE_DATE DATE
               ,CONSTRAINT WO_DU_TRIGGER_REVENUE_PK PRIMARY KEY (TRIGGER_REVENUE_ID)
          ) ;

PROMPT

PROMPT should be converted to \ECHO supported by GAUSS.

Oracle Syntax

Syntax after Migration

prompt
prompt Creating table product
prompt ===============================
prompt
create table product
(
  product_id     VARCHAR2(20),
  product_name   VARCHAR2(50)
);
\echo
\echo Creating table product
\echo ===============================
\echo
CREATE TABLE product
(
  product_id     VARCHAR2(20),
  product_name   VARCHAR2(50)
);

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback