Active IQ Unified Manager Discussions

Can a user input query in WFA have IF THEN ELSE

AdaikkappanArumugam
3,761 Views

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

rkiran
3,671 Views

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}'
)
)

AdaikkappanArumugam
3,613 Views

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

rkiran
3,513 Views

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

 

 

 

FelipeMafra
3,152 Views

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

Public