report with different numbering expected * BTEQ *

Tools
N/A

report with different numbering expected * BTEQ *

Good afternoon.

I'm trying to export the query result below by BTEQ. I am using the following script.

 

SELECT DATABASENAME AS "Banco de Dados"

,SUM(CURRENTPERM) AS "Ocupado"

,(MAX(CURRENTPERM) * 72) AS "Ocupado_comskew"

,SUM(MAXPERM) AS "Alocado"

,Alocado - Ocupado AS "Disponível"

,Alocado - Ocupado_comskew AS "Disponível_comskew"

,(CASE WHEN Alocado = 0 THEN 0

ELSE (Ocupado * 100 / Alocado )

END) AS "Porcentagem_ocup"

,(CASE WHEN Alocado = 0 THEN 0

ELSE (Ocupado_comskew * 100 / Alocado )

END) AS "Porcentagem_ocup_comskew"

,Porcentagem_ocup_comskew - Porcentagem_ocup (INTEGER) AS "Diferenca"

FROM DBC.ALLSPACE

WHERE TABLENAME = 'all'

GROUP BY 1

ORDER BY 2 DESC, 1,3,4,5 ASC

;

 

In sql assistant returns the result as expected when I run select for BTEQ, the result returns different from sql assistant

 

 

=================================================

 

Script used in BTEQ

 

 

.RUN FILE RUNFILE_XXX;

 

.SET TITLEDASHES OFF;

.SET FORMAT OFF;

.SET FOLDLINE ON 1;

.SET WIDTH 254;

 

.HEADING ''

 

 

.EXPORT REPORT FILE=D:\teste\scripts\RELAT.txt

SELECT DATABASENAME AS "Banco de Dados"

,SUM(CURRENTPERM) AS "Ocupado"

,(MAX(CURRENTPERM) * 10) AS "Ocupado_comskew"

,SUM(MAXPERM) AS "Alocado"

,Alocado - Ocupado AS "Disponível"

,Alocado - Ocupado_comskew AS "Disponível_comskew"

,(CASE WHEN Alocado = 0 THEN 0

ELSE (Ocupado * 100 / Alocado )

END) AS "Porcentagem_ocup"

,(CASE WHEN Alocado = 0 THEN 0

ELSE (Ocupado_comskew * 100 / Alocado )

END) AS "Porcentagem_ocup_comskew"

,Porcentagem_ocup_comskew - Porcentagem_ocup (INTEGER) AS "Diferenca"

FROM DBC.ALLSPACE

WHERE TABLENAME = 'all'

GROUP BY 1

ORDER BY 2 DESC, 1,3,4,5 ASC

;

 

   

 

.IF ACTIVITYCOUNT = 0 THEN .QUIT 99

 

.EXPORT RESET

 

.LOGOFF

 

.QUIT

 

===================================

result of export for bteq = incorrect

 

Ocupado_comskel = 1.13235904512000E 012

Porcentagem_ocup = 4.18930010627790E 001

Porcentagem_ocup_comskew =  4.20911320595271E 001

===================================

 

result of export correct for sql assistant

 

Ocupado_comskel = 157.321.559.040,00

Porcentagem_ocup = 2.532.934.059.456,00

Porcentagem_ocup_comskew =  41,91

 

Tags (1)
2 REPLIES
Teradata Employee

Re: report with different numbering expected * BTEQ *

Hi. You are getting default formats when you use BTEQ unless you supply format clauses as part of the SQL request. You will need to review SQL reference documentation about how to use FORMAT clauses and may also need to familiarize yourself with BTEQ's DECIMALDIGITS command by taking a look at its reference documentation.

Re: report with different numbering expected * BTEQ *

use cast functions instead of leaving them for teradata. Eg. cast(variable as decimal(22,8))