SQL Macro

^SQL();A one-line SQL report that can be used on the WebGUI database or any database with which you have established a database link. Sometimes you just need to pull something back from the database quickly. This macro is also useful in extending the SQL Report wobject. It uses the numeric macros (^0; ^1; ^2; etc) to position data and can also use the ^^rownum; macro just like the SQL Report wobject.

Author First Name BarbaraAuthor Last Name NussbaumPublication Date 03/16/06Publication Type Merchants Of Vision

An example using a MySQL function to format a string:

Users enter a start time into the dataform fields hours and minutes. Sometimes, they enter the minutes without a leading 0, or just leave the field blank completely. To display the time "correctly" I used:

^SQL("SELECT LPAD(<tmpl_var minutes>,2,0) FROM DUAL","^0;");

This will pad any minutes entered with zeros so they always display with 2 digits.

LPAD is a standard MySQL function and DUAL is a psuedo-table that is part of all MySQL databases.