ucReportSetUdfMultiSql

Purpose

ucReportSetUdfMultiSql is used to override the SQL statement used to look up multi-value UDF’s.

Applies To

Search & Reporting screens

Signature

public String ucReportSetUdfMultiSql (
      String[] udfName,		// database name of the UDF
      String sqlStatement,	// standard sql statement
      String problemKey,	// alias for the problem id
      String idList,
      boolean doTitleMap) 	// in clause for problem ids

Notes

This can be used to retrieve arbitrary information to display, using the multi value UDF list handling.

Example

public String ucReportSetUdfMultiSql(
                           String[] udfName, // database name of the UDF
                           String sqlStatement, // standard sql statement
                           String itemKeyAlias, // alias for the item id
                           String idList, // in clause for item ids.
                           boolean doTitleMap) {
      if (udfName == null || udfName.length() == 0)
       udfName = "RELATED_ISSUES";

      if (!"RELATED_ISSUES".equalsIgnoreCase(udfName) )
       return sqlStatement ;

      return "select i.item_id " + problemKey + ", '" + udfName 
                       + "' UDF_NAME, i2.item_id TITLE " +
        " from (select item_id from item where item_id in " + idList + ") i, 
                       item i2, " +
        " (select item_id, value_number from udf, item_udf " +
        "   where udf.name = 'PARENT_ID' and item_udf.udf_id = udf.udf_id) iu, " +
        " (select item_id, value_number from udf, item_udf " +
        " where udf.name = 'PARENT_ID' and item_udf.udf_id = udf.udf_id) iu2" +
        
        // include dummy parameter bind condition which will do nothing
        " where ? = '" + udfName + "'" +
       
        // exclude the current bug from the list
        "  and i2.item_id != i.item_id " +
        "  and (" +
        " (i2.item_id = iu.item_id and iu.value_number 
                = i.item_id and iu2.item_id 
                = iu.item_id and iu2.value_number= iu.value_number) " +// direct children
       
          "or (i2.item_id = iu.value_number and iu.item_id 
                = i.item_id and iu2.item_id = iu.item_id 
               and iu2.value_number= iu.value_number) " +// parent
          "or (i2.item_id = iu2.item_id and iu2.value_number 
                = iu.item_id and iu.value_number = i.item_id) " + // direct grandchildren
          " or (i2.item_id = iu2.item_id and iu2.value_number 
                = iu.value_number and iu.item_id = i.item_id) " + // peers
           " or (i2.item_id = iu2.value_number and iu2.item_id 
                = iu.value_number and iu.item_id = i.item_id) " + // grandparent
        " ) order by " + problemKey + ",UDF_NAME, TITLE" ;
     }
    return sqlStatement;
  }