Using 2 querys combined with PIVOT in perspective table

Hello!

I´m replicating a Ignition Vision to Ignition Perspective project.

In the original Vision project we are using PIVOT to get data from another query into our main query

the main query is in tblSummary

and uses PIVOT to get data from

Root Container dB

SELECT * FROM
(
SELECT WR.ASSET AS SEDE,
(SELECT HAZARD FROM WORKREQUESTHAZARD WHERE COMPANY=WR.COMPANY A
 ...

PIVOT (MAX(HAZARDVALUE) FOR HAZARD IN ({Root Container.HazardList}))
ORDER BY SEDE
 

image

How could I replicate this structure in Perspective?

What is the problem? Why does the Vision query not work in Perspective?

Indented SQL query
SELECT * 
FROM (
    SELECT 
        WR.ASSET AS SEDE,
        (
            SELECT HAZARD 
            FROM WORKREQUESTHAZARD 
            WHERE COMPANY = WR.COMPANY A
 ...

    PIVOT (MAX(HAZARDVALUE) 
    FOR HAZARD IN ({Root Container.HazardList})
)
ORDER BY SEDE
1 Like

I have 2 querys,

one in A

SELECT * FROM
(
SELECT WR.ASSET AS SEDE,
...
PIVOT (MAX(HAZARDVALUE) FOR HAZARD IN ({Root Container.HazardList}))
ORDER BY SEDE
 

and the one in B

SELECT ''''||LISTAGG(HAZARD, ''',''') WITHIN GROUP (ORDER BY HAZARD)||'''' 
FROM HAZARD WHERE COMPANY='{Root Container.PrismCompany}'

image

I don´t know how to replicate te structure with 2 db in perspective.
Maybe should I use 2 custom properties?

Sorry, I still don't understand the problem.

You need to make it easy for people to help. Format your SQL properly with line breaks and indentation.

1 Like

The issue is how to replicate this part of the query into the perspective table,
If I add a custom property with the second db, how I call it from my query, similar to what is done:
({Root Container.HazardList})

because I don´t know where to put the query that is in B

Then, yes, create a custom property to store result B. You can then use that as parameter in a named query or as a value in a scripted query.

1 Like

If my 2nd query is in custom property: "this.custom.hazardlist" I call it in my query with

({this.custom.hazardlist }) ??

SELECT ''''||LISTAGG(HAZARD, ''',''') WITHIN GROUP (ORDER BY HAZARD)||'''' 
FROM HAZARD WHERE COMPANY='{this.custom.hazardlist}'

Use a named query for perspective.

1 Like

If you already have the two datasets queried into Perspective, you may be able to pivot/join them without having to do any more calls to the SQL database by using @pturmel's Integration Tooklit module with leftJoin() or pseudo-SQL in view()

1 Like

Hello @msteele

How to do a reference to a custom propertie value in perspective in the same table?

({this.custom.property})

??

Show us a screengrab of your query bindings. (Post code too if you think we might want to edit it.)

1 Like
SELECT * FROM (SELECT LEVEL RIESGO,NULL AS TOTAL FROM DUAL CONNECT BY LEVEL < 7) SEQ LEFT JOIN (
SELECT HAZARDVALUE,HAZARD 
FROM WORKREQUESTHAZARD WH INNER JOIN WORKREQUEST WR ON WH.WORKREQUEST=WR.WORKREQUEST AND WH.COMPANY=WR.COMPANY
WHERE WH.COMPANY='({this.custom.PrismaCompany})' AND WR.REQUESTER='PRL' AND WR.WORKREQUESTSTATE <> '30') HAZ ON SEQ.RIESGO=HAZ.HAZARDVALUE
PIVOT (COUNT(HAZARDVALUE) FOR HAZARD IN ('RI_PRL0001','RI_PRL0002','RI_PRL0003','RI_PRL0004','RI_PRL0005','RI_PRL0006','RI_PRL0007','RI_PRL0008','RI_PRL0009','RI_PRL0010','RI_PRL0011','RI_PRL0012','RI_PRL0013','RI_PRL0014','RI_PRL0015','RI_PRL0016','RI_PRL0017','RI_PRL0018','RI_PRL0019'))
ORDER BY RIESGO
 

for example in this case I have a named query that is binded to my table...

Then I want get the value ({this.custom.PrismaCompany}) from a custom property

image

would like to know if this way to write it in the query : ({this.custom.PrismaCompany})

is correct.

You didn't show where that SQL query is used. Is it in a named query? If so you need to add parameters to the query. Then they will show up in the Query Binding.

There is a video link on that page.

1 Like

The named query is used to populte a table, the custom property is within the table.

2 Likes

Thanks a lot!

image

1 Like