User Input Query Multi-Select Error

[SeifriedJ]

I'm trying to gather multiple values from a user input query selection. Here is an example selection:

And here is the query behind it:

SELECT
    logical_interface.name AS lif,
    logical_interface.address AS address,
    logical_interface.protocols AS protocols,
    broadcast_domain.name AS test_broadcast_domain  
FROM
    cm_storage.cluster cluster,
    cm_storage.vserver vserver,
    cm_storage.logical_interface logical_interface,
    cm_storage.broadcast_domain broadcast_domain,
    cm_storage.ipspace ipspace  
WHERE
    cluster.name = '${ClusterName}'          
    AND vserver.name = '${SourceVserverName}'          
    AND vserver.cluster_id = cluster.id          
    AND logical_interface.vserver_id = vserver.id          
    AND broadcast_domain.ipspace_id = ipspace.id          
    AND ipspace.cluster_id = cluster.id          
    AND ipspace.name != 'Cluster'          
    AND ipspace.name NOT IN (
        SELECT
            ipspace.name                  
        FROM
            cm_storage.vserver vserver,
            cm_storage.ipspace ipspace                  
        WHERE
            vserver.name = '${SourceVserverName}'                          
            AND vserver.ipspace_id = ipspace.id          
    ) 

Judging from the variable values this gives me exactly what I want - I can split the string on '~' and pull out the values I need. The problem is I'm getting an error in planning:

"The value smv1_lif1~10.3.240.200~nfs,cifs~SE-Lab240-Test,smv1_lif2~10.3.240.201~nfs,cifs~drtest for input $SourceVserverNetworkInterfaces has to be within [smv1_lif2~10.3.240.201~nfs,cifs~SE-Lab240-Test, smv1_lif2~10.3.240.201~nfs,cifs~drtest, smv1_lif1~10.3.240.200~nfs,cifs~SE-Lab240-Test, smv1_lif1~10.3.240.200~nfs,cifs~SE-Lab241-Test, smv1_lif1~10.3.240.200~nfs,cifs~drtest, smv1_lif2~10.3.240.201~nfs,cifs~SE-Lab241-Test]"

The values are included in the specified set. I also tried just selecting one value, but the results are the same. What am I doing wrong here?

WFA v4.2.0.0.1 build 4759965

[SeifriedJ]

I thought I had a workaround by creating a table and using queries within, but unfortuantely I ran into an issue with that too. So now I have two potential ways I could do this that are somewhat elegant and neither work due to WFA bugs.

[moep]

To get a single column from the result you can use the SplitByDelimiter function:

splitByDelimiter($SourceVserverNetworkInterfaces,"~",0)

I guess using multiple rows as an input for a command will be difficult. Usually it is done with iterating through the results with row repetition:

[cbauernf]

The problem is that one of the keys (protocols) has commas in its data, and WFA cannot distinguish between the commas in the data and the commas that separate its records.  A workaround would be to replace the commas in the protocols data with a safe character (like ":") in the SQL SELECT statement, and replace them back before passing them to a command step.

By the way, there are certified functions (getValueAt, getValueAt2D and getColumns) to deal with multi-query variables.  The are not functionally different from splitByDelimiter, but since they are made just for this purpose, might as well use them.

Best,

Christian