SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
LEFT OUTER JOIN Formatting in SQL Assistant 3.5.11

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
LEFT OUTER JOIN Formatting in SQL Assistant 3.5.11
Author Message
anildas



Joined: 14 Nov 2007
Posts: 69
Country: United States

Post LEFT OUTER JOIN Formatting in SQL Assistant 3.5.11 Reply with quote
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.
Mon May 05, 2008 7:02 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6512

Post Reply with quote
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
       ...,
       (
           ...
       )

Mon May 05, 2008 7:11 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6512

Post Reply with quote
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 View user's profile Send private message
anildas



Joined: 14 Nov 2007
Posts: 69
Country: United States

Post Reply with quote
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.
Mon May 05, 2008 9:20 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6512

Post Reply with quote
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 View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6512

Post Reply with quote
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 View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
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


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.