Hi,
Is it better to use SQL transform using custom SQL code inspite of using lookup_ext()
sjain 
(BOB member since 2009-04-17)
Hi,
Just one rule of thumb that I follow, if there is something that I can’t implement using DI, then and only then I use SQL transform!
It all about performance!
data_guy 
(BOB member since 2006-08-19)
I’d also add that the SQL transform shouldn’t be used to mask the developers lack of BODI experience. I’ve seen many occasions where some developers have used it as a shortcut to achieving their goal simply because they dont know how to use DI correctly.
It should only ever be used when there is no other way of doing the work with DI. I’d even say its not all about performance but supportability too. I’d rather see someone develop a few clear dataflows that run slightly longer than a crazy long bit of SQL stuck in SQL Transform.
ScoobyDoo 
(BOB member since 2007-05-10)
Werner Daehn 
(BOB member since 2004-12-17)
Usually not as DI’s engine can only execute the SQL as-is, it can’t optimize the code for execution and it cannot provide any data lineage information through/across SQL transforms.
I’ve only used SQL transforms as input for DI processes if I wanted it to do something very database-specific, had to use some complex SQL to prepare the data for further processing (due to data quality issues that we could not address in the source system) etc. As said above, it’s usually a last resort.
I do use the SQL() command in some of our custom functions which are mostly used for additional logging, stats gathering or workflow control - e.g. updating a Process Control table to flag which files have been loaded etc.
ErikR 
(BOB member since 2007-01-10)