Separate list results of persistent classes from SQL
Hello everyone,
We are trying out lists inside persistent classes. Because we may need to use them in a very close future.
So far, I managed to create a class that has a list property which has an indice.
Here is the class in question:
Class User.TestList.Data.Titre Extends (%Persistent, %Populate) { Property numTitre As %Integer; Property millesime As %Integer; Property codeProduit As %String; /// Old field which will be replaced by the next one Property numDossierMER As %Integer; /// New field which is a list Property numDossiersMER As list Of %Integer; Index titreIdx On (numTitre, millesime, codeProduit) [ PrimaryKey ]; /// Old index Index numDossierMERIdx On numDossierMER; /// New index Index numDossiersMERIdx On numDossiersMER(ELEMENTS); }
I also managed to query the table depending on values of the list with the following query structure:
SELECT ID, codeProduit, millesime, numDossierMER, numDossiersMER, numTitre FROM User_TestList_Data.Titre WHERE FOR SOME %ELEMENT(numDossiersMER) (%VALUE IN (24, 662703520))
Altough, one question remained unanswered so far:
How can I fetch results from the table from SQL and split all elements of the list into separate lines ?
For example, let's say I only have two lines in my table.
If I query the table with a simple SELECT * FROM User.TestList.Data.Titre. The results will be as follows:
numTitre | millesime | codeProduit | numDossierMER | numDossiersMER (list) |
---|---|---|---|---|
1 | 2021 | X | 1 | 1 2 3 |
2 | 2021 | X | 4 | 4 5 6 |
How can I get the results to be formatted like this instead ?
numTitre | millesime | codeProduit | numDossierMER | numDossiersMER (list) |
1 | 2021 | X | 1 | 1 |
1 | 2021 | X | 1 | 2 |
1 | 2021 | X | 1 | 3 |
2 | 2021 | X | 4 | 4 |
2 | 2021 | X | 4 | 5 |
2 | 2021 | X | 4 | 6 |
Thank you
Lucas
Product version: Ensemble 2017.1
$ZV: Cache for Windows (x86-64) 2017.2.1 (Build 801U) Wed Dec 6 2017 09:07:51 EST