SQL Workbench formatter

[Akash Nath]

Hi Thomas,

I use the SQL formatter all the time and it was working perfectly. Off late I have noticed that formatting does not seem to work for create table command:

BEFORE FORMATTING:

CREATE OR REPLACE TABLE ECP_DEV.STAGE_DIMS.THL_2020 

(

  PY                                                           VARCHAR(20),

  CCN                                                        VARCHAR(20),

  TIN                                                          VARCHAR(20),

  PECOS_HOSPITAL_NAME                  VARCHAR(255),

  PECOS_LEGAL_BUSINESS_NAME    VARCHAR(255),

  PECOS_STREET_ADDRESS                VARCHAR(255),

  PECOS_PO_BOX                                   VARCHAR(20)

);

AFTER FORMATTING (CTRL + R):

CREATE OR REPLACE TABLE ECP_DEV.STAGE_DIMS.THL_2020 (PY VARCHAR(20),CCN VARCHAR(20),TIN VARCHAR(20),PECOS_HOSPITAL_NAME VARCHAR(255),PECOS_LEGAL_BUSINESS_NAME VARCHAR(255),PECOS_STREET_ADDRESS VARCHAR(255),PECOS_PO_BOX VARCHAR(20));

Has anything changed?

I am using 127 and Java 11.0.6

Thank you

Akash

[Thomas Kellerer]

Hmm, I can not reproduce this.

Maybe there are some hidden special characters in the text that for some reason confuse the parser/formatter.

Regards
Thomas

[Akash Nath]

Its confusing :-(

I tried to type a simple create table statement:

CREATE OR REPLACE TEMP TABLE ECP_PROD.CORE.TEST1

(

  A VARCHAR(1),

  B VARCHAR(1),

  C VARCHAR(1),

  D VARCHAR(1),

  E INT

);

 and my Reformat SQL formats it as:

CREATE OR REPLACE TEMP TABLE ECP_PROD.CORE.TEST1 (A VARCHAR(1),B VARCHAR(1),C VARCHAR(1),D VARCHAR(1),E INT);

I guess its something to do with my settings, could you assist me figure it out? Thank you

Akash

workbench.editor.autocompletion.closewithsearch=true

workbench.editor.autocompletion.current.schema=false

workbench.editor.autocompletion.paste.case=upper

workbench.editor.autocompletion.paste.sort=name

workbench.editor.autojumpnext=false

workbench.editor.autosave=never

workbench.editor.bracket.hilite.both=false

workbench.editor.bracket.hilite.enable=true

workbench.editor.bracket.hilite.left=true

workbench.editor.bracket.hilite.rectangle=true

workbench.editor.color.comment1=255,102,102

workbench.editor.color.comment2=255,102,102

workbench.editor.color.cursor=255,255,255

workbench.editor.color.datatype=255,51,204

workbench.editor.color.error=255,0,0

workbench.editor.color.foreground=255,255,255

workbench.editor.color.gutter=51,51,51

workbench.editor.color.keyword1=0,153,153

workbench.editor.color.keyword2=51,51,255

workbench.editor.color.keyword3=0,153,0

workbench.editor.color.linenumber=255,255,255

workbench.editor.color.literal1=204,204,0

workbench.editor.color.literal2=255,153,255

workbench.editor.color.operator=255,153,0

workbench.editor.currentstmt.color=0,255,0

workbench.editor.electricscroll=0

workbench.editor.expand.macro.key=32,0

workbench.editor.format.list.maxelements.nonquoted=10

workbench.editor.format.list.maxelements.quoted=2

workbench.editor.highlightcurrent=true

workbench.editor.lineending.external=default

workbench.editor.lineending.internal=lf

workbench.editor.nowordsep=_$

workbench.editor.occurance.highlight.casesensitive=true

workbench.editor.occurance.highlight.color=255,255,0

workbench.editor.occurance.highlight.enable=true

workbench.editor.occurance.highlight.minlength=2

workbench.editor.occurance.highlight.nowhitespace=true

workbench.editor.plain.wordwrap=true

workbench.editor.rightclickmovescursor=false

workbench.editor.showlinenumber=true

workbench.editor.sql.emptyline.delimiter=false

workbench.editor.syntax.style.comment1=1

workbench.editor.syntax.style.comment2=1

workbench.editor.syntax.style.datatype=1

workbench.editor.syntax.style.keyword1=1

workbench.editor.syntax.style.keyword2=1

workbench.editor.syntax.style.keyword3=1

workbench.editor.syntax.style.literal1=0

workbench.editor.syntax.style.literal2=0

workbench.editor.syntax.style.operator=1

workbench.editor.tabwidth=3

workbench.editor.usetab=false

workbench.sql.create.view.columnlist=true

workbench.sql.formatter.comma.afterLineBreak=false

workbench.sql.formatter.comma.spaceAfterLineBreakComma=false

workbench.sql.formatter.comma.spaceafter=true

workbench.sql.formatter.datatype.case=upper

workbench.sql.formatter.functions.case=upper

workbench.sql.formatter.identifier.case=upper

workbench.sql.formatter.insert.columnsperline=1

workbench.sql.formatter.insert.values.columnname=false

workbench.sql.formatter.join.condition.wrapstyle=onlyMultiple

workbench.sql.formatter.keywords.case=upper

workbench.sql.formatter.select.columnsperline=1

workbench.sql.formatter.subselect.maxlength=60

workbench.sql.formatter.update.columnsperline=1

workbench.formatter.db2.enabled=false

workbench.formatter.db2.supports.scripts=false

workbench.formatter.default.enabled=false

workbench.formatter.default.supports.scripts=false

workbench.formatter.firebird.enabled=false

workbench.formatter.firebird.supports.scripts=false

workbench.formatter.h2.enabled=false

workbench.formatter.h2.supports.scripts=false

workbench.formatter.hsql_database_engine.enabled=false

workbench.formatter.hsql_database_engine.supports.scripts=false

workbench.formatter.informix_dynamic_server.enabled=false

workbench.formatter.informix_dynamic_server.supports.scripts=false

workbench.formatter.microsoft_sql_server.enabled=false

workbench.formatter.microsoft_sql_server.supports.scripts=false

workbench.formatter.mysql.enabled=false

workbench.formatter.mysql.supports.scripts=false

workbench.formatter.oracle.enabled=false

workbench.formatter.oracle.supports.scripts=false

workbench.formatter.postgresql.enabled=false

workbench.formatter.postgresql.supports.scripts=false

workbench.sql.generate.comment.includeempty=false

workbench.sql.generate.delete.doformat=true

workbench.sql.generate.insert.doformat=true

workbench.sql.generate.insert.ignoreidentity=true

workbench.sql.generate.table.case=upper

workbench.sql.generate.update.doformat=true

workbench.sql.historysize=15

workbench.sql.lastscriptdir=C:\\Users\\aknx\\Documents\\Python Scripts\\aknx\\Redshift DDL

workbench.sql.log.obfuscate=false

workbench.sql.log.statements=false

workbench.sql.replace.criteria.history="CDW_PROD";"ECP_PROD";"_DEV";"ECP_TEST";"ECP_DEV";"VARCHAR(255)";";";"NOT NULL"

workbench.sql.replace.criteria.lastvalue=CDW_PROD

workbench.sql.replace.ignoreCase=false

workbench.sql.replace.replacement.history="CDW_DEV";"ECP_DEV";"_TEST";"ECP_PROD";"ECP_TEST"

workbench.sql.replace.replacement.lastvalue=CDW_DEV

workbench.sql.replace.selectedText=false

workbench.sql.replace.useRegEx=false

workbench.sql.replace.wholeWord=false

workbench.sql.replace.window.1376x774.x=447

workbench.sql.replace.window.1376x774.y=247

workbench.sql.replace.wrapSearch=false

workbench.sql.search.history="with";"cast";"centris";"TOTAL_ATT";"total_atte";"xref";"roster";"NULL";"PROD.ECP_DEV_NAME";"group";"prod_g";"splt";"veeva";"CONTRACT";"contract";"coal";"null";"IDF.ORG.PRTY_ID";"information";"employee";"INTERACTIONCATEGORY";"attendees.meal_consumedy";"not in";"testing";"target"

workbench.sql.search.ignoreCase=true

workbench.sql.search.useRegEx=false

workbench.sql.search.wholeWord=false

workbench.sql.search.wrapSearch=false

workbench.gui.MainWindow.1376x774.height=747

workbench.gui.MainWindow.1376x774.width=1390

workbench.gui.MainWindow.1376x774.x=1368

workbench.gui.MainWindow.1376x774.y=-7

workbench.gui.MainWindow.macropopup.visible=false

workbench.gui.MainWindow.screen=\\Display0

workbench.gui.MainWindow.state=6

workbench.gui.autocompletion.cycle.popup=true

workbench.gui.autocompletion.filtersearch=true

workbench.gui.autocompletion.partialsearch=true

workbench.gui.autocompletion.sortcolumns=true

workbench.gui.autoconnect=true

workbench.gui.bookmarks.colwidths.save=false

workbench.gui.bookmarks.sort.save=false

workbench.gui.busyicon.name=loading-static

workbench.gui.cancel.firstconnect.exit=false

workbench.gui.cancelicon.name=cancelling-static

workbench.gui.closebutton.right=true

workbench.gui.closetab.confirm=false

workbench.gui.closetab.multiple.confirm=true

workbench.gui.columnselector.clipboardcopy.formattext=false

workbench.gui.columnselector.clipboardcopy.includeheader=true

workbench.gui.columnselector.clipboardcopy.selectedonly=true

workbench.gui.completioncache.localstorage=always

workbench.gui.completioncache.localstorage.maxage=30d

workbench.gui.data.append.results=false

workbench.gui.data.column.header.includetable=false

workbench.gui.data.column.header.tablename.columnprefix=false

workbench.gui.data.maxrows=0

workbench.gui.data.resultname.firsttable=true

workbench.gui.data.selection.summary=true

workbench.gui.data.sql.tooltip=full

workbench.gui.dbexplorer.tablelist.sort=false

workbench.gui.dbobjects.ObjectScripterUI.1376x774.height=400

workbench.gui.dbobjects.ObjectScripterUI.1376x774.width=500

workbench.gui.dbobjects.ObjectScripterUI.1376x774.x=438

workbench.gui.dbobjects.ObjectScripterUI.1376x774.y=166

workbench.gui.dbobjects.autoselectdatapanel=false

workbench.gui.dbobjects.autoselectsrcpanel=true

workbench.gui.dbobjects.showfocus=false

workbench.gui.dbobjects.tabletabs=top

workbench.gui.dbtree.position=left

workbench.gui.dbtree.quickfilter=true

workbench.gui.dbtree.use.tab.connection=true

workbench.gui.desktop.scalefonts=true

workbench.gui.display.decimal.group=,

workbench.gui.display.multiline.editor.wrap=false

workbench.gui.display.multiline.renderer.wrap=false

workbench.gui.display.multilinethreshold=250

workbench.gui.display.name_at_end=false

workbench.gui.display.result.sql=false

workbench.gui.display.resulttab.closebutton=false

workbench.gui.display.rowheightresize=false

workbench.gui.display.showfilename=none

workbench.gui.display.showprofilegroup=true

workbench.gui.display.showpworkspace=true

workbench.gui.display.showurl=false

workbench.gui.display.showurl.includeuser=false

workbench.gui.display.sqltab.closebutton=false

workbench.gui.display.tab.closebutton.onlyactive=false

workbench.gui.display.timeformat=HH:mm:ss

workbench.gui.display.titlegroupbracket=[

workbench.gui.display.titlegroupsep=/

workbench.gui.edit.profile.sshconfig.1376x774.height=521

workbench.gui.edit.profile.sshconfig.1376x774.width=393

workbench.gui.edit.profile.sshconfig.last_version=127

workbench.gui.edit.requiredfield.color=255,100,100

workbench.gui.edit.requiredfield.dohighlight=true

workbench.gui.edit.toolbar.dialog.1376x774.height=522

workbench.gui.edit.toolbar.dialog.1376x774.width=972

workbench.gui.edit.warn.discard.changes=false

workbench.gui.editor.defaultdir=C:\\Users\\inital\\Documents\\SQL Workbench

workbench.gui.editor.errorjump=true

workbench.gui.editor.exec.disable=false

workbench.gui.editor.execute.highlighterror=true

workbench.gui.editor.execute.onlyselected=false

workbench.gui.editor.file.reloadtype=none

workbench.gui.editor.followfiledir=false

workbench.gui.editor.wheelscroll.units=4

workbench.gui.fontzoom.mousewheel=true

workbench.gui.form.fieldlines=5

workbench.gui.form.fieldwidth=30

workbench.gui.keep.currentsql.selection=true

workbench.gui.language=en

workbench.gui.log.consolidate=false

workbench.gui.lookandfeelclass=com.bulenkov.darcula.DarculaLaf

workbench.gui.macropopup.enter.run=true

workbench.gui.macropopup.esc.closes=false

workbench.gui.macropopup.show.filter=true

workbench.gui.macropopup.useworkspace=false

workbench.gui.mainwindow.showtoolbar=true

workbench.gui.mainwindow.tabpolicy=0

workbench.gui.maxrows.tooltipwarning=true

workbench.gui.maxrows.warning.show=true

workbench.gui.menu.showicons=true

workbench.gui.optimalrowheight.automatic=false

workbench.gui.optimalrowheight.ignore.emptylines=false

workbench.gui.optimalrowheight.maxlines=10

workbench.gui.optimalwidth.automatic=true

workbench.gui.optimalwidth.includeheader=true

workbench.gui.optimalwidth.maxsize=850

workbench.gui.optimalwidth.minsize=50

workbench.gui.profiles.DriverEditorDialog.1376x774.height=446

workbench.gui.profiles.DriverEditorDialog.1376x774.width=700

workbench.gui.profiles.DriverEditorDialog.last_version=127

workbench.gui.profiles.ProfileSelectionDialog.1376x774.height=628

workbench.gui.profiles.ProfileSelectionDialog.1376x774.width=1053

workbench.gui.profiles.ProfileSelectionDialog.last_version=127

workbench.gui.profiles.divider=282

workbench.gui.profiles.quickfilter=true

workbench.gui.profiles.quickfilter.initialfocus=true

workbench.gui.query.retrieve.comments=false

workbench.gui.quickfilter.useregex=true

workbench.gui.renderer.null.fontstyle=0

workbench.gui.renderer.numberalignment=right

workbench.gui.scale.menuicon=true

workbench.gui.script.alert=false

workbench.gui.script.alert.minduration=0

workbench.gui.script.alert.systemtray=false

workbench.gui.settings.SettingsPanel.1376x774.height=618

workbench.gui.settings.SettingsPanel.1376x774.width=944

workbench.gui.sql.current.line.statement=false

workbench.gui.sql.error.prompt=PromptWithErroressage

workbench.gui.sql.join.completion.prefer.using=false

workbench.gui.sql.join.completion.use.parenthesis=false

workbench.gui.sql.script.showtime=true

workbench.gui.sql.script.statement.feedback=true

workbench.gui.sql.script.statement.showtime=true

workbench.gui.table.alternate.use=false

workbench.gui.table.header.bold=true

workbench.gui.table.header.include.remarks=false

workbench.gui.table.header.include.type=true

workbench.gui.table.rownumber.show=true

workbench.gui.table.searchhighlite.color=255,255,0

workbench.gui.tabs.lru=true

workbench.gui.tabs.showindex=false

workbench.gui.updatecheck.interval=30

workbench.gui.updatecheck.lastcheck=2021-10-11

workbench.gui.values.creator.delimiter=,

workbench.gui.values.creator.emptystring.null=false

workbench.gui.values.creator.regex=false

workbench.gui.values.creator.trim.delimiter=true

[Stephen Zander]

For me (using Build128)

With a connection to AWS EMR Hive and `replace` is flagged as a function by the SQL highlighter, the formatter does not like `or replace` in that command (for me running Build128).  Remove `or replace` and the SQL formats.

With a connection to AWS RedShift and `replace` flagged as a keyword by the SQL highlighter, the formatter handles things just fine.

[Akash Nath]

@Stephen interesting find. Thank you.

I am able to reproduce the problem. In Snowflake you have an option of using CREATE OR REPLACE TABLE:

FOR THE BELOW SCRIPT:

WITH REPLACE AFTER FORMATTING:

CREATE OR REPLACE TABLE PUBLIC.SOME_TABLE_NAME (FIELD1 VARCHAR(1),FIELD2 INT,FIELD3 DATE);

WITHOUT REPLACE AFTER FORMATTING:

CREATE TABLE PUBLIC.SOME_TABLE_NAME
(
  FIELD1   VARCHAR(1),
  FIELD2   INT,
  FIELD3   DATE
);

@Thomas - Would you have any suggestions?

[Akash Nath]

BTW, I am using Build 129