Using the SQL Editor 

Use

You may create new relational queries or edit the SQL statements of existing relational queries with the SQL Editor.

 

Prerequisites

·        You have at least one relational data service configured as a BI system.

·        You are connected to a portal.

·        You have created and zoomed to the level of an iView in which to place your query.

·         The SQL Editor is only available for relational data services.

·         The SQL Editor is not available for SAP Query data services (services defined based on the SAP Query Connection)

 

Procedures

To create new relational queries with the SQL Editor:

...

       1.      From the BI menu, choose SQL Editor.

       2.      In the SQL Editor, select a system from the System drop-down list.

       3.      In the Object Browser, select metadata and syntax examples with which to construct your query with the Browse, Search, and Syntax Examples tabs:

¡        Use the Browse tab to browse for and select specific metadata to use in your statement. You may browse for and select any type of metadata.

¡        Use the Search tab to search for a specific table:

                                                  i.       Place your cursor in the Table field, and type the name of the table you want to find. This field also functions as a drop-down list, which you can use to select from the history of searched terms.

                                                ii.       Click Search. The SQL Editor searches across all catalogs for tables with the specified name. If the table is found, you may select it or browse and select fields within it in the list below.

¡        Use the Syntax Examples tab to select a template with which to build your statement. The SQL Editor provides several templates organized into different categories. After you transfer the syntax example to the Editor pane, replace the objects in brackets (<< >>) with selected metadata from your data service.

       4.      Drag-and-drop or double-click to transfer the syntax example or the unique name of the selected object to the Editor pane at the current cursor location.

       5.      Edit your statement as desired in the Editor pane, using any of the following methods:

¡        Type directly into the pane.

¡        Transfer metadata objects into the pane using the procedure in step 4.

¡        Use the editor icons:

Editor Icons

Icon	Function
	Cut
	Copy
	Paste
	Undo
	Redo
	Validate
	Run

 

       6.      Validate your statement by clicking the Validate icon.

       7.      Run your statement to preview its result by clicking the Preview icon.

       8.      Maximize or restore the Preview pane at any time by clicking the up or down arrows.

       9.      Press Save to save your statement.

   10.      Press Close to close without saving.

The SQL Editor allows you to save a statement even if it is invalid. We recommend you check the validity of your statement using the Validate icon before saving it.

 

To edit SQL statements of existing relational queries using the SQL Editor:

See Modifying Existing Queries, and follow the previous editor usage instructions.

 

Result

Once you have saved a statement, you return to the Visual Composer workspace, where a data service has been configured with the SQL statement you have created.

See Working With Queries for ways to continue working with your query.