Highlighted
Outstanding Contributor.
Outstanding Contributor.
1682 views

SQL query in Dashboard or View

Jump to solution

Hello guys,
does anyone know how can I create SQL query in Dashboard or View? Is that possible?

I want to use this query:
SELECT        APPROVALLOGM1.COUNTER, APPROVALLOGM1.FILE_NAME, APPROVALLOGM1.UNIQUE_KEY, APPROVALLOGM1.CATEGORY, APPROVALLOGM1.CURRENT_PHASE, APPROVALLOGM1.ACTION,
                         APPROVALLOGM1.DATE, APPROVALLOGM1.OPERATOR, APPROVALLOGM1.DELEGATE, APPROVALLOGM1.[GROUP], APPROVALLOGM1.COMMENTS, APPROVALLOGM1.NAME,
                         APPROVALLOGM1.OPERATOR_FULLNAME, APPROVALLOGM1.SYSMODTIME, APPROVALLOGM1.SYSMODUSER, APPROVALLOGM1.SYSMODCOUNT, APPROVALLOGM1.COMPONENT,
                         APPROVALLOGM1.REJECT_REASON, REQUESTM1.REQUESTED_FOR, REQUESTM1.AFFECTED_ITEM, REQUESTM1.PROBLEM_TYPE
FROM            APPROVALLOGM1 LEFT OUTER JOIN
                         REQUESTM1 ON APPROVALLOGM1.UNIQUE_KEY = REQUESTM1.NUMBER
Best regards,
Dino

Tags (1)
0 Likes
1 Solution
9 Replies
Highlighted
Honored Contributor.
Honored Contributor.

Re: SQL query in Dashboard or View

Jump to solution

Were in the same page here, inbox.view or Favorites and Dashboards basically query only in 1 table. I'm not sure if someone here were able to have such of DB queries involving 2 tables and have it displayed in there Views. 

0 Likes
Highlighted
Honored Contributor.
Honored Contributor.

Re: SQL query in Dashboard or View

Jump to solution

Thank you @lingyanmeng, that was helpful.

Highlighted
Outstanding Contributor.
Outstanding Contributor.

Re: SQL query in Dashboard or View

Jump to solution

@lingyanmeng, @SandyAdmin,

Do you know what could be wrong when I have created a view with relatedObjectMap by using table "request" and "ApprovalLog" and query looks like this " request;approval.status;ApprovalLog;action;ApprovalLog;action="approved" and request;number;ApprovalLog;unique.key;ApprovalLog;operator=operator()" which is ok and it shows all approved requests by some user but it doesen't show unique data.
I have the same request shown almost 700 times...
Best regards,
Dino

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: SQL query in Dashboard or View

Jump to solution

Hi @dfilipovic2,

It's caused by your query introduced Cartesian join, the request approval.status is not unique for the file.

The following query should work for you:

 

request;number;ApprovalLog;unique.key;ApprovalLog;operator=operator() and approval.status="approved"

 

 

Let me know if it works,

Ling-Yan

Highlighted
Outstanding Contributor.
Outstanding Contributor.

Re: SQL query in Dashboard or View

Jump to solution

@lingyanmeng, thank you that worked.
Hmm, i think that I need to play with this type of query a little more so I can get it better.

Best regards,
Dino

0 Likes
Highlighted
Knowledge Partner
Knowledge Partner

Re: SQL query in Dashboard or View

Jump to solution

Hi @lingyanmeng

Based on my tests, I assume this works only for >=9.32 RTE and APP versions.. Any chance to get a specific unload file with this functionality for App 9.30 and RTE >=9.32?

Like some fixes released as unload files with RTE upgrades.

Thanks in advance

Regards,
Breno Abreu

If you feel this was helpful please click the KUDOS! thumb below!
0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Re: SQL query in Dashboard or View

Jump to solution

@BrenoAbreu,

It should work with App 9.30 + RTE 9.32.

Best regards,

Ling-yan

Highlighted
Knowledge Partner
Knowledge Partner

Re: SQL query in Dashboard or View

Jump to solution

Hi @lingyanmeng,

You are right! I was receiving a parser error and understood that it was due some error but in fact is because I was using _ instead of . like we usually do in JS objects.. my mistake... Since it might save some time to others... bellow an example: 

select con.contact.name, con.full.name from contacts con where con.contact.name="BRENO".

Its "con.contact.name"

not "con.contact_name"...

Thanks again 

Regards,
Breno Abreu

If you feel this was helpful please click the KUDOS! thumb below!
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.