BTEQ EXPORTING a VALUE to file.

[tdradha]

Hello,

I am facing some issues with Bteq export/ display the value in the required format.

e.g:-  I am trying the below one.

.export file = appxsize.txt

SELECT (MAX(currentperm)*(HASHAMP()+1)) AS EffUsedSpaceMB

FROM DBC.TableSizeV

WHERE DATABASENAME ='abc'

AND TABLENAME ='xyz'

GROUP BY databasename,tablename;

.export reset

I am gettingh the output as

4.94037117731482E 007

but I need the out put as  49,403,711.77

What are the .SET commands I missed?

I tried with

.SET LARGEDATAMODE

also

.SET RECORDMODE ON

nothing helped me.  Any ideas ?

[Jim_Chapman]

SELECT (MAX(currentperm)*(HASHAMP()+1))

       (FORMAT'--,---,---,---,---,---,--9.99') AS EffUsedSpaceMB ...

Although you say you need 2 decimal places in the output, note that the result will always be a whole number.  Perhaps you intended the result to be in units of megabytes...

SELECT (MAX(currentperm)*(HASHAMP()+1)) / 1000000

       (FORMAT'--,---,---,---,---,---,--9.99') AS EffUsedSpaceMB ...

[tdradha]

Thanks Jim....It helped

[tdradha]

Jim,  I have one more quick question can we use multiple export statements with in the single BTEQ Script?

e.g:-

bteq<< EOF

.logon.....

.export file =abc.txt

select query1

.export reset

.export file =123.txt

select query 2

.export reset

.quit

EOF

can I do this?

[ulrich]

I am not Jim but yes you can...

export reset is important here.

[tdradha]

Thanks ..I tried it worked!

[S_Banerjee]

Someone please tell me what is the exact function of EOF in bteq<< EOF command I tried with and without <<EOF. Without <<EOF my script goes in interactive mode and with <<EOF its running in batch mode.Please explain.

Thanks,

Banerjee

[abhishekgambhir]

EOF (End of File) will let the script know, that SQLs that bteq needs to execute ends here. Hope it helps!