Active IQ Unified Manager Discussions

tableName.<$variableName> works in preview but cannot save

mmodi

Hi Guys,

The following SQL query works in preview but cannot save.

Just wondering if the idea of using variable as a column name within WFA is not feasible ?

===== Variable to select Protocol

${SelectedProtocol} = ENUM (cifs_allowed,nfs_allowed,iscsi_allowed,fcp_allowed)

===== Query to select Vservers based on Protocol

SELECT

    vserver.name as Name,

    admin_state AS Status,

    IF (cifs_allowed=1,'Yes','No') AS CIFS,

    IF (nfs_allowed=1,'Yes','No') AS NFS,

    IF (iscsi_allowed=1,'Yes','No') AS iSCSI,

    IF (fcp_allowed=1,'Yes','No') AS FCP

FROM

    cm_storage.vserver,

    cm_storage.cluster

WHERE

    vserver.cluster_id = cluster.id

    AND cluster.name = '${SelectedCluster}'

    AND vserver.is_repository IS NOT TRUE

    AND

    (

        vserver.type = 'cluster'

        OR vserver.type = 'data'

    )

    AND vserver.${SelectedProtocol} = 1

ORDER BY

    vserver.name ASC

thanks

Modi

1 ACCEPTED SOLUTION

mgoddard

Hi Modi,

What outcome are you trying to achieve?

You could separate the table structure from the input using a CASE statement such as this:

SELECT

    vserver.name as Name,

    admin_state AS Status,

    IF (cifs_allowed=1,'Yes','No') AS CIFS,

    IF (nfs_allowed=1,'Yes','No') AS NFS,

    IF (iscsi_allowed=1,'Yes','No') AS iSCSI,

    IF (fcp_allowed=1,'Yes','No') AS FCP

FROM

    cm_storage.vserver,

    cm_storage.cluster

WHERE

    vserver.cluster_id = cluster.id

    AND cluster.name = '${SelectedCluster}'

    AND vserver.is_repository IS NOT TRUE

    AND

    (

        vserver.type = 'cluster'

        OR vserver.type = 'data'

    )

    AND CASE

        WHEN 'fcp' = '${protocol}' THEN vserver.fcp_allowed = 1

        WHEN 'iscsi' = '${protocol}' THEN vserver.iscsi_allowed = 1

        WHEN 'cifs' = '${protocol}' THEN vserver.cifs_allowed = 1

        WHEN 'nfs' = '${protocol}' THEN vserver.nfs_allowed = 1

    END

ORDER BY

    vserver.name ASC

Kind Regards,

Michael Goddard.

View solution in original post

2 REPLIES 2

mgoddard

Hi Modi,

What outcome are you trying to achieve?

You could separate the table structure from the input using a CASE statement such as this:

SELECT

    vserver.name as Name,

    admin_state AS Status,

    IF (cifs_allowed=1,'Yes','No') AS CIFS,

    IF (nfs_allowed=1,'Yes','No') AS NFS,

    IF (iscsi_allowed=1,'Yes','No') AS iSCSI,

    IF (fcp_allowed=1,'Yes','No') AS FCP

FROM

    cm_storage.vserver,

    cm_storage.cluster

WHERE

    vserver.cluster_id = cluster.id

    AND cluster.name = '${SelectedCluster}'

    AND vserver.is_repository IS NOT TRUE

    AND

    (

        vserver.type = 'cluster'

        OR vserver.type = 'data'

    )

    AND CASE

        WHEN 'fcp' = '${protocol}' THEN vserver.fcp_allowed = 1

        WHEN 'iscsi' = '${protocol}' THEN vserver.iscsi_allowed = 1

        WHEN 'cifs' = '${protocol}' THEN vserver.cifs_allowed = 1

        WHEN 'nfs' = '${protocol}' THEN vserver.nfs_allowed = 1

    END

ORDER BY

    vserver.name ASC

Kind Regards,

Michael Goddard.

View solution in original post

mmodi

Great answer, thanks mate.

Move closer to where I sit

Announcements
NetApp on Discord Image

We're on Discord, are you?

Live Chat, Watch Parties, and More!

Explore Banner

Meet Explore, NetApp’s digital sales platform

Engage digitally throughout the sales process, from product discovery to configuration, and handle all your post-purchase needs.

NetApp Insights to Action
I2A Banner
Public