Teradata SQL Formatter

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

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.

21 Comments
Enthusiast
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.
Hi Jonathan,

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

Patrick.
Teradata Employee
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
Enthusiast
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.
Enthusiast
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.
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
Enthusiast
Hi, can you please send me ur utility to ashwini.ishu@gmail.com, thanks..
Hi Jonathan, please send me ur utility to vijay.mathad@gmail.com
Hi Jonathan, could you send me ur utility to chenhuan_6@hotmail.com, Thanks a lot
Enthusiast
Hi Jonathan, could you send me ur utility to katakoti@gmail.com.

Thanks in advance!!

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

Thanks,
Vipul
Hi Jonathan,

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

Regards,
Prabakaran R
Hi Jonathan,

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

Thank you very much in advance.
DZS.

Hi Jonathan,

Could you please be kind and share with me your tool?

Appreciate it! Thank you very much.

Regards,

Derek Guo

Teradata Employee

Hi Jonathan,

Could you please send me your code to rabaltazar4345@yahoo.com.ph.

Thanks,

Jhun

Hi Jonathan

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

Thanks

Ranga

Enthusiast

Hi Jonathan,

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

Dave

Visitor

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

Much appreciated.

AJ

Enthusiast

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

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

Teradata Employee

This tool can be downloaded @

https://github.com/jonathanyan/tools