![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
anildas
Joined: 14 Nov 2007 Posts: 69 Country: United States |
|
LEFT OUTER JOIN Formatting in SQL Assistant 3.5.11 |
|
Given code:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
SELECT
... AS ...
,...
FROM
... AS ...
JOIN
... ON ... = ...
LEFT OUTER JOIN
... ON ... = ...
RIGHT OUTER JOIN
... ON ... = ...
|
I get the following output from ^F11:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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.
|
|
Mon May 05, 2008 7:02 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
I pasted your code into my editor, pressed Ctrl+F11 and I got a different result
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
SELECT ... AS ...,
... = ...,
(
...
)
FROM ... AS ...,
...
JOIN ...
ON ... = ...
AND ...,
(
...
)
WHERE ... = ...
AND ...
OR (
...
)
GROUP BY
...,
(
...
)
HAVING ...,
(
...
)
ORDER BY
...,
(
...
) |
|
|
Mon May 05, 2008 7:11 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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
|
|
Mon May 05, 2008 7:14 pm |
|
![](templates/GreenTech/images/spacer.gif) |
anildas
Joined: 14 Nov 2007 Posts: 69 Country: United States |
|
|
|
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:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
SELECT ... AS ...,
... = ...,
(
...
)
FROM ... AS ...,
...
JOIN
...
ON ... = ...
AND ...,
(
...
)
WHERE ... = ...
AND ...
OR (
...
)
GROUP BY
...,
(
...
)
HAVING ...,
(
...
)
ORDER BY
...,
(
...
) |
I format and get the following:
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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):
![](templates/GreenTech/images/code_header.gif) |
![](templates/GreenTech/images/spacer.gif) |
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.
|
|
Mon May 05, 2008 9:20 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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.
|
|
Wed May 07, 2008 9:47 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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.
|
|
Wed May 07, 2008 10:45 am |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|