Community

Subscribe
Highlighted

How do you use the query multi-select function?

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?

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?


Re: How do you use the query multi-select function?

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

Re: How do you use the query multi-select function?

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.


Re: How do you use the query multi-select function?

Thanks for the input. I have removed the qtree selection from the workflow for now in order to test. So now the only multi-select option is the volumes and nothing else is dependent upon that query. However when I try to change the controller, I get yet another error.

Re: How do you use the query multi-select function?

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?

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?

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

Re: How do you use the query multi-select function?

Thanks Jeremy. I did as you said but it seems to hiccup looking for the aggregate, which I don't believe should be necessary for performing this workflow. I have attached my exported workflow to make it easier to see what is going on. It is essentially the workflow from your pirate pack with some tweaks. Thanks in advance for any help you can provide.

Re: How do you use the query multi-select function?

So very close.  You need to search for the volume name and not just declare the name.  The command to NFS Exports requires the Array information based on the Volume reference variable.  Take a look at the screenshots

Jeremy Goodrum, NetApp

The Pirate

Twitter: @virtpirate

Blog: www.virtpirate.com