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

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

[AdaikkappanArumugam]

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]

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]

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