Perm Space Value in BTEQ Export Report file incorrect

Tools
Fan

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?

2 REPLIES
Junior Contributor

Re: Perm Space Value in BTEQ Export Report file incorrect

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)).

Fan

Re: Perm Space Value in BTEQ Export Report file incorrect

Hi Dieter,

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