Several SQL Anywhere procedures and functions take SQL statements as string arguments; for example,
- sa_describe_query() returns information about all the columns in a result set,
- rewrite() shows how the query optimizer is going to manipulate your query, and
- graphical_plan() returns the execution plan used by the query engine.
Here's an example:
1 | <br>SELECT STRING ( <br> '<HTML><HEAD><TITLE>',<br> 'All the demo database''s sales identifiers',<br> '</TITLE></HEAD><TABLE>',<br> LIST ( <br> STRING ( <br> '<TR><TD>', SalesOrders.ID,<br> '</TD><TD>', SalesOrderItems.LineID,<br> '</TD><TD>', Products.ID,<br> '</TD></TD>' ),<br> ''<br> ORDER BY SalesOrders.ID,<br> SalesOrderItems.LineID,<br> Products.ID ),<br> '</TABLE></HTML>' )<br> FROM SalesOrderItems<br> INNER JOIN SalesOrders<br> ON SalesOrders.ID = SalesOrderItems.ID<br> INNER JOIN Products<br> ON Products.ID = SalesOrderItems.ProductID<br> |
Here's how it has to be coded if you're going to pass the SELECT as a string literal to the rewrite() function:
1 | <br>SELECT rewrite ( '<br>SELECT STRING ( <br> ''<HTML><HEAD><TITLE>'',<br> ''All the demo database''''s sales identifiers'',<br> ''</TITLE></HEAD><TABLE>'',<br> LIST ( <br> STRING ( <br> ''<TR><TD>'', SalesOrders.ID,<br> ''</TD><TD>'', SalesOrderItems.LineID,<br> ''</TD><TD>'', Products.ID,<br> ''</TD></TD>'' ),<br> ''''<br> ORDER BY SalesOrders.ID,<br> SalesOrderItems.LineID,<br> Products.ID ),<br> ''</TABLE></HTML>'' )<br> FROM SalesOrderItems<br> INNER JOIN SalesOrders<br> ON SalesOrders.ID = SalesOrderItems.ID<br> INNER JOIN Products<br> ON Products.ID = SalesOrderItems.ProductID<br>' )<br> |
But... there's an easier way: Copy the SELECT as-is (no doubling-up of the quotes required) to a separate text file, and then code xp_read_file() as the argument to rewrite().
Here, the SELECT has been copied to a text file called select.txt:
1 | <br>SELECT rewrite ( xp_read_file ( 'select.txt' ) )<br> |
By the way, here's what rewrite() had to say about the SELECT; no need for any joins at all since the SalesOrderItems table has all the columns that are needed:
1 | <br>select<br> STRING(<br> '<HTML><HEAD><TITLE>',<br> 'All the demo database''s sales identifiers',<br> '</TITLE></HEAD><TABLE>',<br> LIST(<br> STRING(<br> '<TR><TD>',SalesOrderItems_1.ID,<br> '</TD><TD>',SalesOrderItems_1.LineID,<br> '</TD><TD>',SalesOrderItems_1.ProductID,<br> '</TD></TD>'),<br> '' order by<br> SalesOrderItems_1.ID asc,<br> SalesOrderItems_1.LineID asc,<br> SalesOrderItems_1.ProductID asc),<br> '</TABLE></HTML>')<br> from SalesOrderItems as SalesOrderItems_1<br> |
No comments:
Post a Comment