DB2 10.5 for Linux, UNIX, and Windows

sqlquery function

The db2-fn:sqlquery function retrieves a sequence that is the result of an SQL fullselect in the currently connected DB2® database.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-db2-fn:sqlquery(string-literal-+-----------------------------+-)-><
                                  | .-------------------------. |     
                                  | V                         | |     
                                  '---,--parameter-expression-+-'     

string-literal
Contains a fullselect. The fullselect must specify a single-column result set, and the column must have the XML data type. The scope of the fullselect is a new SQL query scope, not a nested SQL query.

The fullselect cannot contain an isolation clause or lock-request clause.

If the fullselect contains single quotation marks (for example, around a string constant), enclose the function argument in double quotation marks. For example:
"select c1 from t1 where c2 = 'Hello'"
If the fullselect contains double quotation marks (for example, around a delimited identifier), enclose the function argument in single quotation marks. For example:
'select c1 from "t1" where c2 = 47'
If the fullselect contains both single and double quotation marks, enclose the function argument in single quotation marks and represent each internal single quote by two adjacent single quote characters. For example:
'select c1 from "t1" where c2 = ''Hello'''
The fullselect can contain calls to the PARAMETER function to reference the result value from each parameter-expression specified in the db2-fn:sqlquery function invocation. The PARAMETER function calls are substituted with the result value of the corresponding parameter-expression in the execution of the fullselect.
parameter-expression
An XQuery expression that returns a value. The result value of each parameter-expression can be referenced by a designated SQL function PARAMETER with an integer value argument in the SQL fullselect. The integer value is an index to the parameter-expression by its position in the db2-fn:sqlquery function invocation. The valid integer values are between 1 and the total number of the parameter-expression in the function invocation. For example, if the string-literal argument includes PARAMETER(1) and PARAMETER(2) in the SQL fullselect, the function invocation must specify two XQuery parameter-expression arguments. PARAMETER(1) references the result of the first parameter-expression argument and PARAMETER(2) references the result of the second parameter-expression argument.

During the processing of the SQL fullselect, each PARAMETER function call is replaced with the result value of the corresponding parameter-expression in the db2-fn:sqlquery function invocation. Each parameter-expression is evaluated once regardless the number of times it is referenced in the SQL fullselect.

The result data type of the corresponding parameter-expression must be castable to the result type of the PARAMETER function according to the rules of XMLCAST. If not, an error is returned.

The result type of the PARAMETER function is determined as if it were a parameter marker in the SQL fullselect. For example, a parameter marker is indicated by a question mark (?), or a colon followed by a name (:name), in other contexts. If the result type cannot be determined for a PARAMETER function, an error is returned.
Tip: If an untyped parameter marker is not allowed in an operation, you can use the CAST specification or XMLCAST specification to specify a type. For example, to cast PARAMETER(1) to the type DOUBLE, use the following CAST specification, CAST(PARAMETER(1) as double).

Returned value

The returned value is a sequence that is the result of the fullselect in string-literal. The fullselect is processed as an SQL statement, following the usual dynamic SQL rules for authorization and name resolution. If the fullselect contains any calls to the PARAMETER function, they are substituted with the result value of the XQuery expression of the corresponding parameter-expression argument when the fullselect is evaluated. The XML values that are returned by the fullselect are concatenated to form the result of the function. Rows that contain null values do not affect the result sequence. If the fullselect returns no rows or returns only null values, the result of the function is an empty sequence.

The number of items in the sequence that is returned by the db2-fn:sqlquery function can be different from the number of rows that are returned by the fullselect because some of these rows can contain null values or sequences with multiple items.

Examples

Example of fullselects that return a sequence of XML documents: The following example shows several function calls that return the same sequence of documents from table PRODUCT. The documents are in column DESCRIPTION.

Any of the following functions produce the same result:

db2-fn:sqlquery('select description from product')
db2-fn:sqlquery('SELECT DESCRIPTION FROM PRODUCT')
db2-fn:sqlquery('select "DESCRIPTION" from "PRODUCT"')

Example of fullselects that return a single XML document: The following example returns a sequence that is a single document in table PRODUCT. The document is in column DESCRIPTION and is identified by a value of '100-103-01' for column PID.

Any of the following functions produce the same result:

db2-fn:sqlquery('select Description from Product where pID=''100-103-01''')
db2-fn:sqlquery("select description from product where pid='100-103-01'")
db2-fn:sqlquery("select ""DESCRIPTION"" from product where pid='100-103-01'")

Example of fullselect using two PARAMETER function calls and one expression: The following example returns the purchase ID, part ID, and the purchase date for all the parts sold within the promotion dates.

xquery 
for $po in db2-fn:xmlcolumn('PURCHASEORDER.PORDER')/PurchaseOrder,
  $item in $po/item/partid 
for $p in db2-fn:sqlquery( 
  "select description 
  from product 
  where promostart < parameter(1)
  and  promoend  >  parameter(1)", 
  $po/@OrderDate )
where $p//@pid = $item 
return 
<RESULT>
  <PoNum>{data($po/@PoNum)}</PoNum>
  <PartID>{data($item)} </PartID>
  <PoDate>{data($po/@OrderDate)}</PoDate>
</RESULT>

During processing of the db2-fn:sqlquery function, both references to parameter(1) return the value of the order date attribute $po/@OrderDate.

Example of a fullselect using two PARAMETER function calls and two expressions: The following example uses the PURCHASEORDER table from the DB2 SAMPLE database. The XQuery expression retrieves unshipped purchase orders that have an order date before April 4, 2006, and lists the distinct part numbers from each purchase order:

xquery
let $status := ( "Unshipped" ), $date := ( "2006-04-04" )
for $myorders in db2-fn:sqlquery(
   "select porder from purchaseorder
   where status = parameter(1)
   and orderdate < parameter(2)",
   $status, $date )
return
<LateOrder>
  <PoNum>
  {data($myorders/PurchaseOrder/@PoNum)}
  </PoNum>
  <PoDate>
  {data($myorders/PurchaseOrder/@OrderDate)}
  </PoDate>
  <Items>
     {for $itemID in distinct-values( $myorders/PurchaseOrder/item/partid )
     return
     <PartID>
     {$itemID}
     </PartID>}
   </Items>
</LateOrder>

During processing of the db2-fn:sqlquery function, the reference to parameter(1) returns the result value of the expression $status, and the reference to parameter(2) returns the result value of the expression $date.

When run against the SAMPLE database, the expression returns the following result:

<LateOrder>
  <PoNum>5000</PoNum>
  <PoDate>2006-02-18</PoDate>
  <Items>
    <PartID>100-100-01</PartID>
    <PartID>100-103-01</PartID>
  </Items>
</LateOrder>