You can use it in lookup override where you want to use dynamic sql override, Look up override which can be controlled from parameter file.

We are doing an indirect load of flat file those have the same structure. There is a single target to be appended with this data. Lets say these source files can have Tier1/2/3 sales data that particularly explains the partner type involved in the particular sale,,,, File names will be of this sort.. Filexxxxxxxx_<partnertype>.dat ,,,,,, you predicted that your Lookup SQL can be made unique for every partner type data that will yield a greater performance. So a mapping variable in Lookup override can help to achieve this ——Filename_withoutdat=substr(filename,1,instr(filename,’.’)),,,$$var_lkpsql = substr(filename_withoutdat,instr(filename,’_’),length(filename_withoutdat)),,,,,,

Lookup override – where partner_type = ‘$$var_lkpsql’