9 Replies Latest reply: Oct 10, 2013 9:09 AM by goodrum RSS

How do you use the query multi-select function?

TABER_BRANDEN
Currently Being Moderated

How do you actually go about using the SQL multi-select function? Whenever I change a query to multi-select, it either throws an error or just locks up the whole program. Is there something special you have to write in the SQL query to be able to do multi-select? For example, I'm wanting to select multiple volumes at a time to export to the same server.

 

Thanks in advance.

  • Re: How do you use the query multi-select function?
    sinhaa
    Currently Being Moderated

    Branden,

            Using Multi-select query for User Inputs doesn't need anything special. Writing the query as you normally would for type 'query' should be fine.

     

     

    @ Whenever I change a query to multi-select, it either throws an error or just locks up the whole program.

    -------

    Can you please post the screen shot of the exact error message that it throws? What time do you get the error? When you press "Test" or "OK" or at the Workflow(WF) execution/preview?

     

     

    multi-select.png

    • Re: How do you use the query multi-select function?
      TABER_BRANDEN
      Currently Being Moderated

      When I hit preview on the workflow I receive the following message box.

      multiselect-error.PNG

      • Re: How do you use the query multi-select function?
        sinhaa
        Currently Being Moderated

        Branden,

               Your issue was reproduced and this will fall in as a User Input design error. Attaching my workflow to let you  see it more clearly. If you read the error message, it gives the message right.

         

        With regards to your workflow, you are having one variable name (Volume Name) as a multi-select query i.e. more than one volumes can get picked. Then you are having another variable of type query (Qtree Name ) which selects one qtree but who is having reference to volumeName which are muliple by virtue of multi-select.Remove this referance i.e. have query instead of multi-select query for volumeName. It may need some workflow design change too.

         

        This design error couldn't have been reached if you follow the SQL query auto-complete feature. WFA has intelligence to identify this before hand and it will not display the reference to a multi-select object at all. This SQL auto-complete in WFA2.1 is quite a good feature.

         

        multi-select_error.png

         


  • Re: How do you use the query multi-select function?
    goodrum
    Currently Being Moderated

    The Multi-Select query type creates a mulit-dimensional array variable type depending on the number of columns that you have.  For example, if I have a simple single column query such as

    --------------------------------------------

    VolumeNames

    --------------------------------------------

    Jeremy

    Pirate

    Goodrum

    WFA

    --------------------------------------------

    Then and I select Jeremy and Goodrum then I end up with a one dimensional array string of Jeremy,Goodrum

     

    On the other hand, if I create a more complex query with multiple column results.  Say for example, I return the nodeName and portNames on a cDOT system

    --------------------------------------------

    Node Name | Port Name

    --------------------------------------------

    Node01 | a0a

    Node02 | a0a

    Node01 | e0a

    Node02 | e0b

    --------------------------------------------

    Then I select Node01 and Node02 ports a0a, I will end up with a multi-dimmensional array (an array containing an array) - Node01~a0a,Node02~a0a

     

     

    Ok, so why is this important?  I can use the functions listed below to manipulate the data.  In your use case, you want to create a Repeat Row based on the number of volumes (getSize) selected and then apply the new export to that specific Volume (getValueAt($VolumeList, Index)).  The challenge here is that you need to ensure that no other inputs depend on that variable for further limiting.  IE, don't try to select multiple volumes in a single input and then use that to filter the available qtrees.

     

    getSize()

    --------------------------------------------

    This function returns the size of a 1 or 2 dimensional array.

    For a 1-dimensional array it returns the number of elements in the array, for example getSize("a,b,c") will return 3.

    For a 2-dimensional array it returns the number of rows in the array,  for example getSize("a~1,b~2,c~3,d~4") will return 4

    --------------------------------------------

     

     

    getValueAt()

    --------------------------------------------

    This function accepts a 1 dimensional array as a comma separated String and returns the element at the location specified by the index argument

    arrayVar - String - String representation of a 1-dimensional array

    index - int - index starting from 1 of the element to be returned

    For example: getValueAt("a,b,c,d,e,f",3) would return c.

    --------------------------------------------

     

    getValueAt2D()

    --------------------------------------------

    This function accepts a 2-dimensional array and returns the element at the location specified by the rowIndex and colIndex arguments

    arrayVar - String - String representation of a 2-dimensional array

    rowIndex - int - row number starting from 1 of the element to be returned

    colIndex - int - column number starting from 1 of the element to be returned

    For example: getValueAt2D("a~1,b~2",2,1) would return b.

    --------------------------------------------

     

     

    getValueFrom2DByRowKey() -

    --------------------------------------------

    This function accepts a 2-dimensional array and returns a column value matching the given row key and column index. First column in each row is considered as the key for the row.

    arrayVar - String - String representation of a 2-dimensional array.

    rowKey - String - Row matching the first column as the given rowKey will be selected.

    colIndex - String - From the selected row, the value from the given column index will be returned.

     

    For example: getValueFrom2DByRowKey("a~5~P,b~6~Q", "b", 3) would return "Q".

    --------------------------------------------

     

     

     

    Jeremy Goodrum, NetApp

    The Pirate

    Twitter: @virtpirate

    Blog: www.virtpirate.com

    • Re: How do you use the query multi-select function?
      TABER_BRANDEN
      Currently Being Moderated

      Fantastic information. Thank you! I think I am on the right track, but when I try to use No-Op Storage to procure the volume names it doesn't seem to work, where-as it did before without using multi-select queries.

       

      In No-Op storage I have it configured such that I am filtering volumes by name in array. The Volume Name is set to $volumeName and array IP or Name is chosenArray.ip.

       

      Do I need to filter some other way since I am selecting multiple volume names? Should I move away from No-Op storage and find the volumes on the actual export commands themselves?

       

      Thanks in advance.

      • Re: How do you use the query multi-select function?
        goodrum
        Currently Being Moderated

        So remember that the the variable $volumeName is no longer a string value but instead is a series of strings in an Array.  You need to do the following:

        1. Add a repeat row option based on number and set the count to getSize($volumeName) and the Index variable to volumeIndex
        2. Now in the row, use a No-Op to find the volume based on the value in the array [getValueAt($volumeName,volumeIndex)] and the array as chosenArray.ip (Name this Volume Variable something like VolumeMember)
        3. Modify the exports based on the previously defined variable(VolumeMember)

         

        This should get you rolling

         

         

         

        Jeremy Goodrum, NetApp

        The Pirate

        Twitter: @virtpirate

        Blog: www.virtpirate.com

More Like This

  • Retrieving data ...