I did some research and could not find a good Teradata SQL formatter. There is a 'Formatter' button in SQL assistant, but the output is a bit ugly. I wrote a small tool with lex & yacc which is able to format a Teradata query.

I will distribute this tool if anybody is interested.

Here is an example:

Original query

SELECT                          SUBSTR(ppackUDF.recordIDOut, 1, 1) locationtype,
    SUBSTR(ppackUDF.recordIDOut, 2, 5) locationid,                          CAST(SUBSTR(ppackUDF.recordIDOut,7,10)
    AS INTEGER) productnumber,                          ppackUDF.vbout
    dpromofcst_vBlock                      FROM                          TABLE (fcst_promopack_tf(
    fcst_promopack12_s.locationtype || fcst_promopack12_s.locationid || CAST(fcst_promopack12_s.productnumber
    AS CHAR(10)) (CHAR(17)),                            fcst_promopack12_s.promotionid,
    fcst_promopack12_s.runtype,                            fcst_promopack12_s.pcount,
    fcst_promopack12_s.startday, fcst_promopack12_s.duration,
    fcst_promopack12_s.winstartday, fcst_promopack12_s.winendday,
    fcst_promopack12_s.sf01, fcst_promopack12_s.sf02,                            fcst_promopack12_s.sf03,
    fcst_promopack12_s.sf04,                            fcst_promopack12_s.sf05, fcst_promopack12_s.sf06,
    fcst_promopack12_s.sf07, fcst_promopack12_s.sf08,
    fcst_promopack12_s.sf09, fcst_promopack12_s.sf10,                            fcst_promopack12_s.sf11,
    fcst_promopack12_s.sf12,                            fcst_promopack12_s.sf13, fcst_promopack12_s.leadtime,
    fcst_promopack12_s.uplifttype,
    fcst_promopack12_s.buyerid,                            fcst_promopack12_s.promoadjustment)
    HASH BY  fcst_promopack12_s.locationtype, fcst_promopack12_s.locationid, fcst_promopack12_s.productnumber
    LOCAL ORDER BY fcst_promopack12_s.locationtype,
    fcst_promopack12_s.locationid, fcst_promopack12_s.productnumber,                            fcst_promopack12_s.runtype,
    fcst_promopack12_s.startday) AS ppackUDF (recordIDOut, vbout)

SQL Assistant formatted query

SELECT                          SUBSTR(ppackUDF.recordIDOut, 1,
  1) locationtype,
    SUBSTR(ppackUDF.recordIDOut, 2, 5) locationid,                          CAST(SUBSTR(ppackUDF.recordIDOut,
  7,10)
    AS INTEGER) productnumber,                          ppackUDF.vbout
    dpromofcst_vBlock                     
FROM                          TABLE (fcst_promopack_tf(
    fcst_promopack12_s.locationtype || fcst_promopack12_s.locationid || CAST(fcst_promopack12_s.productnumber
    AS CHAR(10)) (CHAR(17)),                            fcst_promopack12_s.promotionid,
    fcst_promopack12_s.runtype,                            fcst_promopack12_s.pcount,
    fcst_promopack12_s.startday, fcst_promopack12_s.duration,
    fcst_promopack12_s.winstartday, fcst_promopack12_s.winendday,
    fcst_promopack12_s.sf01, fcst_promopack12_s.sf02,                            fcst_promopack12_s.sf03,
    fcst_promopack12_s.sf04,                            fcst_promopack12_s.sf05,
  fcst_promopack12_s.sf06,
    fcst_promopack12_s.sf07, fcst_promopack12_s.sf08,
    fcst_promopack12_s.sf09, fcst_promopack12_s.sf10,                            fcst_promopack12_s.sf11,
    fcst_promopack12_s.sf12,                            fcst_promopack12_s.sf13,
  fcst_promopack12_s.leadtime,
    fcst_promopack12_s.uplifttype,
    fcst_promopack12_s.buyerid,                            fcst_promopack12_s.promoadjustment)
    HASH BY  fcst_promopack12_s.locationtype, fcst_promopack12_s.locationid,
  fcst_promopack12_s.productnumber
    LOCAL
ORDER BY fcst_promopack12_s.locationtype,
    fcst_promopack12_s.locationid, fcst_promopack12_s.productnumber,
                             fcst_promopack12_s.runtype,
    fcst_promopack12_s.startday) AS ppackUDF (recordIDOut, vbout)

My formatted query

SELECT
    SUBSTR ( ppackUDF.recordIDOut , 1 , 1 ) locationtype , SUBSTR ( ppackUDF.recordIDOut , 2 , 5 ) locationid
    , CAST ( SUBSTR ( ppackUDF.recordIDOut , 7 , 10 ) AS INTEGER ) productnumber
    , ppackUDF.vbout dpromofcst_vBlock
FROM
    TABLE
        (
        fcst_promopack_tf ( fcst_promopack12_s.locationtype
            || fcst_promopack12_s.locationid || CAST ( fcst_promopack12_s.productnumber AS CHAR ( 10 ) ) ( CHAR ( 17 ) )
            , fcst_promopack12_s.promotionid , fcst_promopack12_s.runtype
            , fcst_promopack12_s.pcount , fcst_promopack12_s.startday
            , fcst_promopack12_s.duration , fcst_promopack12_s.winstartday
            , fcst_promopack12_s.winendday , fcst_promopack12_s.sf01
            , fcst_promopack12_s.sf02 , fcst_promopack12_s.sf03
            , fcst_promopack12_s.sf04 , fcst_promopack12_s.sf05
            , fcst_promopack12_s.sf06 , fcst_promopack12_s.sf07
            , fcst_promopack12_s.sf08 , fcst_promopack12_s.sf09
            , fcst_promopack12_s.sf10 , fcst_promopack12_s.sf11
            , fcst_promopack12_s.sf12 , fcst_promopack12_s.sf13
            , fcst_promopack12_s.leadtime , fcst_promopack12_s.uplifttype
            , fcst_promopack12_s.buyerid , fcst_promopack12_s.promoadjustment )
        HASH BY fcst_promopack12_s.locationtype
            , fcst_promopack12_s.locationid , fcst_promopack12_s.productnumber
        LOCAL ORDER BY fcst_promopack12_s.locationtype
            , fcst_promopack12_s.locationid , fcst_promopack12_s.productnumber
            , fcst_promopack12_s.runtype , fcst_promopack12_s.startday
        ) AS ppackUDF ( recordIDOut , vbout
            )

If interested, please leave a message in the comments.

Discussion
tonez01 1 comment Joined 08/10
26 Aug 2010

Hi Jonathan,

I too have been messing the Formatter which is built into SQL Assistant and it doesn't do a very good job. I'd be interested in trying out the utility you have developed and would be happy to give you feedback.

Tony.

pbengine 1 comment Joined 03/07
27 Aug 2010

Hi Jonathan,

I'd be interested too in trying out your utility.
Is there any limits in SQL size?

Patrick.

Jonathan 3 comments Joined 08/10
30 Aug 2010

I have been told Teradata has a good sql formatter in Teradata SQL Assistant Java Edition. Just try it. Here is the info.

Teradata SQL Assistant Java Edition also contains a SQL Formatter. It is based on a successful parse of the SQL statement, then uses parsing nodes to format the SQL.

Another thing to mention is that the Teradata Parser/Formatter is also available as standalone Java API

Innovation provides performance

Darin 4 comments Joined 09/05
13 Sep 2010

Yes, I think they could put some time into the SQL Formatter in SQL Assistiant. I have been using SQLinForm http://www.sqlinform.com whic has lots of options and does a great job.

robpaller 16 comments Joined 05/09
30 Sep 2010

I have also used SQLinForm to format queries with great success. It would be nice to see SQL Assistant mimic the functionality of SQLinForm or the work you have done with your scripting.

Lotus26 1 comment Joined 11/10
24 Nov 2010

Hi Jonathan - I am also in the same boat. Could you please send your utility to me. I really appreciate it and my email id is madala26@hotmail.com

Thanks,
Lotus26

ashwini.ishu 1 comment Joined 04/11
31 Aug 2011

Hi, can you please send me ur utility to ashwini.ishu@gmail.com, thanks..

vijaymathad 1 comment Joined 10/11
11 Oct 2011

Hi Jonathan, please send me ur utility to vijay.mathad@gmail.com

joyenigma 1 comment Joined 10/11
12 Mar 2012

Hi Jonathan, could you send me ur utility to chenhuan_6@hotmail.com, Thanks a lot

katakoti 1 comment Joined 11/12
30 Nov 2012

Hi Jonathan, could you send me ur utility to katakoti@gmail.com.

Thanks in advance!!

Thanks,
Koti

vipatel_2010 1 comment Joined 07/12
09 Jan 2013

Hi Jonathan, could you send me ur utility to vipuldba24@gmail.com.

Thanks,
Vipul

08 Feb 2013

Hi Jonathan,

Could u pls send me ur code to prabakaran.ssiet@gmail.com?

Regards,
Prabakaran R

zazasado1803 1 comment Joined 11/12
19 Feb 2013

Hi Jonathan,

Could you please be kind enough and send me your code to zazasado1803@gmail.com?

Thank you very much in advance.
DZS.

DerekDGuo 1 comment Joined 04/13
30 Apr 2013

Hi Jonathan,
Could you please be kind and share with me your tool?
Appreciate it! Thank you very much.
Regards,
Derek Guo

JhunB 1 comment Joined 03/11
29 Aug 2013

Hi Jonathan,
Could you please send me your code to rabaltazar4345@yahoo.com.ph.
 
Thanks,
Jhun
 
 

Ranga69 1 comment Joined 11/13
19 Nov 2013

Hi Jonathan
Can you please send me the formatter as well. My email address is raym846@gmail.com
 
Thanks
 
Ranga
 

bunch1962 6 comments Joined 09/12
06 Dec 2013

Hi Jonathan,
May I have a copy of your utility? My email address is bunch1962@comcast.net.  Thanks in advance.
Dave

Dave Bunch

jain.aalok 1 comment Joined 02/14
06 Feb 2014

would love to have a copy of your utility please, email id jain.aalok@gmail.com.
Much appreciated.
AJ

wmmiteff 8 comments Joined 02/11
07 Feb 2014

Can i get a copy of this sent to me at wmmiteff@gmail.com

mmarisa 1 comment Joined 07/14
02 Jul 2014

can i get a copy, please email to mimi.marisa@yahoo.com

Jonathan 3 comments Joined 08/10
01 Aug 2014

This tool can be downloaded @
https://github.com/jonathanyan/tools

Innovation provides performance

You must sign in to leave a comment.