Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ Teradata Syntax Migration/ BTEQ Utility Command
Updated on 2024-12-09 GMT+08:00
View PDF
Share

BTEQ Utility Command

GaussDB(DWS) provides gsql meta-commands that can be used to replace common BTEQ tool commands. The mostly used replacements are as follows:

.QUIT | .EXIT | .RETURN

The meta-command \q [value] can be used to exit the gsql program, and value specifies the exit code. The .QUIT, .EXIT, and .RETURN commands can replace each other with the \q command.

Input

Output

.QUIT 0

\q 0

.EXIT

\q

.RETURN

\q

.LABEL and .GOTO

The Teradata command .LABEL is used to create tags and is usually used together with .GOTO. .GOTO skips all intermediate BTEQ commands and SQL statements, instructs you to reach the specified label position, and performs corresponding restoration processing.

gsql meta-command \goto LABEL... and \label LABEL can be replaced with each other with no constraints.

Input

Output

 
.IF CHECK_PK='' THEN .GOTO NOCHECK
${CHECK_PK};
.LABEL NOCHECK
.QUIT 0
 
\if ${CHECK_PK} == ''
\goto NOCHECK
\endif
${CHECK_PK}
\label NOCHECK
\q 0

.RUN FILE

Executing the SQL request of a specified file can be implemented by the gsql meta-command \i.

Input

Output

 
.RUN FILE=sampfile
 
\i 'sampfile'

.EXPORT FILE

Exporting the SQL statement execution result to a specified file can be implemented by the gsql meta-command \o.

Input

Output

 
.EXPORT REPORT FILE=resultfile
 
\o 'resultfile'

..SET ..END

To set a variable to specified value, both a single-line command or a multi-line command ended with ..END can be used. You can run the SELECT command to query a specified variable name or run the \set command to convert the variable name.

Input

Output

 
.SET SEPARATOR ' | '
 
\set SEPARATOR '|'
 
..SET NAME
'Jack'
..END
 
SELECT 'Jack' AS "NAME";

.IF

.IF is one of the important process control commands used to execute parts of an input script. It can be a single-branch command, which is used in pairs with the THEN clause. It can also be a multi-branch command. The multi-branch IF structure allows multiple nested layers, but each layer must start with the IF command and end with the ENDIF command.

The flow control meta-commands \if... \else ... \endif can be used to replace this BTEQ command.

Input

Output

 
.IF ERRORCODE <> 0 THEN .QUIT 12;
 
\if ${ERROR} != 'false'
\q 12
\endif

..FOR

In the loop control command, when the loop condition is met, the script of the loop body can be executed continuously until .QUIT exits the loop. GaussDB(DWS) provides \for... \loop ... \exit-for ... \end-for structure command implements the loop logic.

Input

Output

 
..IF ACTIVITYCOUNT > 0   THEN
..FOR
SEL SqlStr AS V_SqlStr
FROM ${ ETL_DATA}.TB_DWDATA_UPDATE
WHERE JobName = '${JOB_NAME}'
AND TXDATE = ${ TX_DATE} - 19000000
ORDER BY ExcuteSeq ASC;
..DO
${V_SqlStr}
.IF ERRORCODE<>0 THEN .QUIT 12
..END-FOR
..END-IF
 
\if ${ERROR} != 'false'
\q 12
\endif
\if ${ACTIVITYCOUNT} != 0
\for
SELECT
SqlStr AS V_SqlStr
FROM
${ETL_DATA}.TB_DWDATA_UPDATE
WHERE
JobName = '${JOB_NAME}'
AND TXDATE = to_date( ${TX_DATE} ,'yyyymmdd' )
ORDER BY
ExcuteSeq ASC
\loop
${V_SqlStr} ;
\if ${ERROR} != 'false'
\q 12
\endif
\end-for
\endif