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
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
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
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()
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...
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.