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:
SELECT STRING (
'<HTML><HEAD><TITLE>',
'All the demo database''s sales identifiers',
'</TITLE></HEAD><TABLE>',
LIST (
STRING (
'<TR><TD>', SalesOrders.ID,
'</TD><TD>', SalesOrderItems.LineID,
'</TD><TD>', Products.ID,
'</TD></TD>' ),
''
ORDER BY SalesOrders.ID,
SalesOrderItems.LineID,
Products.ID ),
'</TABLE></HTML>' )
FROM SalesOrderItems
INNER JOIN SalesOrders
ON SalesOrders.ID = SalesOrderItems.ID
INNER JOIN Products
ON Products.ID = SalesOrderItems.ProductID
Here's how it has to be coded if you're going to pass the SELECT as a string literal to the rewrite() function:
SELECT rewrite ( '
SELECT STRING (
''<HTML><HEAD><TITLE>'',
''All the demo database''''s sales identifiers'',
''</TITLE></HEAD><TABLE>'',
LIST (
STRING (
''<TR><TD>'', SalesOrders.ID,
''</TD><TD>'', SalesOrderItems.LineID,
''</TD><TD>'', Products.ID,
''</TD></TD>'' ),
''''
ORDER BY SalesOrders.ID,
SalesOrderItems.LineID,
Products.ID ),
''</TABLE></HTML>'' )
FROM SalesOrderItems
INNER JOIN SalesOrders
ON SalesOrders.ID = SalesOrderItems.ID
INNER JOIN Products
ON Products.ID = SalesOrderItems.ProductID
' )
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:
SELECT rewrite ( xp_read_file ( 'select.txt' ) )
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:
select
STRING(
'<HTML><HEAD><TITLE>',
'All the demo database''s sales identifiers',
'</TITLE></HEAD><TABLE>',
LIST(
STRING(
'<TR><TD>',SalesOrderItems_1.ID,
'</TD><TD>',SalesOrderItems_1.LineID,
'</TD><TD>',SalesOrderItems_1.ProductID,
'</TD></TD>'),
'' order by
SalesOrderItems_1.ID asc,
SalesOrderItems_1.LineID asc,
SalesOrderItems_1.ProductID asc),
'</TABLE></HTML>')
from SalesOrderItems as SalesOrderItems_1
0 comments:
Post a Comment