You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

how to make an SQR that will create an output in Excel format.

1 reply [Last post]
Guest's picture
Guest

how to make an SQR that will create an output in Excel format

PG's picture
PG
User offline. Last seen 5 years 42 weeks ago. Offline
Joined: 10/15/2014
Posts: 1
Re: how to make an SQR that will create an output in Excel ...

Below is the sample code if you are creating new sqr with CSV output.
1. Open the file.
2. Write the file
3. Close the file

List of things keep in your mind
1. Path in which the file to placed.
2. Type of delimiter. In the below sample you can see I have used comma.
3. Close the correct file. I am writing file 1 then we have to close the file 1.

Call the procedure accordingly
Some sample procedure of code.

Open a file
Begin-Procedure Open-Files
let $ps_home = getenv('PS_HOME')
let $file1 = $ps_home ||'/ps/psoutput/Abc.csv'
open $file1 as 1 for-writing record=10000:vary status=#open1
If #open1 != 0
stop
else
let $fileOpened = 'Y'
end-if
End-Procedure Open-Files

!Write header if you have
begin-procedure Header
Let $Heading = 'Column1'||'Column2'||'column3'
Write 1 from $Heading
End-procedure Header

Writing content in the file. This procedure which you can keep in Driving sql or place it in which you want the write the final values.
begin-procedure CSV_Content

Let $DELIMITER = ','

Let $Content = $Column1||$DELIMITER||$Column2||$DELIMITER||$column3

Write 1 from $Content

End-procedure CSV_Content

Close the file which you have written.
BEGIN-PROCEDURE CLOSE-File
close 1
END-PROCEDURE CLOSE-File

Regards,
PG