Formatting Bug

Provides intelligent code completion for SQL Server editors.

Moderators: JonathanWatts, David Atkinson, Anu Deshpande, Paul Stephenson, Michelle Taylor, Mike Upton, justin.caldicott, Aaron Law

Formatting Bug

Postby eklein@ahd.com » Mon Aug 13, 2012 8:11 pm

If I start with the following code
Code: Select all

SELECT  *
INTO    #tmp2
FROM    ( VALUES ( 'A703', '00100'), ( 'A703', '00200'), ( 'A803', '01200'),
        ( 'G200', '01100'), ( 'G200', '01200'), ( 'G200', '01400'),
        ( 'G200', '02000'), ( 'G200', '02500'), ( 'G200', '02700'),
        ( 'D200', '00500'), ( 'D401', '00300'), ( 'D401', '02000'),
        ( 'D401', '02400'), ( 'D401', '02700'), ( 'D401', '02900'),
        ( 'D401', '03900'), ( 'D401', '04000'), ( 'D401', '03400'),
        ( 'D401', '03700'), ( 'D401', '02600'), ( 'D401', '02800'),
        ( 'D401', '03800'), ( 'D402', '04300'), ( 'D402', '04600'),
        ( 'D400', '00200'), ( 'D400', '00500'), ( 'D400', '00800'),
        ( 'D400', '01200'), ( 'D400', '01300'), ( 'D400', '01700'),
        ( 'D400', '01800'), ( 'D400', '02100'), ( 'D400', '02300'),
        ( 'D400', '02600'), ( 'D400', '02800'), ( 'D400', '03400'),
        ( 'D400', '03700'), ( 'D400', '03800'), ( 'D400', '04300'),
        ( 'D400', '04600'), ( 'D400', '05100'), ( 'D102', '04300'),
        ( 'D102', '04400'), ( 'D102', '04500'), ( 'D102', '04600'),
        ( 'D102', '04700'), ( 'H302', '00400'), ( 'H302', '00500'),
        ( 'S301', '00800'), ( 'S301', '00900'), ( 'S301', '01000'),
        ( 'S301', '01100'), ( 'S301', '01200'), ( 'S301', '02000'),
        ( 'S301', '02200'), ( 'S420', '02000'), ( 'K503', '00700'),
        ( 'K503', '00800'), ( 'K503', '01000'), ( 'K503', '01000'),
        ( 'K503', '01000'), ( 'S301', '02800'), ( 'S302', '00700'),
        ( 'S302', '00400') ) AS tmp2 ( sheet, base_line )     
                  UPDATE    wk_ref_line_master
              SET       ok_2_use = CASE WHEN wrlf.source = 'A' THEN 'S'
                                        ELSE 'B'
                                   END
                      , super_rule = 'rollup know sublines and drop alpha on sublines  '
              FROM      wk_ref_line_master AS wrlf
              INNER JOIN [#tmp2] AS t ON wrlf.wks_4pos = t.sheet
                                         AND wrlf.base_line = t.base_line
              WHERE     ( wrlf.ok_2_use = 'N' )

   


if I do a check systax I get no errors , If do a format sql I get an error message. If I select from top top down to but not including the line with the Update statement and then do a SQL format I get the following

Code: Select all
SELECT  *
INTO    #tmp2
FROM    ( VALUES ( 'A703', '00100'), ( 'A703', '00200'), ( 'A803', '01200'),
        ( 'G200', '01100'), ( 'G200', '01200'), ( 'G200', '01400'),
        ( 'G200', '02000'), ( 'G200', '02500'), ( 'G200', '02700'),
        ( 'D200', '00500'), ( 'D401', '00300'), ( 'D401', '02000'),
        ( 'D401', '02400'), ( 'D401', '02700'), ( 'D401', '02900'),
        ( 'D401', '03900'), ( 'D401', '04000'), ( 'D401', '03400'),
        ( 'D401', '03700'), ( 'D401', '02600'), ( 'D401', '02800'),
        ( 'D401', '03800'), ( 'D402', '04300'), ( 'D402', '04600'),
        ( 'D400', '00200'), ( 'D400', '00500'), ( 'D400', '00800'),
        ( 'D400', '01200'), ( 'D400', '01300'), ( 'D400', '01700'),
        ( 'D400', '01800'), ( 'D400', '02100'), ( 'D400', '02300'),
        ( 'D400', '02600'), ( 'D400', '02800'), ( 'D400', '03400'),
        ( 'D400', '03700'), ( 'D400', '03800'), ( 'D400', '04300'),
        ( 'D400', '04600'), ( 'D400', '05100'), ( 'D102', '04300'),
        ( 'D102', '04400'), ( 'D102', '04500'), ( 'D102', '04600'),
        ( 'D102', '04700'), ( 'H302', '00400'), ( 'H302', '00500'),
        ( 'S301', '00800'), ( 'S301', '00900'), ( 'S301', '01000'),
        ( 'S301', '01100'), ( 'S301', '01200'), ( 'S301', '02000'),
        ( 'S301', '02200'), ( 'S420', '02000'), ( 'K503', '00700'),
        ( 'K503', '00800'), ( 'K503', '01000'), ( 'K503', '01000'),
        ( 'K503', '01000'), ( 'S301', '02800'), ( 'S302', '00700'),
        ( 'S302', '00400') ) AS tmp2 ( sheet, base_line )                   UPDATE    wk_ref_line_master
              SET       ok_2_use = CASE WHEN wrlf.source = 'A' THEN 'S'
                                        ELSE 'B'
                                   END
                      , super_rule = 'rollup know sublines and drop alpha on sublines  '
              FROM      wk_ref_line_master AS wrlf
              INNER JOIN [#tmp2] AS t ON wrlf.wks_4pos = t.sheet
                                         AND wrlf.base_line = t.base_line
              WHERE     ( wrlf.ok_2_use = 'N' )

[/code]

The \"Update \" statement was NOT part of the selection
eklein@ahd.com
 
Posts: 3
Joined: Thu Mar 05, 2009 5:07 pm

Return to SQL Prompt 5

Who is online

Users browsing this forum: No registered users and 0 guests