Has anyone used the ECHO FILE clause when running SQL code?

I found this intriguing syntax in the documentation, but there are no examples of it being used.

Creating Reports With WebFOCUS Language > Using SQL to Create Reports > Using SQL Translator Commands

Syntax: How to Use SQL Translator Commands:

SQL 
sql statement;
[ECHO|FILE]
[TABLE phrases]
END

quote:

ECHO - Are optional debugging phrases that capture the generated TABLE request. These options are placed after the SQL statement.
FILE [name] - Writes the translated TABLE phrases to the named procedure. If you do not supply a file name, a default name is assigned when the request runs. The file is then deleted.

Running this code gives me an error:

SQL DB2
SELECT * FROM SYS1.TIME_D
FETCH FIRST 100 ROWS ONLY;
ECHO FILE SSQQLL
TABLE ON TABLE HOLD AS H001
END

quote:

(FOC1517) UNRECOGNIZED COMMAND FILE SSQQLL

This message has been edited. Last edited by: Francis Mariani,

According to my Key Sheet Quick Reference Guide, try ECHO ON [FILE filename]

SQL DB2
SELECT * FROM SYS1.TIME_D
FETCH FIRST 100 ROWS ONLY;
ECHO ON FILE SSQQLL
TABLE ON TABLE HOLD AS H001
END

(FOC1517) UNRECOGNIZED COMMAND FILE SSQQLL
0 ERROR AT OR NEAR LINE 2 IN PROCEDURE SQLOUT FOCEXEC *
(FOC001) THE NAME OF THE FILE OR THE WORD 'FILE' IS MISSING

I tried it my self, and ECHO doesn't seems to produce an error, but the rest does.

It also looks like you are supposed to use either ECHO or FILE.

I think Ask IBI for proper docco, and a working example.

Thanks Waz.

I just had a good search of tech support and found this.

SQL INSERT Operation in RMS

And its from EDA 4.3

After much back-and-forth with Tech Support, they tell me it's only available for non-relational tables.

I figured out (not them) that the file is created in the agent's folder and TEMPERASE OFF must be issued to edit the file.

SET TEMPERASE=OFF
-RUN

SQL
SELECT CAR , COUNTRY
FROM CAR;
ECHO ON
FILE MYFILE
TABLE HEADING
"THIS IS A HEADING"
END
-RUN

This is too bad, because I thought it would be an interesting way to translate SQL to WebFOCUS.

I just had a thought, that if you have a second set of masters that has suffix of FIX, the process will work.

I just tested it, it does create the fex, even with a join in place, but the output is quite interesting.

TABLE's HOLD FORMAT FOCUS's, etc.

I wonder how far the FEX generator will go.

This message has been edited. Last edited by: Waz,

That might be an interesting experiment! Thanks for the idea.