Effective December 3, NetApp adopts Microsoft’s Business-to-Customer (B2C) identity management to simplify and provide secure access to NetApp resources.
For accounts that did not pre-register (prior to Dec 3), access to your NetApp data may take up to 1 hour as your legacy NSS ID is synchronized to the new B2C identity.
To learn more, read the FAQ and watch the video.
Need assistance? Complete this form and select “Registration Issue” as the Feedback Category.

Active IQ Unified Manager Discussions

Can a user input query in WFA have IF THEN ELSE

AdaikkappanArumugam

Below is my query and when I save it WFA throws the following error.

Illegal user input query 'IF '${vfilerName}' != 'vfiler0' THEN
SELECT
vol.name,
array.ip AS 'array.ip'
FROM
storage.vfiler AS vfl,
storage.volume AS vol,
storage.array AS array
WHERE
vol.vfiler_id = vfl.id...

 

HERE is my actual query. IS an  IF THEN ELSE supported or I am doing something wrong....

 

IF '${vfilerName}' != 'vfiler0' THEN
SELECT
vol.name,
array.ip AS 'array.ip'
FROM
storage.vfiler AS vfl,
storage.volume AS vol,
storage.array AS array
WHERE
vol.vfiler_id = vfl.id
AND vfl.name = '${vfilerName}'
AND vol.array_id = array.id
AND (
array.ip = '${arrayName}'
OR array.name = '${arrayName}'
)
AND vol.name = '${volName}'
ORDER BY
vol.name DESC

ELSE
SELECT
vol.name AS 'name',
array.ip AS 'array.ip',
array.name AS 'array.name'
FROM
storage.volume AS vol
JOIN
storage.array AS array
ON vol.array_id = array.id
WHERE
(
array.ip = '${arrayName}'
OR array.name = '${arrayName}'
)
END IF

4 REPLIES 4

FelipeMafra

Yes you can use IF, but you cannot use Select ..... IF.... Select... ELSE.... SELECT... . It's not a valid SQL statement. If you want many SELECT statements you should use UNION but most of the time you won't need it.

 

I've create a query that uses and it may help you. Take a look at the way I use IF.

 

SELECT
    export_policy.name 'Nome das políticas',
    GROUP_CONCAT(if(rw_rule='sys'
    OR rw_rule='any',
    export_rule.clientmatch,
    null)) 'REGRAS RW',
    GROUP_CONCAT(if((ro_rule='sys'
    OR ro_rule='any')
    AND rw_rule='none',
    export_rule.clientmatch,
    null)) 'Regras RO'
FROM
    cm_storage.export_policy
INNER JOIN
    cm_storage.export_rule
        ON export_policy.id=export_rule.policy_id
JOIN
    cm_storage.vserver
        ON export_policy.vserver_id=vserver.id
WHERE
    vserver.name='${VserverName}'
    AND export_policy.name!='default'
GROUP BY
    export_policy.name

 

You can have some reference of IF usage here: https://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_if

rkiran

I think you cannot use SELECT inside IF-ELSE stmt like that in SQL. Instead, you can use UNION like shown below:

 

(
SELECT
vol.name,
array.ip AS 'array.ip',
array.name AS 'array.name'
FROM
storage.vfiler AS vfl,
storage.volume AS vol,
storage.array AS array
WHERE
'${vfilerName}' != 'vfiler0'
AND vol.vfiler_id = vfl.id
AND vfl.name = '${vfilerName}'
AND vol.array_id = array.id
AND (
array.ip = '${arrayName}'
OR array.name = '${arrayName}'
)
AND vol.name = '${volName}'
ORDER BY
vol.name DESC
)
UNION
(
SELECT
vol.name AS 'name',
array.ip AS 'array.ip',
array.name AS 'array.name'
FROM
storage.volume AS vol
JOIN
storage.array AS array
ON vol.array_id = array.id
WHERE
'${vfilerName}' = 'vfiler0'
AND
(
array.ip = '${arrayName}'
OR array.name = '${arrayName}'
)
)

Thanks kiran. But when I save it the workflow it throws the following error message.

java.lang.RuntimeException: An unexpected error has occurred while attempting to format SQL. It may be that the SQL provided is not well formed.

 

But I am able to test the query and it returns proper results. Looks like there is some restriction in userinput.

 

Regards

Adai

Right, user input query is not able to format the SQL properly. You can try the following working example:

 

SELECT
vol.name AS 'name',
array.ip AS 'array.ip',
array.name AS 'array.name'
FROM
storage.vfiler AS vfl,
storage.volume AS vol,
storage.array AS array
WHERE
'${vfilerName}' != 'vfiler0'
AND vol.vfiler_id = vfl.id
AND vfl.name = '${vfilerName}'
AND vol.array_id = array.id
AND (
array.ip = '${arrayName}'
OR array.name = '${arrayName}'
)
AND vol.name = '${volName}'
UNION
SELECT
vol.name AS 'name',
array.ip AS 'array.ip',
array.name AS 'array.name'
FROM
storage.volume AS vol
JOIN
storage.array AS array
ON vol.array_id = array.id
WHERE
'${vfilerName}' = 'vfiler0'
AND (
array.ip = '${arrayName}'
OR array.name = '${arrayName}'
)
ORDER BY
NAME DESC

 

 

 

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