Start a new topic

Format buffer messes up IN statements

[This topic is migrated from our old forums. The original author name has been removed]

Try formatting the following SQL: select * from mytable where my_long_column_name in ('1','2','3','4','5') You'll see that it chooses some less-than-useful indentation. Whilst this trivial example is easily corrected by hand, when a long query contains lots of these it can be a pain to correct every time you reformat. I propose two SQL editor functions that, whilst they don't solve this problem, would make it a lot quicker to correct manually: 1. Format Case Only - Allow an option to reformat the case of the SQL without adjusting indentation. This is all I want to do 80% of the time as I maintain my indentation manually but sometimes forget to capitalise keywords. 2. Join Lines - A useful feature of the vim and Sublime Text 2 text editors that simply joins the selected lines into one, replacing any carriage returns with a space. This would allow for quick and easy correction of the dodgy IN clause formatting into something more readable.

[This reply is migrated from our old forums.]

Re: Format buffer messes up IN statements
Hi Vince, Yes, I see your point. Like I said, we have an open ticket for improvements in this area and your comments have been added. Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: Format buffer messes up IN statements
Thanks but I can't do that when I also have a comma-separated column list e.g. UPDATE mytable SET mycolumn1 = 'something or other' , mycolumn2 = 'some more guff' , mycolumn3 = CASE WHEN @myvariable = 0 THEN 'A' WHEN @myvariable = 1 THEN 'B' WHEN @myvariable = 2 THEN 'C' END WHERE mycolumn4 IN ('red','green','blue') In that situation I'd like carriage returns in the column list but not in the IN clause. It's not as contrived an example as it seems - I'm always editing scripts that contain both and very rarely do I want IN clauses on more than one line.
[This reply is migrated from our old forums.]

Re: Format buffer messes up IN statements
Hi Vince, We have an open ticket to look at more options for the SQL Formatter, so I will add your comments there. But you can already avoid getting the IN list broken up into multiple lines by disabling line brakes After Comma in the SQL Commander/SQL Formatting/Line Breaks in Tool Properties. To join lines, you can use the Replace dialog (Ctrl-H by default) and specify "\n" as the Find What and a single space as the Replace With, and then check Use and select Regular Expression. The click Find and All. If you want to reduce multiple spaces and tabs (indentation) with just one space at the same time, specify Find What as "\s+" instead. Best Regards, Hans