What are the challenges you face when working across database platforms? Take the survey
Options

All of a sudden my layout is getting messed up

caderouxcaderoux Posts: 6
edited April 15, 2009 10:29AM in SQL Prompt Previous Versions
Lot of extra spaces which don't appear to have any pattern. Repeated reformatting only makes it worse.

I've reset SQLPrompt (3.9.0.43) to defaults and rebooted my machine and nothing is helping.

Have I accidentally set something wrong in SSMS (2005)?

What should I look for?

Here's a sample:
    TRUNCATE                 TABLE MISWork.BFL_PRE
    TRUNCATE        TABLE MISWork.BFL

    -- Insert
    ;
    WITH    P  AS (
                   SELECT   YYMM_ID
                           ,ALOCN_SRC_CD
                           ,PROD_LINE_NUM
                           ,GL_ACCT_NUM
                           ,LINE_ITEM_NUM
                           ,ISNULL([125],            0)          
                             + ISNULL([130],            0) AS                [131]
                           ,ISNULL([313],            0)          
                             + ISNULL([314],             0) 
                                       + ISNULL(    [321],             0)    
                                    + ISNULL(    [322],               0) 
                                       + ISNULL([677],             0)        
                                + ISNULL(
                                                 [678],             0) AS             [133]
                           ,ISNULL([676],           0)          
                            + ISNULL([677],           0) 
                                     + ISNULL([678],           0) AS           [132]
                           ,ISNULL([280],          0)         + ISNULL([281],           0) 
                                   + ISNULL([282],            0)      
                              + ISNULL([283],           0) 
                                   + ISNULL([284],         0)       
                             + ISNULL([285],        0) 
                                   + ISNULL([286],           0)      
                              + ISNULL([287],            0) 
                                   + ISNULL([288],            0)      
                              + ISNULL([289],          0) 
                                   + ISNULL([290],         0)       
                             + ISNULL([291],         0) AS         [279]
                   FROM     PUASFIN.FocusResults.BTS PIVOT (                SUM(ACCT_STATS_CNT)  FOR  ALOCN_BASE_CD  IN  ([125],                [130],              [313],                 [314],                 [321],                 [322],             [676],
                                                           [677], 
                                                             [678],         [280],                 [281],                 [282],     
                                                         [283],   
                                                           [284], 
                                                             [285],
                                                              [286],                 [287],       
                                                       [288],    
                                                          [289],  
                                                            [290], 
                                                             [291]) ) AS                pvt
                  )
        INSERT  INTO MISWork.BFL_PRE 
                (
                 YYMM_ID
                ,ALOCN_SRC_CD
                ,PROD_LINE_NUM
                ,GL_ACCT_NUM
                ,LINE_ITEM_NUM
                ,ALOCN_BASE_CD
                ,ACCT_STATS_CNT
                )
                SELECT  YYMM_ID
                       ,ALOCN_SRC_CD
                       ,PROD_LINE_NUM
                       ,GL_ACCT_NUM
                       ,LINE_ITEM_NUM
                       ,ALOCN_BASE_CD
                       ,ACCT_STATS_CNT
                FROM    P UNPIVOT (               ACCT_STATS_CNT  FOR  ALOCN_BASE_CD  IN  ([131],           [132],             [133],         [279]) ) AS                unpvt
                WHERE   ACCT_STATS_CNT               <> 0.0

Comments

  • Options
    And all of a sudden it's re-formatting correctly again - no longer adding the extra spaces.
  • Options
    Now it's happening again. It's gradually adding extra spaces in.
  • Options
    It must be something to do with square brackets and single-line comments, because I'm doing a lot of refactoring as I'm building this process and the behavior comes and goes as I make major changes.
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Cade,

    As you probably would guess, I cannot reproduce the issue. SQL Prompt will lay out the SQL code again and again and still put it in a consistent format.

    Maybe if we had an exact representation of the query, we could reproduce the problem. Can you save your query to a file in SSMS, attach it to an email (don't copy/paste the text in) and send it to support (at) red-gate.com?

    Thanks!
  • Options
    emailed a version from last week which has the problem and the final version which doesn't exhibit the problem.

    Thanks,

    Cade
Sign In or Register to comment.