# Using 2 querys combined with PIVOT in perspective table

**URL:** <https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777>\
**Category:** Ignition\
**Tags:** vision, perspective\
**Created:** [May 23, 2024, 9:03am UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777 "2024-05-23T09:03:58Z")\
**Posts on this page:** 16\
**Page:** 1

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 23, 2024, 9:03am UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/1 "2024-05-23T09:03:58Z")

</div>

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

```python
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/d/2d3d5b86007b61ce59065df201b970370e219687.png)

How could I replicate this structure in Perspective?

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 23, 2024, 9:08am UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/2 "2024-05-23T09:08:31Z")

</div>

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

> **Indented SQL query**
>
> ```python
> 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
> 
> ```

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 23, 2024, 9:19am UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/3 "2024-05-23T09:19:37Z")

</div>

I have 2 querys,

one in A

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

```

and the one in B

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

```

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/b/5b82a11f631981bdb4ca46e6b1bc0f104dac68e5.png)

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

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 23, 2024, 9:26am UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/4 "2024-05-23T09:26:34Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 23, 2024, 9:31am UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/5 "2024-05-23T09:31:04Z")

</div>

> [@Alejandro\_Alaco](#):
>
> `({Root Container.HazardList})`

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

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 23, 2024, 9:52am UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/6 "2024-05-23T09:52:13Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 23, 2024, 9:55am UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/7 "2024-05-23T09:55:50Z")

</div>

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

({this.custom.hazardlist }) ??

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

```

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [May 23, 2024, 11:18am UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/8 "2024-05-23T11:18:07Z")

</div>

Use a named query for perspective.

---

<div class="post-metadata">

**Author:** ![msteele](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/msteele/32/77889_2.png) [@msteele](https://forum.inductiveautomation.com/u/msteele)\
**Post date:** [May 23, 2024, 12:36pm UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/9 "2024-05-23T12:36:49Z")

</div>

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

> **[Dataset Functions - Integration Toolkit for Ignition](http://automation-pros.com/toolkit/doc/datasets.html#leftjoin)**
>
> A variety of expression functions and scripting functions generally useful in Ignition.

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 23, 2024, 3:59pm UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/10 "2024-05-23T15:59:10Z")

</div>

Hello @msteele

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

({this.custom.property})

??

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 23, 2024, 4:01pm UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/11 "2024-05-23T16:01:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 23, 2024, 4:27pm UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/12 "2024-05-23T16:27:29Z")

</div>

```python
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...

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/2/321a2640408d12a31d325faba43b97140a8707ec.png)

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

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/c/8c0e2284d6b3d5bd5e596c3faef4711227d7d935.png)

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

is correct.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 23, 2024, 4:32pm UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/13 "2024-05-23T16:32:14Z")

</div>

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.

> **[Query Bindings in Perspective | Ignition User Manual](https://docs.inductiveautomation.com/docs/8.1/ignition-modules/perspective/working-with-perspective-components/bindings-in-perspective/query-bindings-in-perspective)**
>
> The Query Binding allows you to pull data from the database using a named query. In Perspective, the Query Binding requires the use of Named Queries. You can't type a query here from this interface, a Named Query must already exist. You can also add...

There is a video link on that page.

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 23, 2024, 4:33pm UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/14 "2024-05-23T16:33:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [May 23, 2024, 4:43pm UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/15 "2024-05-23T16:43:57Z")

</div>

![PrismaCompany](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/6/d6c8609e0686eba6a45653f7ee2b578bbcc920ec.png)

---

<div class="post-metadata">

**Author:** ![Alejandro\_Alaco](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/alejandro_alaco/32/59849_2.png) [@Alejandro\_Alaco](https://forum.inductiveautomation.com/u/Alejandro_Alaco)\
**Post date:** [May 23, 2024, 4:46pm UTC](https://forum.inductiveautomation.com/t/using-2-querys-combined-with-pivot-in-perspective-table/90777/16 "2024-05-23T16:46:29Z")

</div>

Thanks a lot!

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/2/4/2475f5411e304b6d7f2044a547cf846da7b79caf.png)