topic Re: JSL - Multiselect from a list to insert in a SQL request in Discussions

JSL - Multiselect from a list to insert in a SQL request

antoinesofradir — Fri, 26 Apr 2013 07:57:21 GMT

Hello, I have a little problem,

I created a list then a list box to can select differents values from this list, I would like to insert my selection of values in a SQL request.

But it doesn't work, here is my script :

choice = {"rouge","vert","bleu", "jaune"};
recall_flag = 0;

          NW=New Window("Test",
                     H List Box( Text Box( "choose colors" ), y = List Box( choice ) ),
                     V List Box(
                          Button Box( "OK",
                                       choice = (y << getselected);
                                       ::recall_flag = 1;
                                       NW << Close Window;
                                      )
                                   )
                               );

select = "SELECT ...
FROM ...

WHERE Colors IN '" || choice || "' " ;

Have you any idea of the problem ?

Thank you for your answers !

Re: JSL - Multiselect from a list to insert in a SQL request

gbu — Fri, 26 Apr 2013 09:04:36 GMT

Salut Antoine,

You can do like this :

==========================================

choice = {"rouge","vert","bleu", "jaune"};

recall_flag = 0;

NW=New Window("Test",

H List Box( Text Box( "choose colors" ), y = List Box( choice ) ),

V List Box(

Button Box( "OK",

choice = (y << getselected);

::recall_flag = 1;

NW << Close Window;

)

);

select = "SELECT ...

FROM ...

WHERE Colors IN 'strchoice' " ;

/* Step1 : Create a string from the list using loop - Creation d'une chaine de carcatère au format attendu en bouclant sur le nombre de valeurs */

string_color = "('" || choice[1] || "'";

For( i = 2, i <= Nitems(choice), i++,

string_color = string_color || ",'" || choice || "'"

);

string_color = string_color || ")";

/* Step2 : Replace the stingchoice by the value of string_color */

select = substitute(select,"'strchoice'",string_color);

==========================================

Cordialement,

Guillaume

Re: JSL - Multiselect from a list to insert in a SQL request

ms — Fri, 26 Apr 2013 09:28:20 GMT

Apart from turning the list as into a string, which gbu solves nicely above, there is also be the problem of select beeing evaluated before the selection has been made unless the window is modal or the building of the select string is moved into the OK-button script.

Modal window example:

choice = {"rouge", "vert", "bleu", "jaune"};

recall_flag = 0;

NW = New Window( "Test",<<modal,

H List Box( Text Box( "choose colors" ), y = List Box( choice ) ),

V List Box(

Button Box( "OK",

choice = (y << getselected);

::recall_flag = 1;

)

);

choices = "'" || choice[1] || "'"; // First selected item

// Add more items if more than one

For( i = 2, i <= N Items( choice ), i++,

choices = choices || ", '" || choice[i] || "'"

);

select = "SELECT ...FROM ...WHERE Colors IN (" || choices || ")";

Non-modal example:

choice = {"rouge", "vert", "bleu", "jaune"};

recall_flag = 0;

NW = New Window( "Test", //<<modal,

H List Box( Text Box( "choose colors" ), y = List Box( choice ) ),

V List Box(

Button Box( "OK",

choice = (y << getselected);

::recall_flag = 1;

choices = "'" || choice[1] || "'";

For( i = 2, i <= N Items( choice ), i++,

choices = choices || ", '" || choice[i] || "'"

);

select = "SELECT ...FROM ...WHERE Colors IN (" || choices || ")";

NW << Close Window;

)

);

Re: JSL - Multiselect from a list to insert in a SQL request

antoinesofradir — Fri, 26 Apr 2013 11:20:37 GMT

Merci Guillaume, ca fonctionne !

Thank tou MS !

Re: JSL - Multiselect from a list to insert in a SQL request

pmroz — Fri, 26 Apr 2013 12:31:54 GMT

There's a nifty function called concat items which can be used to create your in list with a one-liner:

choice = {"rouge", "vert", "bleu", "jaune"};

recall_flag = 0;

NW = New Window( "Test",

<<modal,

H List Box( Text Box( "choose colors" ), y = List Box( choice ) ),

V List Box(

Button Box( "OK",

choice = (y << getselected);

::recall_flag = 1;

)

);

choices_in_list = "('" || concat items(choice, "', '") || "')";

select = "SELECT ...FROM ...WHERE Colors IN " || choices_in_list;

Re: JSL - Multiselect from a list to insert in a SQL request

gbu — Sun, 28 Apr 2013 14:43:55 GMT

Hello,

Yes, concat function works too but only for string and not for number. True ?

Do you have any tips for a numeric list ?

For example : instead of having choice = {"rouge", "vert", "bleu", "jaune"}; we have numerics values like choice = {12,156,13,25};

and at the end we wants to have :

select = "SELECT ...FROM ...WHERE ID IN (12,156,13,25)

Re: JSL - Multiselect from a list to insert in a SQL request

ms — Sun, 28 Apr 2013 20:41:08 GMT

In a Listbox context as in the original example, the items must be strings to be selectable in the listbox. That is, numbers need to be converted to strings in advance anyway.

But assume the list is given as choice = {12,156,13,25}, then the SQL-string could be concatenated like this:

choice= {12,156,13,25};

select = "SELECT ...FROM ...WHERE ID IN " || substitute(char(choice),"{","(","}",")"," ","");

Re: JSL - Multiselect from a list to insert in a SQL request

pmroz — Mon, 29 Apr 2013 13:20:40 GMT

The concat items function only takes strings as arguments, so that won't work for a numeric list.

I didn't know that you could keep adding substitution arguments after the first two. Thanks for showing me that trick MS!