BTEQ EXPORTING a VALUE to file.

Database
Enthusiast

BTEQ EXPORTING a VALUE to file.

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 ?

7 REPLIES
Enthusiast

Re: BTEQ EXPORTING a VALUE to file.

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

Enthusiast

Re: BTEQ EXPORTING a VALUE to file.

Thanks Jim....It helped

Enthusiast

Re: BTEQ EXPORTING a VALUE to file.

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?

Supporter

Re: BTEQ EXPORTING a VALUE to file.

I am not Jim but yes you can...

export reset is important here.

Enthusiast

Re: BTEQ EXPORTING a VALUE to file.

Thanks ..I tried it worked!

Enthusiast

Re: BTEQ EXPORTING a VALUE to file.

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

New Member

Re: BTEQ EXPORTING a VALUE to file.

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