Commodore
Commodore
593 views

ALM Business View - Convert SQL to DQL

I can write SQL to create an Excel  report in Analsys view using a subquery to get a Min(field) and then use that Min to join back to the same table for additional information, but I want to use Business Views so I can link a spreadsheet to the View and refresh the spreadsheet. 

 

Below is the SQL, but it errors when I paste it in DQL in the business View.

 

Select
F.BG_USER_TEMPLATE_02 /*Defect.Parent ID*/ as 'Defect ID',
F.BG_USER_TEMPLATE_41 /*Defect.Product*/ as 'Product',
F.BG_DETECTED_IN_REL /*Defect.Found In Version*/ as 'Found in Version',
F.BG_DETECTED_IN_RCYC /*Defect.Found in Release*/ as 'Found in Release',
F.BG_SEVERITY /*Defect.Severity*/    ,
F.BG_SUMMARY /*Defect.Summary*/   ,
F.BG_STATUS /*Defect.AGM Defect Status*/,
F.BG_DETECTED_BY /*Defect.Detected By*/,
F.BG_CLOSING_DATE /*Defect.Closed on Date*/  ,
F.BG_DETECTION_DATE /*Defect.Detected on Date*/  ,
F.BG_USER_TEMPLATE_15 /*Defect.Component*/ as 'Component' ,
F.BG_BUG_ID  as  'Fix ID',
F.BG_USER_TEMPLATE_22 /*Defect.Team*/ as 'Team',
F.BG_USER_TEMPLATE_01 /*Defect.Type*/  as 'Type'
From BUG F
Where F.BG_BUG_ID IN
(Select
Min(BUG.BG_BUG_ID) /*Defect.ID*/ as 'Fix id'
FROM
BUG /*Defect*/
WHERE BUG.BG_STATUS /*Defect.AGM Defect Status*/  = 'Closed'
and  BUG.BG_USER_TEMPLATE_01 /*Defect.Type*/   = 'Fix'
GROUP BY    BUG.BG_USER_TEMPLATE_02 /*Defect.Parent ID*/  , BUG.BG_STATUS )

 

In fact if I just try to run the subquery by itself I get an error:

 

QUERY I'm running:

 

Select Min(BUG.BG_BUG_ID) As 'Fix id'
From BUG
Where BUG.BG_STATUS = 'Closed' And BUG.BG_USER_TEMPLATE_01 = 'Fix'
Group By BUG.BG_USER_TEMPLATE_02,
  BUG.BG_STATUS

 

ERROR: DQL Parsing error: mismatched input ''Fix id'' expecting set null in phase PARSER at line 1, column 29 for query: "Select Min(BUG.BG_BUG_ID) As 'Fix id'
From BUG
Where BUG.BG_STATUS = 'Closed' And BUG.BG_USER_TEMPLATE_01 = 'Fix'
Group By BUG.BG_USER_TEMPLATE_02,
  BUG.BG_STATUS".

 

 

0 Likes
3 Replies
Absent Member.
Absent Member.

Hello,

 

Based on the error; this request is a database task .Please check this link with more information about the DQL query usage that might clarify you some details about DQL query:

 

https://en.wikibooks.org/wiki/Structured_Query_Language/Data_Query_Language

 

Also for more information please check guide over the pages ALM 12.20 admin guide on page 344 (to page 366). This explains the DQL use.

 

Sue Sevilla | SW Technical Support Consultant.

Application Lifecycle Management

Mail| sugey.sevilla@hp.com

Hewlett-Packard (SSO Portal)

 

If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.

If you are satisfied with anyone’s response please remember to give them KUDOS by clicking on the STAR at the bottom left of the post and show your appreciation.”

 

0 Likes
Micro Focus Expert
Micro Focus Expert

DQL is similar to SQL but it is entities based and not table based. Hence you should use defect instead of BUG in your statements.

Try to construct new BV using visual editor and see how it is translated into underlying SQL (T-SQL or Oracle). Then convert your SQL query into DQL.

0 Likes
Commodore
Commodore

Thanks everyone -this was actually a problem my select statement - I missed a where clause based and in doing so one of the fields was null unexpectedly.  Sub query that works is:

 

Select defect.user_template_02 As "Parent Defect ID",
    Min(defect.id) As minfix
  From defect
  Where defect.user_template_01 = 'Fix' And defect.status = 'Closed'
  Group By defect.user_template_02

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.