Formatting SQL
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 SQL sub menu
- 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:
- Select the statement. Example:
SELECT * FROM SAKILA.STAFF) - Choose SQL Commander → Format SQL → Copy Formatted,
- Set Target SQL to Java StringBuffer,
- Click Format to place the formatted statement on the system clipboard,
- 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:
- Select the code containing an SQL statement in your program,
- Copy it to the system clipboard,
- Choose SQL Commander → Format SQL → Paste Formatted,
- Check SQL is Between and enter the character enclosing the SQL statement in the code,
- 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
SELECTe.LAST_NAMEAS"Last Name",e.FIRST_NAMEAS"First Name",d.DEPARTMENT_NAMEAS"Department",e.SALARYAS"Salary",e.SALARY+e.SALARY*e.COMMISSION_PCT,e.COMMISSION_PCT*100||'%',ROUND(e.SALARY/(SELECTMAX(SALARY)FROMHR.EMPLOYEES),2)*100AS"Percentage of Max"FROMHR.EMPLOYEESeINNERJOINHR.DEPARTMENTSdON(e.DEPARTMENT_ID=d.DEPARTMENT_ID)WHEREd.DEPARTMENT_IDIN(10,20,90,210)ANDe.SALARY>3000;
-- CASE example
SELECTFIRST_NAME,LAST_NAME,SALARY,CASEWHENSALARY>10000THEN'High'WHENSALARYBETWEEN5000AND999THEN'Midlevel'ELSE'Low'ENDAS"Income Level",CASEDEPARTMENT_IDWHEN40THEN'Administration'WHEN20THEN'Sales Related'ELSE'Other'ENDAS"Special Departments"FROMEMPLOYEES;
-- JOIN example, with GROUP BY, HAVING and ORDER BY
SELECTCOUNT(d.DEPARTMENT_NAME)AS"Departments per Location",c.COUNTRY_NAME,l.STATE_PROVINCEFROMDEPARTMENTSdINNERJOINLOCATIONSlONd.LOCATION_ID=l.LOCATION_IDINNERJOINCOUNTRIEScUSING(COUNTRY_ID)GROUPBYc.COUNTRY_NAME,l.STATE_PROVINCEHAVINGCOUNT(d.DEPARTMENT_NAME)>1ORDERBY2,3,1;
-- UPDATE example
UPDATEEMPLOYEESSETCOMMISSION_PCT=10WHERECOMMISSION_PCT=0ANDSALARY<5000;
-- INSERT example
INSERTINTOEMPLOYEES(FIRST_NAME,LAST_NAME)VALUES('Roger','Bjarevall');
-- DELETE example
DELETEFROMEMPLOYEESWHEREHIRE_DATE<to_timestamp('1900-01-10','RR-MM-DD');
-- CREATE TABLE example
CREATETABLEDEPARTMENTS(DEPARTMENT_IDNUMBER(4)NOTNULL,DEPARTMENT_NAMEVARCHAR2(30)NOTNULL,MANAGER_IDNUMBER(6),LOCATION_IDNUMBER(4),CONSTRAINTDEPT_ID_PKPRIMARYKEY(DEPARTMENT_ID),CONSTRAINTDEPT_LOC_FKFOREIGNKEY(LOCATION_ID)REFERENCES"LOCATIONS"("LOCATION_ID"),CONSTRAINTDEPT_MGR_FKFOREIGNKEY(MANAGER_ID)REFERENCES"EMPLOYEES"("EMPLOYEE_ID"),CONSTRAINTDEPT_NAME_NNCHECK("DEPARTMENT_NAME"ISNOTNULL));
Formatted with default settings:
-- Basic SELECT example, with Sub-SELECT and JOIN
SELECT
e.LAST_NAMEAS"Last Name",
e.FIRST_NAMEAS"First Name",
d.DEPARTMENT_NAMEAS"Department",
e.SALARYAS"Salary",
e.SALARY+e.SALARY*e.COMMISSION_PCT,
e.COMMISSION_PCT*100||'%',
ROUND(e.SALARY/
(SELECT
MAX(SALARY)
FROM
HR.EMPLOYEES),2)*100AS"Percentage of Max"
FROM
HR.EMPLOYEESe
INNERJOIN
HR.DEPARTMENTSd
ON
(
e.DEPARTMENT_ID=d.DEPARTMENT_ID)
WHERE
d.DEPARTMENT_IDIN(10,
20,
90,
210)
ANDe.SALARY>3000;
-- CASE example
SELECT
FIRST_NAME,
LAST_NAME,
SALARY,
CASE
WHENSALARY>10000
THEN'High'
WHENSALARYBETWEEN5000AND999
THEN'Midlevel'
ELSE'Low'
ENDAS"Income Level",
CASEDEPARTMENT_ID
WHEN40
THEN'Administration'
WHEN20
THEN'Sales Related'
ELSE'Other'
ENDAS"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
DEPARTMENTSd
INNERJOIN
LOCATIONSl
ON
d.LOCATION_ID=l.LOCATION_ID
INNERJOIN
COUNTRIESc
USING
(COUNTRY_ID)
GROUPBY
c.COUNTRY_NAME,
l.STATE_PROVINCE
HAVING
COUNT(d.DEPARTMENT_NAME)>1
ORDERBY
2,
3,
1;
-- UPDATE example
UPDATE
EMPLOYEES
SET
COMMISSION_PCT=10
WHERE
COMMISSION_PCT=0
ANDSALARY<5000;
-- INSERT example
INSERTINTO
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
CREATETABLE
DEPARTMENTS
(
DEPARTMENT_IDNUMBER(4)NOTNULL,
DEPARTMENT_NAMEVARCHAR2(30)NOTNULL,
MANAGER_IDNUMBER(6),
LOCATION_IDNUMBER(4),
CONSTRAINTDEPT_ID_PKPRIMARYKEY(DEPARTMENT_ID),
CONSTRAINTDEPT_LOC_FKFOREIGNKEY(LOCATION_ID)REFERENCES"LOCATIONS"("LOCATION_ID"),
CONSTRAINTDEPT_MGR_FKFOREIGNKEY(MANAGER_ID)REFERENCES"EMPLOYEES"("EMPLOYEE_ID"),
CONSTRAINTDEPT_NAME_NNCHECK("DEPARTMENT_NAME"ISNOTNULL)
);
Unformatted to compact form:
/*-- Basic SELECT example, with Sub-SELECT and JOIN*/SELECTe.LAST_NAMEAS"Last Name",e.FIRST_NAMEAS"First Name",d.DEPARTMENT_NAMEAS"Department",e.SALARYAS"Salary",e.SALARY+e.SALARY*e.COMMISSION_PCT,e.COMMISSION_PCT*100||'%',ROUND(e.SALARY/(SELECTMAX(SALARY)FROMHR.EMPLOYEES),2)*100AS"Percentage of Max"FROMHR.EMPLOYEESeINNERJOINHR.DEPARTMENTSdON(e.DEPARTMENT_ID=d.DEPARTMENT_ID)WHEREd.DEPARTMENT_IDIN(10,20,90,210)ANDe.SALARY>3000;
/*-- CASE example*/SELECTFIRST_NAME,LAST_NAME,SALARY,CASEWHENSALARY>10000THEN'High'WHENSALARYBETWEEN5000AND999THEN'Midlevel'ELSE'Low'ENDAS"Income Level",CASEDEPARTMENT_IDWHEN40THEN'Administration'WHEN20THEN'Sales Related'ELSE'Other'ENDAS"Special Departments"FROMEMPLOYEES;
/*-- JOIN example, with GROUP BY, HAVING and ORDER BY*/SELECTCOUNT(d.DEPARTMENT_NAME)AS"Departments per Location",c.COUNTRY_NAME,l.STATE_PROVINCEFROMDEPARTMENTSdINNERJOINLOCATIONSlONd.LOCATION_ID=l.LOCATION_IDINNERJOINCOUNTRIEScUSING(COUNTRY_ID)GROUPBYc.COUNTRY_NAME,l.STATE_PROVINCEHAVINGCOUNT(d.DEPARTMENT_NAME)>1ORDERBY2,3,1;
/*-- UPDATE example*/UPDATEEMPLOYEESSETCOMMISSION_PCT=10WHERECOMMISSION_PCT=0ANDSALARY<5000;
/*-- INSERT example*/INSERTINTOEMPLOYEES(FIRST_NAME,LAST_NAME)VALUES('Roger','Bjarevall');
/*-- DELETE example*/DELETEFROMEMPLOYEESWHEREHIRE_DATE<to_timestamp('1900-01-10','RR-MM-DD');
/*-- CREATE TABLE example*/CREATETABLEDEPARTMENTS(DEPARTMENT_IDNUMBER(4)NOTNULL,DEPARTMENT_NAMEVARCHAR2(30)NOTNULL,MANAGER_IDNUMBER(6),LOCATION_IDNUMBER(4),CONSTRAINTDEPT_ID_PKPRIMARYKEY(DEPARTMENT_ID),CONSTRAINTDEPT_LOC_FKFOREIGNKEY(LOCATION_ID)REFERENCES"LOCATIONS"("LOCATION_ID"),CONSTRAINTDEPT_MGR_FKFOREIGNKEY(MANAGER_ID)REFERENCES"EMPLOYEES"("EMPLOYEE_ID"),CONSTRAINTDEPT_NAME_NNCHECK("DEPARTMENT_NAME"ISNOTNULL));