All Forums Tools
parxv 2 posts Joined 05/15
19 May 2015
Perm Space Value in BTEQ Export Report file incorrect

Hi All,
I am trying to write a simple script to report tables which have not been used since last 90 days. i am using below code to get the values (some specific tables have been renamed)

.SET WIDTH 254;

.EXPORT REPORT FILE= unused_tables_VD.txt

SELECT CAST(FINAL1.DATABASENAME AS CHAR(40)) (TITLE 'DATABASENAME'),CAST(FINAL1.TABLENAME AS CHAR(50))(TITLE 'TABLENAME'),FINAL1.LAST_ACCESS (TITLE 'LAST_ACCESS'),FINAL1.SPACE_IN__GB (TITLE 'SPACE_IN_GB'),T.CREATORNAME(TITLE 'CREATOR')

FROM

 

(

SELECT OAA.DATABASENAME,OAA.TABLENAME,OAA.FIRST_ACCESS, OAA.LAST_ACCESS,

SUM(TS.CURRENTPERM/1024/1024/1024) AS SPACE_IN__GB

FROM

 
but when report is generated i am getting it is generating something like below, where i am getting all as expected, only the sapce_IN_GB (space occupied by table) showing some unusual value. i am not able to find if it is BTEQ issue or i hvae to use some function to get correct value.

DATABASENAME                              TABLENAME                                           LAST_ACCESS                           SPACE_IN_GB                            CREATOR

----------------------------------------  --------------------------------------------------  -----------  ----------------------  ------------------------------

XXX_XXX_XXX                               123abc                                                   2015/01/18                      2.72951547622681E 002                      user1

XXX_1234_XXX                             abcdefg                                                   2015/01/18                      1.59725086212158E 002                      user2

please see SPACE_IN_GB, the first value is actually 273 GB,is there something missed?

dnoeth 4628 posts Joined 11/04
20 May 2015

Well, CurrentPerm is a FLOAT column and you get FLOAT as result.
The default format for FLOAT is a scientific notation with exponent and mantissa, so 2.72951547622681E 002  equals 2.72951547622681 * 100 which is 272.9515.
You might simply CAST(SPACE_IN_GB AS DEC(8,2)).

Dieter

parxv 2 posts Joined 05/15
20 May 2015

Hi Dieter,
Thank you for your reply, my issue is resolved.: :-)

You must sign in to leave a comment.