Self-Solve Knowledge Search

Minimize Maximize
Business View Generation issue with Sub-Select
This document has not been formally reviewed for accuracy and is provided as is
Title :
Business View Generation issue with Sub-Select
Document ID :
KM01318208
Product - Version:
quality center 11.52 12.00 12.01 ;
OS :
Updated :
Tue Dec 16 21:15:58 GMT 2014
Summary :
if you generate an excel report or a graph based on it, you will get error massage “The server failed to return business view data”.
Problem

In ALM 11.52 and above, you may add your own business view or modify the existing one with defined DQL as below format, which contains at least two SELECT clauses and the Sub-SELECT clause is after the key word “Not EXISTS”/” EXISTS”/”NOT IN”/”IN” of the WHERE clause.
SELECT clause
FROM clause
WHERE Not EXISTS/ EXISTS/NOT IN/IN
 ( Sub-SELECT clause
   Sub-FROM clause
   Sub-WHERE clause)
 
 
 
Even though the validation of this business view succeeds, if you generate an excel report or a graph based on it, you will get error massage “The server failed to return business view data”.
Cause
The server side would translate DQL of the business view to SQL by parsing the DQL as a tree, when each select clause is a node. During the process, the server is not able to resolve the embedded Select clause with Referred Field in Where clause correctly.
Fix
Change you DQL to avoid using “NOT EXISTS”/” EXISTS”/”NOT IN”/”IN”, instead, use “COUNT” , “NOT EXISTS”/”NOT IN” is equivalent to COUNT(‘required field’)<1, while ” EXISTS”/”IN” is equivalent to COUNT(‘required field’)>0. For example
 
Select test.id
From test
Where test.id Not In (Select test.id From test Where test.id < 2)
 
Change to –
 
Select A.id
From test A
Where (Select Count( B.id) From test B Where B.id < 2 AND B.id=A.id)<1
After changing the DQL, save it for the business view and generate BV graph/BV excel report based on it.