BTEQ Utility Command

Updated on 2025-01-09 GMT+08:00

View PDF

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

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

Parent topic: Teradata Syntax Migration

Previous topic: Type Casting and Formatting

Next topic: Teradata Formats