# Problem result sql query

**URL:** <https://forum.inductiveautomation.com/t/problem-result-sql-query/86345>\
**Category:** Ignition\
**Created:** [February 26, 2024, 8:52am UTC](https://forum.inductiveautomation.com/t/problem-result-sql-query/86345 "2024-02-26T08:52:39Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Theo\_Ceccaldi](https://avatars.discourse-cdn.com/v4/letter/t/7cd45c/32.png) [@Theo\_Ceccaldi](https://forum.inductiveautomation.com/u/Theo_Ceccaldi)\
**Post date:** [February 26, 2024, 8:52am UTC](https://forum.inductiveautomation.com/t/problem-result-sql-query/86345/1 "2024-02-26T08:52:39Z")

</div>

Hello,

I'm new to the forum. I'm having a problem with the return of a SQL query. I'm looking to get the result of a SELECT query and reuse it in an SELECT query. I've tried a lot of different scripts but I can't do it. Here's my script.I'm looking for a way to convert my ESSAI3 result into a value that the INSERT query could include  
I use MSSQL in serve :

CodeMat = system.tag.readBlocking(['[default]Cde\_Matiere'])[0].value

essai =system.dataset.toDataSet(system.db.runPrepQuery("SELECT DISTINCT CodeStockage FROM tStockageInfo WHERE CodeMatiere = (?)", [CodeMat] ))

essai2 = system.dataset.toDataSet(system.db.runQuery("SELECT DISTINCT CodeStockage FROM tStockage WHERE CodeGroupe=1 AND NOT CodeStockage IN (Select CodeStockage FROM tStockageInfo) "))

essai3 = system.dataset.appendDataset(essai2,essai)

Here is my return script but the value is not good for ' essai3 ' :

system.db.runPrepQuery("SELECT DISTINCT LibelleL1 FROM tStockage WHERE CodeStockage IN ((?))", [essai3] )

---

<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:** [February 26, 2024, 11:13am UTC](https://forum.inductiveautomation.com/t/problem-result-sql-query/86345/2 "2024-02-26T11:13:29Z")

</div>

Welcome to the forum, Theo.

See [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831). There's a pencil icon below the post so you can edit. You can also use Python's triple quotes to wrap your SQL for readability.

```auto
CodeMat = system.tag.readBlocking(['[default]Cde_Matiere'])[0].value

essai =system.dataset.toDataSet(system.db.runPrepQuery("""
    SELECT DISTINCT CodeStockage 
    FROM tStockageInfo 
    WHERE CodeMatiere = (?)""", 
    [CodeMat])
)

essai2 = system.dataset.toDataSet(system.db.runQuery("""
    SELECT DISTINCT CodeStockage 
    FROM tStockage 
    WHERE CodeGroupe=1 
      AND NOT CodeStockage IN (
        SELECT CodeStockage FROM tStockageInfo
      )"""
    )
)

essai3 = system.dataset.appendDataset(essai2, essai)

```

The code below won't work.

```auto
system.db.runPrepQuery("""
    SELECT DISTINCT LibelleL1 
    FROM tStockage 
    WHERE CodeStockage IN ((?))", 
    [essai3] 
)

```

The prepared query can't take a dataset or even a list.  
I think the best solution would be to convert the dataset to a comma separated string using a join function. Then pass that into the IN(\_).

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 26, 2024, 1:10pm UTC](https://forum.inductiveautomation.com/t/problem-result-sql-query/86345/3 "2024-02-26T13:10:55Z")

</div>

If this is all in one database, then you should be using a JOIN to express the chained condition.

---

<div class="post-metadata">

**Author:** ![Theo\_Ceccaldi](https://avatars.discourse-cdn.com/v4/letter/t/7cd45c/32.png) [@Theo\_Ceccaldi](https://forum.inductiveautomation.com/u/Theo_Ceccaldi)\
**Post date:** [February 26, 2024, 1:23pm UTC](https://forum.inductiveautomation.com/t/problem-result-sql-query/86345/4 "2024-02-26T13:23:45Z")

</div>

I thank you for your response and I understood about the code publication. can you tell me the code to do the join? I can't find..

Thank you in advance

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 26, 2024, 1:53pm UTC](https://forum.inductiveautomation.com/t/problem-result-sql-query/86345/5 "2024-02-26T13:53:12Z")

</div>

Replace all three queries with this:

```auto
SELECT DISTINCT s.LibelleL1
FROM tStockage s
LEFT JOIN tStockageInfo si
  ON s.CodeStockage = si.CodeStockage
WHERE si.CodeMatiere = ? OR (s.CodeGroupe = 1 AND si.CodeStockage IS NULL)

```

Pass just `CodeMat` as the one parameter.

---

<div class="post-metadata">

**Author:** ![Theo\_Ceccaldi](https://avatars.discourse-cdn.com/v4/letter/t/7cd45c/32.png) [@Theo\_Ceccaldi](https://forum.inductiveautomation.com/u/Theo_Ceccaldi)\
**Post date:** [February 26, 2024, 5:32pm UTC](https://forum.inductiveautomation.com/t/problem-result-sql-query/86345/6 "2024-02-26T17:32:23Z")

</div>

OK, thanks a lot.  
however if I want to retrieve a dataset from a SELECT query, how can I convert it so that in my INSERT query the language corresponds ?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 26, 2024, 5:54pm UTC](https://forum.inductiveautomation.com/t/problem-result-sql-query/86345/7 "2024-02-26T17:54:30Z")

</div>

I know of no way to do that with MS SQL Server. If you adopt a competent database technology, you can use SQL UNNEST() and similar technologies:

> [@Taking a Dataset and Inserting it to a SQL temp table](https://forum.inductiveautomation.com/t/taking-a-dataset-and-inserting-it-to-a-sql-temp-table/83852/4):
>
> If you were using a competent database (the following is PostgreSQL), you can pass java arrays as arguments, and unnest() them in a FROM clause. For an example, I created a table in my psql forum playground called phrases, to be used in a join. When queried by itself, you get this dataset: "#NAMES" "prefix","suffix" "#TYPES" "str","I" "#ROWS","4" "Hello","1" "Good Bye","2" "Welcome to","3" "Whoa!","4" To go with that, in a custom property named lookups, I created the following unbound datase…

However, most people would combine the SELECT and the INSERT in a single statement, using this general syntax:

```auto
INSERT INTO someTable (someColumns....)
SELECT matchingColumns....
FROM ....

```

In the `matchingColumns` list of expressions, you might include expressions like:

```auto
    ? AS aColumnName

```

to inject desired constants into every new row.

---

<div class="post-metadata">

**Author:** ![Theo\_Ceccaldi](https://avatars.discourse-cdn.com/v4/letter/t/7cd45c/32.png) [@Theo\_Ceccaldi](https://forum.inductiveautomation.com/u/Theo_Ceccaldi)\
**Post date:** [February 26, 2024, 6:36pm UTC](https://forum.inductiveautomation.com/t/problem-result-sql-query/86345/8 "2024-02-26T18:36:54Z")

</div>

ok, thank you for your valuable help. I'm going to try all of that.

Goodbye