SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[SA 9.0.176 Pro] - Insert code formatting

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[SA 9.0.176 Pro] - Insert code formatting
Author Message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post [SA 9.0.176 Pro] - Insert code formatting Reply with quote
I have a bit of code I wrote out:

Code:


INSERT INTO @Sequence
  (RunNo,SampID,SampType,RunDate,Direction)
VALUES
(1,'CCV1','CCV','11/4/2016 08:00','D'),
(1,'Samp1','SAMP','11/4/2016 08:05',NULL),
(1,'Samp2','SAMP','11/4/2016 08:10',NULL),
(1,'Samp3','SAMP','11/4/2016 08:15',NULL),
(1,'Samp4','SAMP','11/4/2016 08:20',NULL),
(1,'CCV2','CCV','11/4/2016 08:25','UD'),
(1,'Samp5','SAMP','11/4/2016 08:30',NULL),
(1,'Samp6','SAMP','11/4/2016 08:35',NULL),
(1,'Samp7','SAMP','11/4/2016 08:40',NULL),
(1,'Samp8','SAMP','11/4/2016 08:45',NULL),
(1,'CCV3','CCV','11/4/2016 08:50','UD'),
(1,'Samp9','SAMP','11/4/2016 08:55',NULL),
(1,'Samp10','SAMP','11/4/2016 09:00',NULL),
(1,'Samp11','SAMP','11/4/2016 09:05',NULL),
(1,'Samp12','SAMP','11/4/2016 09:10',NULL),
(1,'CCV4','CCV','11/4/2016 09:15', 'U')


But when I format it:

Code:

INSERT INTO @Sequence
  (
    RunNo
   ,SampID
   ,SampType
   ,RunDate
   ,Direction
  )
VALUES
  (
    1
   ,'CCV1'
   ,'CCV'
   ,'11/4/2016 08:00'
   ,'D'
  ),
(
    1,
    AND .'Samp1',
    AND .'SAMP',
    AND .'11/4/2016 08:05',
    AND .NULL
),
(
    1,
    AND .'Samp2',
    AND .'SAMP',
    AND .'11/4/2016 08:10',
    AND .NULL
),
(
    1,
    AND .'Samp3',
    AND .'SAMP',
    AND .'11/4/2016 08:15',
    AND .NULL
),
(
    1,
    AND .'Samp4',
    AND .'SAMP',
    AND .'11/4/2016 08:20',
    AND .NULL
),
(
    1,
    AND .'CCV2',
    AND .'CCV',
    AND .'11/4/2016 08:25',
    AND .'UD'
),
(
    1,
    AND .'Samp5',
    AND .'SAMP',
    AND .'11/4/2016 08:30',
    AND .NULL
),
(
    1,
    AND .'Samp6',
    AND .'SAMP',
    AND .'11/4/2016 08:35',
    AND .NULL
),
(
    1,
    AND .'Samp7',
    AND .'SAMP',
    AND .'11/4/2016 08:40',
    AND .NULL
),
(
    1,
    AND .'Samp8',
    AND .'SAMP',
    AND .'11/4/2016 08:45',
    AND .NULL
),
(
    1,
    AND .'CCV3',
    AND .'CCV',
    AND .'11/4/2016 08:50',
    AND .'UD'
),
(
    1,
    AND .'Samp9',
    AND .'SAMP',
    AND .'11/4/2016 08:55',
    AND .NULL
),
(
    1,
    AND .'Samp10',
    AND .'SAMP',
    AND .'11/4/2016 09:00',
    AND .NULL
),
(
    1,
    AND .'Samp11',
    AND .'SAMP',
    AND .'11/4/2016 09:05',
    AND .NULL
),
(
    1,
    AND .'Samp12',
    AND .'SAMP',
    AND .'11/4/2016 09:10',
    AND .NULL
),
(
    1,
    AND .'CCV4',
    AND .'CCV',
    AND .'11/4/2016 09:15',
    AND .'U'
)


The first one formats ok, then everything after that starts getting a bunch of "AND"s in it. I assume it's using the "(..)" rule instead of the "INSERT" rule?
Fri Nov 04, 2016 11:10 am View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
Ok if I disable the "(..)" rule I get:

Code:


INSERT INTO @Sequence
  (
    RunNo
   ,SampID
   ,SampType
   ,RunDate
   ,Direction
  )
VALUES
  (
    1
   ,'CCV1'
   ,'CCV'
   ,'11/4/2016 08:00'
   ,'D'
  ),
(1,'Samp1','SAMP','11/4/2016 08:05',NULL),
(1,'Samp2','SAMP','11/4/2016 08:10',NULL),
(1,'Samp3','SAMP','11/4/2016 08:15',NULL),
(1,'Samp4','SAMP','11/4/2016 08:20',NULL),
(1,'CCV2','CCV','11/4/2016 08:25','UD'),
(1,'Samp5','SAMP','11/4/2016 08:30',NULL),
(1,'Samp6','SAMP','11/4/2016 08:35',NULL),
(1,'Samp7','SAMP','11/4/2016 08:40',NULL),
(1,'Samp8','SAMP','11/4/2016 08:45',NULL),
(1,'CCV3','CCV','11/4/2016 08:50','UD'),
(1,'Samp9','SAMP','11/4/2016 08:55',NULL),
(1,'Samp10','SAMP','11/4/2016 09:00',NULL),
(1,'Samp11','SAMP','11/4/2016 09:05',NULL),
(1,'Samp12','SAMP','11/4/2016 09:10',NULL),
(1,'CCV4','CCV','11/4/2016 09:15', 'U')


I don't mind a compact parens in this case, but the code rule expands the commas downward for the first result, but then it seems to 'forget' to do that for the rest.
Fri Nov 04, 2016 11:15 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
What you see there is a combined effect of the insert rule and the (...) rule. Please note that the order is important, rules are applied sequentially. I think if you move insert to the top, the end result should be different.
Fri Nov 04, 2016 1:04 pm View user's profile Send private message
Mindflux



Joined: 25 May 2013
Posts: 616
Country: United States

Post Reply with quote
Interesting.. I would have thought the rules would apply on a 'best match' scenario.

Since the statement has 'insert' I would have thought it would have chosen that insert rule over (..)

Not only that, but I can't seem to move the rules up or down... they're simply in alphabetical order.

*Edit: Also why does it expand the first row under VALUES but none of the rest?
Fri Nov 04, 2016 1:23 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 6499

Post Reply with quote
Quote:
Interesting.. I would have thought the rules would apply on a 'best match' scenario.


Multiple rules are supposed to be applied based on their pattern matching, it's not one rule one statement processing. The insert rule, is like insert into (....) values (...) that's why only the first set of (...) values is formatted by that rule. But now that i'm trying to reproduce this case, I'm seeing something else is in play there. If I get it correctly, the code formatter lines up recurring (...) after the first insert into (....) values (...) as function parameters and as if there was a function call after the first set of values inserted.

I actually created a new insert many rule with the following code and I think it does what you want

Code:
INSERT ...
  (
    ...
    ,...
  )
VALUES
  (
    ...
    ,...
  )
  ,(
    ,...
    ...
  )
  ,...

Fri Nov 04, 2016 4:57 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.