Is there a function available to reformat existing poorly formatted SQL statements so that they are more legible?

Document ID : KB000026859
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

Is there a function which is able to reformat existing poorly formatted SQL statements so that they are more legible?

The SQL language can be a notoriously hard one to read if formatted poorly by a writer or sometimes by a SQL generating routine. To assist a user as in most computer languages SQL has some accepted layouts that have been shown to improve legibility. Writers of SQL or any computer language should be mindful of the format they use but in some cases this pre-written SQL can be reformatted.

 

Answer:

The ISQL product has a subcomponent called the SQL editor. The SQL Editor is also called by CA SQL-EASE and CA RC/Update in order to process SQL prior to execution.  The SQL Editor has a command called "STAND" which is able to reformat SQL that has either been written during the current session or in fact exists already on a dataset.


Steps to take:

  1. Use Main Menu item "I" to enter ISQL(Interactive SQL)

  2. Use menu item "E" Edit SQL to enter the SQL Editor.

  3. At this point there are two options. Continue to use the SQL Editor or enter ISPF Edit.

 

SQL Editor Steps:

  1. In the COMMAND line we need to copy in the existing SQL using the DCOPY command.

  2. Enter the DCOPY 'dataset(member)' in the command line and place an "A" on the first line in the edit area on the first underscore. A__ as you would with a ISPF "C" line command.

  3. When the SQL is displayed on the screen enter the "STAND" command in the command line. This will reformat the SQL to command in the command line. This will reformat the SQL to a standard form and should be more legible.

  4. Then on the command line use the DREPLACE or DCREATE command and specify the target dataset(member) and utilizing C or CC line commands to select the text as would be used for the CREATE or REPLACE ISPF command to store the reformatted text on a dataset.