Using QMF

Converting a prompted query to an SQL query

You can convert a prompted query to an SQL query. Converting a query is useful, for example, if you would like to expand a basic prompted query into a more complex query by using the SQL language.

After you convert a prompted query into an SQL query, you cannot convert it back into a prompted query. If you want to keep a copy of your original prompted query, be sure to save it in the database before you convert it to SQL.

To convert a prompted query to an SQL query:

1. If the prompted query is saved in the database, display it by entering on the QMF command line:

   DISPLAY QUERY queryname
   

2. On the QMF command line, enter:

   CONVERT QUERY
   

   The Convert Confirmation panel displays, if you specified YES for the Confirm option in your QMF user profile.

   Figure 57. The Convert Confirmation panel
   

   +--------------------------------------------------------------------------------+
   | SQL QUERY            USERID.QUERY1                             LINE    1       |
   |   +-------------------------------------------------------------------------+  |
   | S |                          CONVERT Confirmation                           |  |
   |   |                                                                         |  |
   |   | WARNING:                                                                |  |
   |   | Your CONVERT command will convert your current query and place          |  |
   |   | the SQL translation on the SQL query panel.  The original query         |  |
   |   | cannot be redisplayed unless it has been saved or exported.             |  |
   |   |                                                                         |  |
   |   | Do you want to convert this query?                                      |  |
   |   | 1 1. YES - Convert the query to an SQL query.                           |  |
   |   |   2. NO  - Do not convert the query to an SQL query; do not execute the |  |
   |   |      CONVERT command.                                                   |  |
   |   +-------------------------------------------------------------------------+  |
   |   | F1=Help  F12=Cancel                                                     |  |
   |   +-------------------------------------------------------------------------+  |
   +--------------------------------------------------------------------------------+

3. Press Enter to accept choice 1, Yes. The SQL query displays.

   Figure 58. QMF displays the SQL query.
   

   +--------------------------------------------------------------------------------+
   | SQL QUERY                                   LINE     1                         |
   |                                                                                |
   | SELECT A.DEPT, B.LOCATION, AVG (A.SALARY)                                      |
   |  FROM Q.STAFF A, Q.ORG B                                                       |
   |  WHERE ((B.DIVISION = 'EASTERN')                                               |
   |      OR (A.DEPT = 84))                                                         |
   |      AND (A.DEPT = B.DEPTNUMB)                                                 |
   |  GROUP BY A.DEPT, B.LOCATION                                                   |
   | *** END ***                                                                    |
   +--------------------------------------------------------------------------------+

   You can change the query by using SQL statements. You can also save the query in the database if you want.