BTEQ - Cannot write hexa character in output file

Tools & Utilities
Enthusiast

BTEQ - Cannot write hexa character in output file

Hi everyone,

I have a query that generates another sql as output and when i execute this query inside sql assistant all goes fine. Both select and the other generated. But when i put it inside a bteq, the file with sql generated give me an error... I realized that the hexa characther ('0D'XCF = Carriage return) on the select clause are not written in the output file inside the bteq what causes the error, because the line is not broken for the "if" be interpreted. Can anyone please help me with this error? And Can anyone tell me why the return code was 0 since there are errors in the script?!

Here is my bteq:

.RUN FILE C:\TERA_LOGON.PARAM;

.SET ERROROUT STDOUT

.SET ECHOREQ ON

.SET INDICDATA OFF

.SET QUIET OFF

.SET TIMEMSG QUERY

.SET TITLEDASHES OFF

.SET WIDTH 285

.OS DEL /F "C:\TCOE_CPCBS0010_LIMPEZA.SQL"

.EXPORT REPORT FILE = "C:\TCOE_CPCBS0010_LIMPEZA.SQL"

 SELECT

'CREATE MULTISET TABLE '||trim(DATABASENAME)||'.'||trim(OREPLACE(TABLENAME,'STG','HIST'))||' AS '||trim(DATABASENAME)||'.'||trim(TABLENAME)||' WITH DATA AND STATS; '||'0D'XCF||

'.IF ERRORCODE != 0 THEN QUIT '||'0D'XCF||'0D'XCF||

 'DROP TABLE '||trim(DATABASENAME)||'.'||trim(TABLENAME)||'; '||'0D'XCF||

'.IF ERRORCODE != 0 THEN QUIT '||'0D'XCF||'0D'XCF (TITLE '')

 FROM DBC.TABLES

 WHERE TABLENAME LIKE 'STG\____\_TCOE\_%\_%' ESCAPE '\'

 AND DATABASENAME = 'DCP_UDM' ;

.IF ERRORCODE != 0 THEN QUIT

.EXPORT RESET

.OS TYPE C:\TCOE_CPCBS0010_LIMPEZA.SQL

.RUN FILE "C:\TCOE_CPCBS0010_LIMPEZA.SQL"

.LOGOFF

.QUIT ERRORCODE

Here is the log :

BTEQ 14.00.00.02 Tue Sep 11 19:07:50 2012

+---------+---------+---------+---------+---------+---------+---------+----

.RUN FILE C:\TERA_LOGON.PARAM

;

+---------+---------+---------+---------+---------+---------+---------+----

.LOGON ##/##,

 *** Logon successfully completed.

 *** Teradata Database Release is 13.10.03.07                   

 *** Teradata Database Version is 13.10.03.07                     

 *** Transaction Semantics are BTET.

 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 2 seconds.

+---------+---------+---------+---------+---------+---------+---------+----

+---------+---------+---------+---------+---------+---------+---------+----

.SET ERROROUT STDOUT

 *** Error messages now directed to STDOUT.

+---------+---------+---------+---------+---------+---------+---------+----

.SET ECHOREQ ON

+---------+---------+---------+---------+---------+---------+---------+----

.SET INDICDATA OFF

+---------+---------+---------+---------+---------+---------+---------+----

.SET QUIET OFF

+---------+---------+---------+---------+---------+---------+---------+----

.SET TIMEMSG QUERY

+---------+---------+---------+---------+---------+---------+---------+----

.SET TITLEDASHES OFF

+---------+---------+---------+---------+---------+---------+---------+----

.SET WIDTH 285

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.OS DEL /F "C:\TCOE_CPCBS0010_LIMPEZA.SQL"

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.EXPORT REPORT FILE = "C:\TCOE_CPCBS0010_LIMPEZA.SQL"

 *** To reset export, type .EXPORT RESET

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

 SELECT

'CREATE MULTISET TABLE '||trim(DATABASENAME)||'.'||trim(OREPLACE(TABLENAME,'STG','HIST'))||' AS '||trim(DATABASENAME)||'.'||trim(TABLENAME)||' WITH DATA AND STATS; '||'0D'XCF||

'.IF ERRORCODE != 0 THEN QUIT '||'0D'XCF||'0D'XCF||

 'DROP TABLE '||trim(DATABASENAME)||'.'||trim(TABLENAME)||'; '||'0D'XCF||

'.IF ERRORCODE != 0 THEN QUIT '||'0D'XCF||'0D'XCF (TITLE '')

 FROM DBC.TABLES

 WHERE TABLENAME LIKE 'STG\____\_TCOE\_%\_%' ESCAPE '\'

 AND DATABASENAME = 'DCP_UDM' ;

 *** Query completed. 4 rows found. One column returned. 

 *** Total elapsed time was 1 second.

 *** Total Query time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.IF ERRORCODE != 0 THEN QUIT

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.EXPORT RESET

 *** Output returned to console.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.OS TYPE C:\DiogoReis\Oi\DW\BV_RA_ITX\Cobilling\TCOE_CPCBS0010_LIMPEZA.SQL

CREATE MULTISET TABLE DCP_UDM.HIST_ER2_TCOE_20120905_115211 AS DCP_UDM.STG_ER2_TCOE_20120905_115211 WITH DATA AND STATS;  .IF ERRORCODE != 0 THEN QUIT   DROP TABLE DCP_UDM.STG_ER2_TCOE_20120905_115211;  .IF ERRORCODE != 0 THEN QUIT

CREATE MULTISET TABLE DCP_UDM.HIST_ER2_TCOE_20120906_230400 AS DCP_UDM.STG_ER2_TCOE_20120906_230400 WITH DATA AND STATS;  .IF ERRORCODE != 0 THEN QUIT   DROP TABLE DCP_UDM.STG_ER2_TCOE_20120906_230400;  .IF ERRORCODE != 0 THEN QUIT

CREATE MULTISET TABLE DCP_UDM.HIST_ER2_TCOE_20120906_230421 AS DCP_UDM.STG_ER2_TCOE_20120906_230421 WITH DATA AND STATS;  .IF ERRORCODE != 0 THEN QUIT   DROP TABLE DCP_UDM.STG_ER2_TCOE_20120906_230421;  .IF ERRORCODE != 0 THEN QUIT

CREATE MULTISET TABLE DCP_UDM.HIST_ER2_TCOE_20120906_230504 AS DCP_UDM.STG_ER2_TCOE_20120906_230504 WITH DATA AND STATS;  .IF ERRORCODE != 0 THEN QUIT   DROP TABLE DCP_UDM.STG_ER2_TCOE_20120906_230504;  .IF ERRORCODE != 0 THEN QUIT

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.RUN FILE "C:\TCOE_CPCBS0010_LIMPEZA.SQL"

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

 *** Warning: EOF on INPUT stream.

CREATE MULTISET TABLE DCP_UDM.HIST_ER2_TCOE_20120905_115211 AS DCP_UDM.STG_ER2_TCOE_20120905_115211 WITH DATA AND STATS;  .IF ERRORCODE != 0 THEN QUIT   DROP TABLE DCP_UDM.STG_ER2_TCOE_20120905_115211;  .IF ERRORCODE != 0 THEN QUIT

CREATE MULTISET TABLE DCP_UDM.HIST_ER2_TCOE_20120906_230400 AS DCP_UDM.STG_ER2_TCOE_20120906_230400 WITH DATA AND STATS;  .IF ERRORCODE != 0 THEN QUIT   DROP TABLE DCP_UDM.STG_ER2_TCOE_20120906_230400;  .IF ERRORCODE != 0 THEN QUIT

CREATE MULTISET TABLE DCP_UDM.HIST_ER2_TCOE_20120906_230421 AS DCP_UDM.STG_ER2_TCOE_20120906_230421 WITH DATA AND STATS;  .IF ERRORCODE != 0 THEN QUIT   DROP TABLE DCP_UDM.STG_ER2_TCOE_20120906_230421;  .IF ERRORCODE != 0 THEN QUIT

CREATE MULTISET TABLE DCP_UDM.HIST_ER2_TCOE_20120906_230504 AS DCP_UDM.STG_ER2_TCOE_20120906_230504 WITH DATA AND STATS;  .IF ERRORCODE != 0 THEN QUIT   DROP TABLE DCP_UDM.STG_ER2_TCOE_20120906_230504;  .IF ERRORCODE != 0 THEN QUIT

CREATE MULTISET TABLE DCP_UDM.HIST_ER2_TCOE_20120905_115211 AS DCP_UDM.STG_ER2_TCOE_20120905_115211 WITH DATA AND STATS;  .IF ERRORCODE != 0 THEN QUIT   DROP TABLE DCP_UDM.STG_ER2_TCOE_20120905_115211;  .IF ERRORCODE != 0 THEN QUIT

                                                                                                                            $

 *** Failure 3706 Syntax error: expected something between ';' and '.'.

                Statement# 2, Info =125 

 *** Total elapsed time was 1 second.

 *** Total Query time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.LOGOFF

 *** You are now logged off from the DBC.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

 *** Warning: EOF on INPUT stream.

.QUIT ERRORCODE

 *** Exiting BTEQ...

 *** RC (return code) = 0 


12 REPLIES
Junior Supporter

Re: BTEQ - Cannot write hexa character in output file

Hi:

Try  .IF ERRORCODE <> 0 THEN .QUIT instead of  .IF ERRORCODE != 0 THEN QUIT.

HTH.

Cheers.

Carlos.

Enthusiast

Re: BTEQ - Cannot write hexa character in output file

Try 0D0A instead of 0D.

If I can recall correctly then I think '0D0A'xc is equivalent to '\r\n', and '0D'xc is similar to '\n'.

Cheers!

Enthusiast

Re: BTEQ - Cannot write hexa character in output file

Guys, Thanks for your help, but still doesn't working! Any other idea? 

Senior Supporter

Re: BTEQ - Cannot write hexa character in output file

try

select text (title'')
from
(
SELECT databasename,
       tablename,
       id,
        cast(
            case
                 when id = 1 then 'CREATE MULTISET TABLE '||trim(DATABASENAME) ||'.'||trim(OREPLACE(TABLENAME,'STG','HIST'))||' AS '||trim(DATABASENAME)||'.'||trim(TABLENAME)||' WITH DATA AND STATS; '
                 when id = 2 then '.IF ERRORCODE <> 0 THEN .QUIT '
                 when id = 3 then ' '
                 when id = 4 then  'DROP TABLE '||trim(DATABASENAME)||'.'||trim(TABLENAME)||';' 
                 when id = 5 then '.IF ERRORCODE <> 0 THEN .QUIT '
                 when id = 6 then ' '
            end
         as varchar(500)) as text
 FROM DBC.TABLES t
      cross join
      (select calendar_date - current_date as id from sys_calendar.calendar where id between 1 and 6) c
 WHERE TABLENAME LIKE 'STG\____\_TCOE\_%\_%' ESCAPE '\'
       AND DATABASENAME = 'DCP_UDM'
 ) as t
order by databasename, tablename, id;

just corrected the error code check as Carlos is right

Junior Contributor

Re: BTEQ - Cannot write hexa character in output file

BTEQ REPORT removes any control character like CR/LF, you need to use the FOLDLINE option. And instead of specifying TITLE '' for each column to get rid of the header you can also do an EXPORT DATA followed by a RECORDMODE OFF:

.EXPORT DATA FILE = "C:\TCOE_CPCBS0010_LIMPEZA.SQL"
.RECORDMODE OFF
.FOLDLINE ALL

SELECT
'CREATE MULTISET TABLE '||TRIM(DATABASENAME)
||'.'||TRIM(OREPLACE(TABLENAME,'STG','HIST'))||' AS '
||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||' WITH DATA AND STATS; ',
'.IF ERRORCODE != 0 THEN QUIT ',
'DROP TABLE '||TRIM(DATABASENAME)||'.'||TRIM(TABLENAME)||', '
'.IF ERRORCODE != 0 THEN QUIT '
FROM DBC.TABLES
WHERE TABLENAME LIKE 'STG\____\_TCOE\_%\_%' ESCAPE '\'
AND DATABASENAME = 'DCP_UDM' ;

Dieter

Enthusiast

Re: BTEQ - Cannot write hexa character in output file

Dieter, worked perfectly! Thank you and thank everybody for their help.

Enthusiast

Re: BTEQ - Cannot write hexa character in output file

Hi Dieter,

"RECORDMODE OFF" option  is working for only for EXPORT DATA option.

My Observations:

When i export it in DATA mode, the output is showing like report .

How it displayed like that, any reason for that?

Thanks in Advance!!

Junior Contributor

Re: BTEQ - Cannot write hexa character in output file

RECORDMODE OFF switches from DATA back to REPORT format, but still supresses the column header.

Dieter

Enthusiast

Re: BTEQ - Cannot write hexa character in output file

Thank you!!

Koti