SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[10.0.158 BETA] - Formatting issues 2

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[10.0.158 BETA] - Formatting issues 2
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [10.0.158 BETA] - Formatting issues 2 Reply with quote
I've run into difficulties when trying to incorporate the INTO part of a SELECT statement into my SELECT rules.

Given is the following code:
Code:

SELECT a._year, a._quarter, a._source_name, a._db_name, a._path, a.drop_db_stmt, a.create_db_stmt, a.create_tab_stmt, a.index_stmt, a.xfer_stmt, a.remove_stmt FROM @archives AS a ORDER BY a._year, _quarter;


and the following two rules:
#1
Code:

SELECT ... AS ..., ... = ...
INTO ...
FROM ... AS ..., ... JOIN ... ON    ... = ... AND ... OR ... WHERE ... = ... AND ... OR ... GROUP BY ..., ... HAVING ..., ... ORDER BY ..., ...;


and #2 which is a slightly modified version of the factory default (also, note that it is missing the INTO part)
Code:

SELECT
    ... AS ...
   ,... = ...
FROM
    ... AS ...
   ,...
JOIN
    ...
    ON  ... = ...
        AND ...
        OR  ...
WHERE   ... = ...
    AND ...
    OR  ...
GROUP BY
    ...
   ,...
HAVING
    ...
   ,...
ORDER BY
    ...
   ,...
;


Passing the above one-liner through #1 only drops the closing ; to the next line, which clearly is not the desired output.
Code:

SELECT a._year, a._quarter, a._source_name, a._db_name, a._path, a.drop_db_stmt, a.create_db_stmt, a.create_tab_stmt, a.index_stmt, a.xfer_stmt, a.remove_stmt FROM @archives AS a ORDER BY a._year, _quarter
;



Allowing this (or the original one-liner) through #2 will give this:
Code:

SELECT
    a._year
   ,a._quarter
   ,a._source_name
   ,a._db_name
   ,a._path
   ,a.drop_db_stmt
   ,a.create_db_stmt
   ,a.create_tab_stmt
   ,a.index_stmt
   ,a.xfer_stmt
   ,a.remove_stmt
FROM
    @archives AS a
ORDER BY
    a._year
   ,_quarter
;


Now throwing this left-over to #1 again just makes a greater mess:
Code:

SELECT a._year, a._quarter, a._source_name, a._db_name, a._path, a.drop_db_stmt, a.create_db_stmt, a.create_tab_stmt, a.index_stmt, a.xfer_stmt, a.remove_stmt
       FROM
       @archives AS a
       ORDER BY
       a._year, _quarter
;


Removing the INTO ... from the rule will force it to behave as expected.
Code:

SELECT a._year, a._quarter, a._source_name, a._db_name, a._path, a.drop_db_stmt, a.create_db_stmt, a.create_tab_stmt, a.index_stmt, a.xfer_stmt, a.remove_stmt
FROM @archives AS a ORDER BY a._year, _quarter;


Of course, that ruins codes containing INTO in exchange. Also adding INTO to the #2 rule makes it screw INTO-less code. I've tried to have two SELECT rules, one with INTO and one without but it doesn't seem to work.

Shouldn't all the non-first keywords regarded as optional and only used as anchors? Any hints on how to get the best of both worlds?
Wed Aug 29, 2018 12:33 pm View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I also have a rule for IF BEGIN:
Code:

IF ... AND ... OR ...
BEGIN
    <stmtList>;
END


Using this with #2 yields the expected result:
Code:

IF @debug > 0
BEGIN
    SELECT
        a._year
       ,a._quarter
       ,a._source_name
       ,a._db_name
       ,a._path
       ,a.drop_db_stmt
       ,a.create_db_stmt
       ,a.create_tab_stmt
       ,a.index_stmt
       ,a.xfer_stmt
       ,a.remove_stmt
    FROM
        @archives AS a
    ORDER BY
        a._year
       ,_quarter
    ;
END


However, #1 breaks even this one:
Code:

IF @debug>0
BEGIN
    SELECT a._year, a._quarter, a._source_name, a._db_name, a._path, a.drop_db_stmt, a.create_db_stmt, a.create_tab_stmt, a.index_stmt, a.xfer_stmt, a.remove_stmt
    FROM @archives AS a ORDER BY a._year, _quarter;END

Wed Aug 29, 2018 12:42 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.