Beautifier in 6.0.1 - ANSI

[DazzaL]

A while back i read a post on ANSI support, at which point the admin says the beautifier would work with ansi from release 6.0 onwards but i still see issues (from my point of view) with it:

formatted with 6.0.1 - no ansi:

select *
  from a, b, c
 where a.id = b.foreign_id
   and a.c_id = c.id
   and a.criteria = somevalue

and with ansi equivalent:

select *
  from a join b on a.id = b.foreign_id join c on a.c_id = c.id where
 a.criteria = somevalue
               and b.criteria = somevalue2

why does the "where" end up on the same line as the "from"? and that "and" is wierd in its location.

maybe i just havent configured the beautifier correctly?

Regards,

--
Daz.

 

[Marco Kalter]

It's a bug. We'll fix it for 6.0.2.

 

[bosco]

As we convert more to the ANSI standard we find that the format of the beautifer is limited. You can seperate the AND/OR to a new line, but you can not determine if you would like the ON to be on a new line.

In the original example all the table names are a single character, but with different table names the ON conditions are no longer aligned.

SELECT *
  FROM aaaaaa a
  JOIN bbb ON a.id = b.foreign_id
  JOIN cccccccccccc c ON a.c_id = c.ID
 WHERE a.criteria2 = somevalue
   AND a.criteria1 = somevalue
   AND b.criteria = somevalue2
   AND (b.criteria = somevalue2 OR b.criteria = somevalue2)

Also if you code the AND's with the join you should be able to align all the condition (ON,OR,AND), not just to the previous ON

SELECT *
    FROM aaaaaa a
    JOIN bbb ON a.id = b.foreign_id
            AND b.criteria = somevalue2
    JOIN cccccccccccc c ON a.c_id = c.ID
                       AND (b.criteria = somevalue2 OR
                           b.criteria = somevalue2)
   WHERE a.criteria = somevalue
     AND a.criteria1 = somevalue

I'd like to be able to have the result of the format as

SELECT *
    FROM aaaaaa a
    JOIN bbb
     ON  a.id = b.foreign_id
     AND b.criteria = somevalue2
    JOIN cccccccccccc c
     ON  a.c_id = c.ID
     AND (b.criteria = somevalue2 OR b.criteria = somevalue2)
   WHERE a.criteria = somevalue
     AND a.criteria_1 = somevalue

Thanks
Bosco (vs7.0.3.1123)

 

[Marco Kalter]

I have added this to the list of enhancement requests.