Skip to end of metadata
Go to start of metadata

Only in DbVisualizer Pro

This feature is only available in the DbVisualizer Pro edition.

The SQL Commander main menu on the (or right-click in the editor) and its Format SQL sub menu contains operations for formatting SQL statements.

  • Format Buffer and Format Current formats the complete editor content or the current SQL (at cursor position) respectively.
  • Copy Formatted and Paste Formatted are powerful tools for copying SQL statements between programs written in languages like Java, C#, PHP, VB, etc. and the SQL Editor. Both operations display a dialog where you can adjust some of the formatting options, most importantly the Target SQL option and the SQL is Between option. Target SQL can be set to a number of common programming language formats. 
  • Unformat Buffer and Unformat Current produces compact statements by removing unnecessary whitespace. 

Example: 

To copy an SQL statement and paste it as Java code for adding it to a Java StringBuffer:

  1. Select the statement. Example:
    SELECT * FROM SAKILA.STAFF)
  2. Choose SQL->Format SQL->Copy Formatted,
  3. Set Target SQL to Java StringBuffer,
  4. Click Format to place the formatted statement on the system clipboard,
  5. Paste it into your Java code. Example: 
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT ");
    sql.append(" * ");
    sql.append("FROM ");
    sql.append(" SAKILA.STAFF");

To copy a statement wrapped in code from a program:

  1. Select the code containing an SQL statement in your program,
  2. Copy it to the system clipboard,
  3. Choose SQL->Format SQL->Paste Formatted,
  4. Check SQL is Between and enter the character enclosing the SQL statement in the code,
  5. Click Format to extract the SQL statement and paste the formatted SQL in the editor.

Settings

All formatting is done according to the settings defined in the Tool Properties dialog, in the SQL Commander/SQL Formatting category under the General tab.

There are many things you can configure; use the  default example or your own SQL to check the effect of the settings. After making some changes, press Apply and format again to see the result. 

Example:
-- Basic SELECT example, with Sub-SELECT and JOIN
SELECT e.LAST_NAME AS "Last Name", e.FIRST_NAME AS "First Name", d.DEPARTMENT_NAME AS "Department", e.SALARY AS "Salary", e.SALARY + e.SALARY * e.COMMISSION_PCT, e.COMMISSION_PCT * 100 || '%', ROUND(e.SALARY / ( SELECT MAX(SALARY) FROM HR.EMPLOYEES), 2) * 100 AS "Percentage of Max" FROM HR.EMPLOYEES e INNER JOIN HR.DEPARTMENTS d ON ( e.DEPARTMENT_ID = d.DEPARTMENT_ID) WHERE d.DEPARTMENT_ID IN (10, 20, 90, 210) AND e.SALARY > 3000;
-- CASE example
SELECT FIRST_NAME, LAST_NAME, SALARY, CASE WHEN SALARY > 10000 THEN 'High' WHEN SALARY BETWEEN 5000 AND 999 THEN 'Midlevel' ELSE 'Low' END AS "Income Level", CASE DEPARTMENT_ID WHEN 40 THEN 'Administration' WHEN 20 THEN 'Sales Related' ELSE 'Other' END AS "Special Departments" FROM EMPLOYEES;
-- JOIN example, with GROUP BY, HAVING and ORDER BY
SELECT COUNT(d.DEPARTMENT_NAME) AS "Departments per Location", c.COUNTRY_NAME, l.STATE_PROVINCE FROM DEPARTMENTS d INNER JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID INNER JOIN COUNTRIES c USING (COUNTRY_ID) GROUP BY c.COUNTRY_NAME, l.STATE_PROVINCE HAVING COUNT(d.DEPARTMENT_NAME) > 1 ORDER BY 2, 3, 1;
-- UPDATE example
UPDATE EMPLOYEES SET COMMISSION_PCT = 10 WHERE COMMISSION_PCT = 0 AND SALARY < 5000;
-- INSERT example
INSERT INTO EMPLOYEES ( FIRST_NAME, LAST_NAME ) VALUES ( 'Roger', 'Bjarevall' );
-- DELETE example
DELETE FROM EMPLOYEES WHERE HIRE_DATE < to_timestamp('1900-01-10', 'RR-MM-DD');
-- CREATE TABLE example
CREATE TABLE DEPARTMENTS ( DEPARTMENT_ID NUMBER(4) NOT NULL, DEPARTMENT_NAME VARCHAR2(30) NOT NULL, MANAGER_ID NUMBER(6), LOCATION_ID NUMBER(4), CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPARTMENT_ID), CONSTRAINT DEPT_LOC_FK FOREIGN KEY (LOCATION_ID) REFERENCES "LOCATIONS" ("LOCATION_ID"), CONSTRAINT DEPT_MGR_FK FOREIGN KEY (MANAGER_ID) REFERENCES "EMPLOYEES" ("EMPLOYEE_ID"), CONSTRAINT DEPT_NAME_NN CHECK ("DEPARTMENT_NAME" IS NOT NULL) );

Formatted with default settings:
-- Basic SELECT example, with Sub-SELECT and JOIN
SELECT 
    e.LAST_NAME           AS "Last Name", 
    e.FIRST_NAME          AS "First Name", 
    d.DEPARTMENT_NAME     AS "Department", 
    e.SALARY              AS "Salary", 
    e.SALARY + e.SALARY * e.COMMISSION_PCT, 
    e.COMMISSION_PCT * 100 || '%', 
    ROUND(e.SALARY / 
    (   SELECT 
            MAX(SALARY) 
        FROM 
            HR.EMPLOYEES), 2) * 100 AS "Percentage of Max" 
FROM 
    HR.EMPLOYEES e 
INNER JOIN 
    HR.DEPARTMENTS d 
ON 
    ( 
        e.DEPARTMENT_ID = d.DEPARTMENT_ID) 
WHERE 
    d.DEPARTMENT_ID IN (10, 
                        20, 
                        90, 
                        210) 
AND e.SALARY > 3000;
 
-- CASE example
SELECT 
    FIRST_NAME, 
    LAST_NAME, 
    SALARY, 
    CASE 
        WHEN SALARY > 10000 
        THEN 'High' 
        WHEN SALARY BETWEEN 5000 AND 999 
        THEN 'Midlevel' 
        ELSE 'Low' 
    END AS "Income Level", 
    CASE DEPARTMENT_ID 
        WHEN 40 
        THEN 'Administration' 
        WHEN 20 
        THEN 'Sales Related' 
        ELSE 'Other' 
    END AS "Special Departments" 
FROM 
    EMPLOYEES;
 
-- JOIN example, with GROUP BY, HAVING and ORDER BY
SELECT 
    COUNT(d.DEPARTMENT_NAME) AS "Departments per Location", 
    c.COUNTRY_NAME, 
    l.STATE_PROVINCE 
FROM 
    DEPARTMENTS d 
INNER JOIN 
    LOCATIONS l 
ON 
    d.LOCATION_ID = l.LOCATION_ID 
INNER JOIN 
    COUNTRIES c 
USING 
    (COUNTRY_ID) 
GROUP BY 
    c.COUNTRY_NAME, 
    l.STATE_PROVINCE 
HAVING 
    COUNT(d.DEPARTMENT_NAME) > 1 
ORDER BY 
    2, 
    3, 
    1;
 
-- UPDATE example
UPDATE 
    EMPLOYEES 
SET 
    COMMISSION_PCT = 10 
WHERE 
    COMMISSION_PCT = 0 
AND SALARY < 5000;
 
-- INSERT example
INSERT INTO 
    EMPLOYEES 
    ( 
        FIRST_NAME, 
        LAST_NAME 
    ) 
    VALUES 
    ( 
        'Roger', 
        'Bjarevall' 
    );
 
-- DELETE example
DELETE 
FROM 
    EMPLOYEES 
WHERE 
    HIRE_DATE < to_timestamp('1900-01-10', 'RR-MM-DD');
 
-- CREATE TABLE example
CREATE TABLE 
    DEPARTMENTS 
    ( 
        DEPARTMENT_ID   NUMBER(4) NOT NULL, 
        DEPARTMENT_NAME VARCHAR2(30) NOT NULL, 
        MANAGER_ID      NUMBER(6), 
        LOCATION_ID     NUMBER(4), 
        CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPARTMENT_ID), 
        CONSTRAINT DEPT_LOC_FK FOREIGN KEY (LOCATION_ID) REFERENCES "LOCATIONS" ("LOCATION_ID"), 
        CONSTRAINT DEPT_MGR_FK FOREIGN KEY (MANAGER_ID) REFERENCES "EMPLOYEES" ("EMPLOYEE_ID"), 
        CONSTRAINT DEPT_NAME_NN CHECK ("DEPARTMENT_NAME" IS NOT NULL) 
    );
Unformatted to compact form:
/*-- Basic SELECT example, with Sub-SELECT and JOIN*/ SELECT e.LAST_NAME AS "Last Name", e.FIRST_NAME AS "First Name", d.DEPARTMENT_NAME AS "Department", e.SALARY AS "Salary", e.SALARY + e.SALARY * e.COMMISSION_PCT, e.COMMISSION_PCT * 100 || '%', ROUND(e.SALARY / ( SELECT MAX(SALARY) FROM HR.EMPLOYEES), 2) * 100 AS "Percentage of Max" FROM HR.EMPLOYEES e INNER JOIN HR.DEPARTMENTS d ON ( e.DEPARTMENT_ID = d.DEPARTMENT_ID) WHERE d.DEPARTMENT_ID IN (10, 20, 90, 210) AND e.SALARY > 3000;
/*-- CASE example*/ SELECT FIRST_NAME, LAST_NAME, SALARY, CASE WHEN SALARY > 10000 THEN 'High' WHEN SALARY BETWEEN 5000 AND 999 THEN 'Midlevel' ELSE 'Low' END AS "Income Level", CASE DEPARTMENT_ID WHEN 40 THEN 'Administration' WHEN 20 THEN 'Sales Related' ELSE 'Other' END AS "Special Departments" FROM EMPLOYEES;
/*-- JOIN example, with GROUP BY, HAVING and ORDER BY*/ SELECT COUNT(d.DEPARTMENT_NAME) AS "Departments per Location", c.COUNTRY_NAME, l.STATE_PROVINCE FROM DEPARTMENTS d INNER JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID INNER JOIN COUNTRIES c USING (COUNTRY_ID) GROUP BY c.COUNTRY_NAME, l.STATE_PROVINCE HAVING COUNT(d.DEPARTMENT_NAME) > 1 ORDER BY 2, 3, 1;
/*-- UPDATE example*/ UPDATE EMPLOYEES SET COMMISSION_PCT = 10 WHERE COMMISSION_PCT = 0 AND SALARY < 5000;
/*-- INSERT example*/ INSERT INTO EMPLOYEES ( FIRST_NAME, LAST_NAME ) VALUES ( 'Roger', 'Bjarevall' );
/*-- DELETE example*/ DELETE FROM EMPLOYEES WHERE HIRE_DATE < to_timestamp('1900-01-10', 'RR-MM-DD');
/*-- CREATE TABLE example*/ CREATE TABLE DEPARTMENTS ( DEPARTMENT_ID NUMBER(4) NOT NULL, DEPARTMENT_NAME VARCHAR2(30) NOT NULL, MANAGER_ID NUMBER(6), LOCATION_ID NUMBER(4), CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPARTMENT_ID), CONSTRAINT DEPT_LOC_FK FOREIGN KEY (LOCATION_ID) REFERENCES "LOCATIONS" ("LOCATION_ID"), CONSTRAINT DEPT_MGR_FK FOREIGN KEY (MANAGER_ID) REFERENCES "EMPLOYEES" ("EMPLOYEE_ID"), CONSTRAINT DEPT_NAME_NN CHECK ("DEPARTMENT_NAME" IS NOT NULL) );
  • No labels