SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
SQL Server Formatting bug

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
SQL Server Formatting bug
Author Message
sangha



Joined: 19 Dec 2006
Posts: 18

Post SQL Server Formatting bug Reply with quote
This happens if the select statement has an IN keyword. Also note that the last character i.e ')' is missing after formatting.
Paste the following in QA and Format the code
Code:
SELECT * FROM   Customers WHERE  CustomerID IN(1,2)

SELECT * FROM   Customers WHERE  CustomerID IN(1,2)

SELECT * FROM   Customers WHERE  CustomerID IN(1,2)

SELECT * FROM   Customers WHERE  CustomerID IN(1,2)


This is the ouput of the formatting
Code:
SELECT *
FROM   Customers
WHERE  CustomerID IN (SELECT CustomerCustomerDemo.CustomerID
                      FROM   CustomerCustomerDemo)
                 
                  SELECT *
                  FROM   Customers
                  WHERE  CustomerID IN (SELECT CustomerCustomerDemo.CustomerID
                                        FROM   CustomerCustomerDemo)
                                   
                                    SELECT *
                                    FROM   Customers
                                    WHERE  CustomerID IN (SELECT CustomerCustomerDemo.CustomerID
                                                          FROM   CustomerCustomerDemo)
                                                     
                                                      SELECT *
                                                      FROM   Customers
                                                      WHERE  CustomerID IN (1, 2)
                                                                       
                                                                        SELECT *
                                                                        FROM   Customers
                                                                        WHERE  CustomerID IN (1, 2

Wed Jun 27, 2007 11:19 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
I actually get this

Code:
SELECT *
FROM   Customers
WHERE  CustomerID IN (1, 2)
                 
                  SELECT *
                  FROM   Customers
                  WHERE  CustomerID IN (1, 2)
                                   
                                    SELECT *
                                    FROM   Customers
                                    WHERE  CustomerID IN (1, 2)
                                                     
                                                      SELECT *
                                                      FROM   Customers
                                                      WHERE  CustomerID IN (1, 2)

Wed Jun 27, 2007 11:27 pm View user's profile Send private message
sangha



Joined: 19 Dec 2006
Posts: 18

Post Reply with quote
Oops I pasted the wrong set of queries.
Try this:-
Code:
SELECT * FROM   Customers WHERE  CustomerID IN (SELECT CustomerCustomerDemo.CustomerID FROM   CustomerCustomerDemo)

SELECT * FROM   Customers WHERE  CustomerID IN (SELECT CustomerCustomerDemo.CustomerID FROM   CustomerCustomerDemo)

SELECT * FROM   Customers WHERE  CustomerID IN (SELECT CustomerCustomerDemo.CustomerID FROM   CustomerCustomerDemo)

SELECT * FROM   Customers WHERE  CustomerID IN(1,2)

SELECT * FROM   Customers WHERE  CustomerID IN(1,2)


You will get the following after formatting:-
Code:
SELECT *
FROM   Customers
WHERE  CustomerID IN (SELECT CustomerCustomerDemo.CustomerID
                      FROM   CustomerCustomerDemo)
                 
                  SELECT *
                  FROM   Customers
                  WHERE  CustomerID IN (SELECT CustomerCustomerDemo.CustomerID
                                        FROM   CustomerCustomerDemo)
                                   
                                    SELECT *
                                    FROM   Customers
                                    WHERE  CustomerID IN (SELECT CustomerCustomerDemo.CustomerID
                                                          FROM   CustomerCustomerDemo)
                                                     
                                                      SELECT *
                                                      FROM   Customers
                                                      WHERE  CustomerID IN (1, 2)
                                                                       
                                                                        SELECT *
                                                                        FROM   Customers
                                                                        WHERE  CustomerID IN (1, 2

Wed Jun 27, 2007 11:32 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
I tried this in v2.5 preview version that is not yet released. The result is the same. Obviously the incremental offset between SELECT statements is wrong.

Also interesting that if I highlight each statement separately and do the formating statement by statement I get the following

Code:
SELECT *
FROM   Customers
WHERE  CustomerID IN (1, 2)

SELECT *
FROM   Customers
WHERE  CustomerID IN (1, 2
FROM   Custome

SELECT *
FROM   Customers
WHERE  CustomerID IN (1, 2)
FROM   Custome

SELECT *
FROM   Customers
WHERE  CustomerID IN (1, 2
FROM   Custome

Wed Jun 27, 2007 11:34 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
I get the same result here for the set of statements you posted in the last message. I will let developers know.
Wed Jun 27, 2007 11:37 pm View user's profile Send private message
sangha



Joined: 19 Dec 2006
Posts: 18

Post Reply with quote
Yes, the last 2 characters are missing for each query after the first query.
Wed Jun 27, 2007 11:38 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
this all seem to happen when a query ends with ")". If you add at the end of these queries "AND 1=1", the formatted results appear ok.
Wed Jun 27, 2007 11:44 pm View user's profile Send private message
sangha



Joined: 19 Dec 2006
Posts: 18

Post Reply with quote
Yes that's right. It's missing some ')' in some cases and adding 'FROM Custome' to the end of the query.
Here's another example of separate formatting with strange results:-
Code:
SELECT * FROM   Customers WHERE  CustomerID IN (1, 2)  AND  1 = (1)
SELECT * FROM   Customers WHERE  CustomerID IN (1, 2)  AND  1 = (1)


Code:
SELECT *
FROM   Customers
WHERE  CustomerID IN (1, 2)
  AND  1 = (
           1SELECT T
            FROM     Custome
            WHERE    CustomerIIN ( , ,)
              AND    = (
                       
                   )
SELECT *
FROM   Customers
WHERE  CustomerID IN (1, 2)
  AND  1 = (
           1
FROM   Custome

Wed Jun 27, 2007 11:54 pm 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.