Problem result sql query

Ignition
1

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] )

2

Welcome to the forum, Theo.

See Wiki - how to post code on this forum. 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.

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.

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

3

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

1 Like
4

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

5

Replace all three queries with this:

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.

6

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 ?

7

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:

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

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

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

    ? AS aColumnName

to inject desired constants into every new row.

8

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

Goodbye