# SQL Editor

The SQL editor allows to send queries directly to the connected CData Virtuality Server.

The upper pane of the editor allows for entering queries; in the lower pane results of the query are displayed.

## Keyword shortcuts and actions

- Find and replace: Press the Find button, or alternatively `Ctrl+H` on the keyboard. If only the find functionality is desired, `Ctrl+F` can be used instead.
- Format text: Press the Format SQL button, or alternatively `Ctrl+Shift+F` on the keyboard.
  Only the selected regions of the document will be formatted, or the entire document if nothing is selected.
- Delete line(s): `Ctrl+Shift+K`
  The lines touched by the selection will be deleted, or the current line if nothing is selected.
- Trigger content assist: `Ctrl+Space`
  Content assist can be activated to display a list of schemas, tables, and procedures. It will be activated automatically after the keywords `FROM`, `JOIN`, `CALL`, `EXEC`, and `EXECUTE`, as well as after a period if the name of a schema is detected.
- Shift lines: `Tab↹` (shift right) or `Shift+Tab↹` (shift left)
  Shift right will only work on lines touched by a text selection, otherwise a regular TAB character will be inserted.
  Shift left will work on lines touched by a text selection, or the current line if nothing is selected.
- Single-line comment / uncomment: `Ctrl+/`
  The lines touched by the selection will be commented / uncommented, or the current line if nothing is selected.
- Multi-line comment / uncomment: `Ctrl+Shift+/`
  Only lines touched by the selection will be commented / uncommented.

- Run current / selected query: `Ctrl+Enter`
- Open new SQL Editor: `Alt+E`
- Select next / previous SQL Editor: `Alt+PageUp↑` / `Alt+PageDown↓`
- Select the whole Result Set: `Ctrl+A;`
- Copy the selected Rows of Result Set: `Ctrl+C;`

## Opening and saving a script

The SQL editor allows to save the current script to a file, as well as to open a saved script (Open script file and Save script file buttons). If an existing file is open in an SQL Editor, it can be saved under a different name with the Save as button. Please note, only the UTF-8 character encoding is supported.

## Issueing queries

To issue a query type it into the SQL editor.

- You can type multiple queries and run them at once by clicking on the green arrow button. Please separate the queries with the statement separator (can be configured in the Preferences). The default statement separator is a double semicolon (`;;`).
- You can also execute a selection. To do so, select the query to execute in the editor and press the green arrow button with cursor . If nothing is selected, the query under the cursor, as delimited by the statement separator, will be executed. Alternatively, the keyboard shortcut `Ctrl+Enter` can be used to the same effect.
- You can run an SQL script as a scheduled SQL Job. To do so, click the Schedule script button.
- To construct a SQL script for a Google Analytics query, click the Google Analytics button. This will open the Google Analytics Query Builder, which will automatically insert a SQL Query in the editor that corresponds to the parameters given by the user.

You can limit the number of results by setting the Limit Rows value in the filter line. You can reuse one of the previously entered queries by selecting it in the quick history popup or in the Advanced History Dialog .

## CSV and XML Query Builders

The SQL Editor includes wizards to facilitate the generation of queries that retrieve data from CSV or XML files, either local or remote (from a web service). To open the corresponding wizard, simply click on the CSV query builder or the XML query builder buttons, located in the toolbar. Please note that these wizards require a data source of type "File" or "Web Service" to be configured in the Server.

## Query plan

The SQL editor is able to build the execution plan for the current query without executing it. To use it, simply press the Show query plan button. The current query is either the selected text or the query under the cursor if nothing selected, as delimited by the statement separator.

## CLOB limitations

Please note that the SQL Editor result pane displays the preview of the value limited by 250 characters. A tooltip can be displayed on a cell that shows first 32 Kilobytes of data. It is activated by a double click or on hover, depending on the configured CData Virtuality Studio preferences.

## Supported SQL query language

CData Virtuality Server supports, among others, select queries, insert queries, stored procedure language and create / alter statements. For a full list of supported SQL features please see the reference manual.

Following are examples for valid SQL editor queries.

Please note, the default statement separator in the SQL Editor is `;;` (double semicolon). It can be changed in the Preferences.
Anonymous stored procedures

    BEGIN
        SELECT * FROM adventureworks.address;
    END

    BEGIN
        call SYSADMIN.createOptimization('adventureworks.contact');
    END

    BEGIN
    DECLARE date startdate='2012-01-01';
    DECLARE date enddate='2012-02-04';
    DECLARE date idate;
    idate=startdate;
    CREATE LOCAL TEMPORARY TABLE #x(xdate date);
      WHILE (idate<=enddate)
      BEGIN
        INSERT INTO #x(xdate) VALUES (idate);
    	idate=timestampadd(SQL_TSI_DAY,1,idate);
      END
    SELECT * from #x;
    END

Stored procedures

    CREATE VIRTUAL PROCEDURE views.dateaxis(IN startdate date,IN enddate date) returns (xdate date) as
    BEGIN
    DECLARE date idate;
    idate=startdate;
    CREATE LOCAL TEMPORARY TABLE #x(xdate date);
      WHILE (idate<=enddate)
      BEGIN
      	INSERT INTO #x(xdate) VALUES (idate);
     	idate=timestampadd(SQL_TSI_DAY,1,idate);
      END
    SELECT * from #x;
    END;;

    ALTER PROCEDURE views.dateaxis() returns (xdate date) as
    BEGIN
      SELECT current_date;
    END;;

Views

    CREATE VIEW views.countryregion AS SELECT * FROM adventureworks.countryregion;;
    CREATE VIEW views.creditcard AS SELECT * FROM adventureworks.creditcard;;

    CREATE VIEW views.countryregion AS SELECT * FROM adventureworks.countryregion;;
    CREATE VIEW views.creditcard AS SELECT * FROM adventureworks.creditcard;;
    ALTER VIEW views.creditcard AS SELECT * from (SELECT * FROM adventureworks.creditcard) a;;

    CREATE VIEW views.countryregion AS SELECT * FROM adventureworks.countryregion;
    DROP VIEW views.countryregion;

Select

    Select * from adventureworks.address;;

Create table

    create table dwh.testtable(a integer);;
    drop table dwh.testtable;;

Insert into / Select into

    create table dwh.testtable(a integer);;
    insert into dwh.testtable select 1;;
    select 1 into dwh.testtable;;
    select * from dwh.testtable;

Explain

    explain SELECT * FROM adventureworks_part_one.addresstype

## Reference manual pages:

- DML Support
  - Identifiers
  - Expressions
  - Criteria
  - SQL Commands
  - VARIADIC procedure parameters
  - Set Operations
  - Subqueries

- SQL Clauses
  - WITH Clause
  - SELECT Clause
  - FROM Clause
  - ARRAYTABLE
  - OBJECTTABLE
  - WHERE Clause
  - GROUP BY Clause
  - HAVING Clause
  - ORDER BY Clause
  - LIMIT Clause
  - INTO Clause
  - OPTION Clause
  - Default Options

- DDL Support
  - Create Table
  - Temp Tables
  - Create View
  - Alter View
  - Drop View
  - Create Procedure
  - Alter Procedure
  - Drop Procedure
  - Create Trigger
  - Alter Trigger

- XML SELECT Command
  - Overview
  - Query Structure
  - Document Generation

- Data Types
  - Supported Types
  - Type Conversions
  - Special Conversion Cases
  - Escaped Literal Syntax

- Scalar Functions
  - Numeric Functions
  - String Functions
  - Date_Time Functions
  - Type Conversion Functions
  - Choice Functions
  - Decode Functions
  - Lookup Function
  - System Functions
  - XML Functions
  - Security Functions
  - Miscellaneous Functions
  - Nondeterministic Function Handling

- Updatable Views
  - preserved Table

- Procedures
  - Procedure Language
  - Virtual Procedures
  - Update Procedures