cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
antoinesofradir
Level III

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

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

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

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;

  )

  )

);

View solution in original post

7 REPLIES 7
gbu
gbu
Level III

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

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

ms
Super User (Alumni) ms
Super User (Alumni)

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

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;

  )

  )

);

antoinesofradir
Level III

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

Merci Guillaume, ca fonctionne !

Thank tou MS !

pmroz
Super User

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

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;

gbu
gbu
Level III

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

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)

ms
Super User (Alumni) ms
Super User (Alumni)

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

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),"{","(","}",")"," ","");


pmroz
Super User

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

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!