Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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) );
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));
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) );
How would the table statement change if I was selecting from a QVD as opposed to building the table inline?
Replace inline [ .... ] with FROM qvdname.qvd (qvd)