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

[mgoddard · Accepted solution]

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.

[mmodi]

Great answer, thanks mate.

Move closer to where I sit