Selecting a list of values from a QVD and using in SQL

[Anonymous]

I'm looking to select a list of from a QVD and use the list of values in a SQL IN Clause.

For example if my data was the following:

LOAD * inline [

"ID|"First name"|"Last name"|Initials|"Has cellphone"

1|John|Anderson|JA|Yes

2|Sue|Brown|SB|Yes

3|Mark|Carr|MC |No

4|Peter|Devonshire|PD|No

5|Jane|Elliot|JE|Yes

6|Peter|Franc|PF|Yes ] (delimiter is '|');

I would want a variable that would look like ID_LIST(1,2,3,4,5,6)

Then I would take that variable and use it in my SQL:

SELECT * FROM PEOPLE WHERE ID in($ID_LIST);

I've been trying to use getfieldselections to get the data from my ID and getting an unknown statement error.

Any thoughts?

[Anil_Babu_Samineni]

You can create variable like below

SET Var = 1,2,3,4,5;

Appraoch1:

LOAD * inline [

ID|First name|Last name|Initials|Has cellphone

1|John|Anderson|JA|Yes

2|Sue|Brown|SB|Yes

3|Mark|Carr|MC |No

4|Peter|Devonshire|PD|No

5|Jane|Elliot|JE|Yes

6|Peter|Franc|PF|Yes ] (delimiter is '|') Where Match(ID, $(Var));

In you orient, You can try something like below

Appraoch2:

SELECT * FROM PEOPLE WHERE Match(ID, $(Var));

[Gysbert_Wassenaar · Accepted solution]

Try something like this:

MyTable:

LOAD Concat(ID, ',') as ID_List inline [

"ID|"First name"|"Last name"|Initials|"Has cellphone"

1|John|Anderson|JA|Yes

2|Sue|Brown|SB|Yes

3|Mark|Carr|MC |No

4|Peter|Devonshire|PD|No

5|Jane|Elliot|JE|Yes

6|Peter|Franc|PF|Yes ] (delimiter is '|');

LET vID_List = peek('ID_List',0,'MyTable');

MySQLTable:

SELECT * FROM PEOPLE WHERE ID in( $(vID_List) );

[Anonymous]

How would the table statement change if I was selecting from a QVD as opposed to building the table inline?

[Gysbert_Wassenaar]

Replace inline [ .... ]  with FROM qvdname.qvd (qvd)