Administer > Database administration > Data persistence > Queries > Cross-table join query improvements > Example: Handle BLOB, CLOB, text and images

Example: Handle BLOB, CLOB, text and images

This query shows all open Incidents, where current operator is a member of an assignment group (Support Groups) that is defined in the affected service.

Note  

  • Because an RDBMS cannot search on a BLOB field directly, if you want to use BLOB field as a query condition, you must map the BLOB field as multi-row array.
  • You must map support.groups from DEVICE2M1 to DEVICE2A1 for this example to work. Otherwise, you will receive the following error:

    Queries on Blob/Clob/Text/Image field is not supported in cross table query. (display,process.option) Field "ta02.support.groups" in the query is not mapped or mapped to Blob/Clob/Text/Image. Queries on Blob/Clob/Text/Image field is not supported in cross table query. (display,process.option)

var file = new SCFile("probsummary");
var sql = "SELECT ta01.*, ta02.support.groups FROM probsummary ta01 LEFT OUTER JOIN device ta02 ON  ( ta01.affected.item = ta02.logical.name )  WHERE ( ta02.support.groups isin $lo.pm.assignments and ta01.flag=true )";
var success = file.doSelect(sql);
while (success == RC_SUCCESS) 
{
    success = findNextRecord = file.getNext();    
}