SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA Pro 11.1.107] - Code Formatting Problems

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA Pro 11.1.107] - Code Formatting Problems
Author Message
JerichoJohnson



Joined: 04 Dec 2019
Posts: 5
Country: United States

Post [SA Pro 11.1.107] - Code Formatting Problems Reply with quote
Hello,

I have been using SQL Assistant Pro for 10 years, since v4.0 and the SQL Code Formatting has always been fantastic.

I use Microsoft SQL Server Management Studio v17.9 (14.0.17285.0) and SQL Server.

However, ever since 11.0 came out (and it still isn't fixed in the 11.1.107 released today), I have had many problems with code formatting not working.

First, specifically, when SA formats stored procedure parameters, the first parameter is now always left justified, and I have to manually [tab] it over to be in line with the other parameters. This is true whether there is only one parameter, or multiple. Here are both examples:




Second, many times, the FROM and WHERE clauses are garbled and all left justified and lines incorrectly split.




Here is the complete text of one stored procedure that exhibits both issues so that you can test. If this is some sort of settings issue, then I don't know what it is because I haven't changed any settings in quite some time. I upgraded from 10 to 11 and now to 11.1.107 and the settings are imported from the previous version automatically. Maybe since I have used SA for so many years, there are "old" incompatible settings lingering from past version that are causing this? Don't know. But have never had this kind of formatting problem until 11.

Thanks for your help.

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspReport_MailingList_Members_AllActive]
@MonthID INT
AS
BEGIN
   SET NOCOUNT ON;
   SET ARITHABORT ON;
   
   DECLARE @DefaultEmptyDate DATETIME = DATEFROMPARTS(1800, 1, 1)
   DECLARE @DefaultMaxDate DATETIME = DATEFROMPARTS(5555, 12, 31)
   DECLARE @ExpireDateLastYear DATETIME = DATEFROMPARTS(YEAR(GETDATE()) -1, 12, 31)
   
   SELECT m.MembershipNo           MemberNo,
          m.FirstName,
          m.MiddleName,
          m.LastName,
          ma.Address1,
          ma.Address2,
          ma.City,
          ma.[State]               StateProvince,
          ma.Country,
          ma.PostalCode,
          CASE
               WHEN m.[ExpireDate] = @DefaultMaxDate THEN 'LIFE'
               ELSE CAST(YEAR(m.[ExpireDate]) AS VARCHAR(5))
          END                      PaidTill,
          CASE
               WHEN m.Phone1Type = 'H' THEN m.Phone1
               WHEN m.Phone2Type = 'H' THEN m.Phone2
               WHEN m.Phone3Type = 'H' THEN m.Phone3
          END                      HomePhone,
          CASE
               WHEN m.Phone1Type = 'M' THEN m.Phone1
               WHEN m.Phone2Type = 'M' THEN m.Phone2
               WHEN m.Phone3Type = 'M' THEN m.Phone3
          END                      MobilePhone,
          m.Email,
          mt.TypeAbbrev            MemberType,
          ms.StatusAbbrev          MemberStatus,
          mst.MembershipAbbrev     MembershipType,
          m.[ExpireDate],
          m.iMemberType            OldType,
          m.iStatus                OldStatus,
          CASE
               WHEN ISNULL(m.AlternateAddressID, 0) > 0
                    AND ISNULL(m.AlternateAddressBeg, 0) > 0
                    AND ISNULL(m.AlternateAddressEnd, 0) > 0 THEN CASE
                                                                       WHEN @MonthID >= m.AlternateAddressBeg OR @MonthID =
                                                                            m.AlternateAddressEnd THEN 'SNOWBIRD'
                                                                       ELSE 'PRIMARY'
                                                                  END
               ELSE 'PRIMARY'
          END                      AddrType,
          CASE ma.IsBad
               WHEN 1 THEN 'Yes'
               ELSE 'No'
          END                      AddrIsBad
   FROM   dbo.Member m
          LEFT JOIN dbo.MemberAddress ma
               -- ==============================
               -- Dynamically link to the correct mailing address based on member
               -- having a defined Snowbird address for the month specified
               -- ==============================
               ON  ma.MemberAddressID = CASE
                                             WHEN ISNULL(m.AlternateAddressID, 0) > 0
                   AND ISNULL(m.AlternateAddressBeg, 0) > 0
                   AND ISNULL(m.AlternateAddressEnd, 0) > 0 THEN CASE
                                                                      WHEN @MonthID >= m.AlternateAddressBeg OR @MonthID =
                                                                           m.AlternateAddressEnd THEN m.AlternateAddressID
                                                                      ELSE m.PrimaryAddressID
                                                                 END
                       ELSE m.PrimaryAddressID
END
LEFT JOIN dbo.MemberType mt
ON mt.MemberTypeID = m.MemberTypeID
LEFT JOIN dbo.MemberStatus ms
ON ms.MemberStatusID = m.MemberStatusID
LEFT JOIN dbo.MembershipType mst
ON mst.MembershipTypeID = m.MembershipTypeID
WHERE m.MembershipNo > 0
AND m.ParentMemberID = 0
AND m.PreviousMemberID = 0
AND m.MemberTypeID
IN (1, 5)
AND m.[ExpireDate] > @ExpireDateLastYear
AND m.MemberStatusID
IN (1)
AND m.MembershipTypeID NOT
IN (0, 10, 11, 14)
AND ma.IsBad = 0
ORDER BY
ma.[State],
ma.PostalCode
END

Wed Dec 04, 2019 10:00 am View user's profile Send private message
JerichoJohnson



Joined: 04 Dec 2019
Posts: 5
Country: United States

Post Reply with quote
FYI - I had to re-post the code 4 times because apparently the forum "code" block can't handle a "less than or equal to" (less than character and equal character) and thinks it is html, and garbles the rest of the code.

So, I just removed the less than signs from the statement.

Thanks.
Wed Dec 04, 2019 10:49 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
Could you post the formatting rule for alter procedure and for select, please?
Wed Dec 04, 2019 12:14 pm View user's profile Send private message
JerichoJohnson



Joined: 04 Dec 2019
Posts: 5
Country: United States

Post Reply with quote
Yes. Here are the rules. I am pretty sure they are the default as I have changed very few options here.



Wed Dec 04, 2019 12:52 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Thank you for reporting this issue. Can you please email the complete file you're trying to format to our support account (available on the About tab in the Options) so that we can test it in full?
Wed Dec 04, 2019 2:37 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Both issues appear to be reproducible with default settings. We are actively investigating them.
Thu Dec 05, 2019 12:56 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
We have a fix for both issues available. We can provide a private build for testing sometime next week.
Fri Dec 06, 2019 9:38 am View user's profile Send private message
Mogensen



Joined: 07 Dec 2019
Posts: 1
Country: United States

Post Reply with quote
When are these fixed going to be available for the rest of us?
Mon Dec 09, 2019 7:58 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2100

Post Reply with quote
I'm fairly sure you can ask the support for a link.
Mon Dec 09, 2019 8:12 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Hi,

The previously referenced build containing code formatting engine changes is now available and we think the reported issues have been fixed. If you are interested in trying it, please email to support and we will gladly send you a download link.
Mon Dec 09, 2019 9:48 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Mogensen wrote:
When are these fixed going to be available for the rest of us?


The latest changes in the code formatting engine are still being tested for accuracy and regressions. You are very welcome to join and provide your input. Please email to support and ask for download link for 11.1.110. This will allow you to try the updates with your code and scripts locally and verify it works well for you. If you come across any issues, or the results are not satisfying enough, please let us know and we will address them as quickly as possible. We need your input.
Tue Dec 10, 2019 10:30 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.