topic Re: tableName.<$variableName> works in preview but cannot save in Active IQ Unified Manager Discussions

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

mmodi — Thu, 05 Jun 2025 05:53:01 GMT

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

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

mgoddard — Fri, 27 Sep 2013 01:20:27 GMT

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.

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

mmodi — Fri, 27 Sep 2013 01:33:38 GMT

Great answer, thanks mate.

Move closer to where I sit