LEFT OUTER JOIN Formatting in SQL Assistant 3.5.11

LEFT OUTER JOIN Formatting in SQL Assistant 3.5.11

Given code:

	Code:
	SELECT rs.Name ,rsc.Name ,rscd.Name FROM RunSets rs LEFT OUTER JOIN RunSetContents rsc ON rs.ID = rsc.RunSet_ID LEFT OUTER JOIN RunSetContentDetails rscd ON rsd.ID = rscd.RunSetContent_ID

and Code Formatting Option:

	Code:
	SELECT ... AS ... ,... FROM ... AS ... JOIN ... ON ... = ... LEFT OUTER JOIN ... ON ... = ... RIGHT OUTER JOIN ... ON ... = ...

I get the following output from ^F11:

	Code:
	SELECT rs.Name ,rsc.Name ,rscd.Name FROM RunSets rs LEFT OUTER JOIN RunSetContents rsc ON rs.ID = rsc.RunSet_ID LEFT OUTERJOIN RunSetContentDetails rscd ON rsd.ID = rscd.RunSetContent_ID

There is a problem with the second LEFT OUTER JOIN and the code is no longer valid.

I pasted your code into my editor, pressed Ctrl+F11 and I got a different result

	Code:
	SELECT rs.Name, rsc.Name, rscd.Name FROM RunSets rs LEFT OUTER JOIN RunSetContents rsc ON rs.ID = rsc.RunSet_ID LEFT OUTER JOIN RunSetContentDetails rscd ON rsd.ID = rscd.RunSetContent_ID

I'm using all default formatting settings. In my settings there is not customization for LEFT and RIGTH OUTER JOINs as separate clauses and I'm not sure where you got them. Here is the formatting rule as I have in my settings

	Code:
	SELECT ... AS ..., ... = ..., ( ... ) FROM ... AS ..., ... JOIN ... ON ... = ... AND ..., ( ... ) WHERE ... = ... AND ... OR ( ... ) GROUP BY ..., ( ... ) HAVING ..., ( ... ) ORDER BY ..., ( ... )

My guess, a single JOIN clause in the formatting rule is good for all types of joins, including inner, left outer, right outer, cross, and full joins

I want the tables to which I JOIN to appear on a line following the JOIN keyword and I want them indented.

I start with the following:

	Code:
	SELECT t1.ID, t2.ID, t3.ID FROM Table1 t1 JOIN Table2 t2 ON t2.ID = t1.ID INNER JOIN Table3 t3 ON t3.ID = t2.ID LEFT OUTER JOIN Table4 t4 ON t4.ID = t3.ID LEFT JOIN Table5 t5 ON t5.ID = t4.ID RIGHT OUTER JOIN Table6 t6 ON t6.ID = t5.ID RIGHT JOIN Table7 t7 ON t7.ID = t6.ID

I use a copy of the default format template for SELECT with only one modification, I have moved the ellipses (...) from after the keyword JOIN to the next line and indented it with spaces:

	Code:
	SELECT ... AS ..., ... = ..., ( ... ) FROM ... AS ..., ... JOIN ... ON ... = ... AND ..., ( ... ) WHERE ... = ... AND ... OR ( ... ) GROUP BY ..., ( ... ) HAVING ..., ( ... ) ORDER BY ..., ( ... )

I format and get the following:

	Code:
	SELECT t1.ID, t2.ID, t3.ID FROM Table1 t1 JOIN Table2 t2 ON t2.ID = t1.ID INNER JOIN Table3 t3 ON t3.ID = t2.ID LEFT OUTER JOIN Table4 t4 ON t4.ID = t3.ID LEFT JOIN Table5 t5 ON t5.ID = t4.ID RIGHT OUTER JOIN Table6 t6 ON t6.ID = t5.ID RIGHT JOIN Table7 t7 ON t7.ID = t6.ID

I wanted (in this simple example):

	Code:
	SELECT t1.ID, t2.ID, t3.ID FROM Table1 t1 JOIN Table2 t2 ON t2.ID = t1.ID INNER JOIN Table3 t3 ON t3.ID = t2.ID LEFT OUTER JOIN Table4 t4 ON t4.ID = t3.ID LEFT JOIN Table5 t5 ON t5.ID = t4.ID RIGHT OUTER JOIN Table6 t6 ON t6.ID = t5.ID RIGHT JOIN Table7 t7 ON t7.ID = t6.ID

Note that only the table after the simple JOIN keyword appears on a separate line. This is why I was adding additional lines into the format template such as LEFT OUTER JOIN. I understand that this may not be a v3.5 issue and may simply be a limitation with SQL Assistant. Please advise.

Note also, this is only a simple example. I expected the ability to format my code quite explicitly to my preferences.

I don't have an answer for that yet. I just sent message to developers asking to describe how it is supposed to work and if that behavior can be changed using a different kind of pattern.

Here is the reply I got from developers - "Formatter incorrectly removes end of line characters after xxx JOIN"

This is a bug. Issue #1164 in the support tracking system.

So it appears that in the current build you cannot get to work as you want. Please wait till code formatter behavior is fixed.