unable to save workflow - malformed SQL

[nscherer]

Hi,

I'm trying to save a workflow (WFA 2.1) and get an error message stating

Save Workflow Failure

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

This is caused by SQL for a user input, after removing the SQL code I can save the workflow.

Is there a way to work around it? The SQL code is working fine when previewing the workflow.

Thanks,

Nico

And this is the SQL code:

( SELECT quota.target AS target

  FROM storage.quota AS quota

  WHERE NOT STRCMP('${QuotaType}','Project Folder') AND

  SUBSTRING_INDEX(SUBSTRING_INDEX(quota.target,'/',-2),'/',1) IN

  (  SELECT vol.name AS target FROM storage.volume AS vol

     JOIN storage.resource_group_member AS rgm ON rgm.volume_id = vol.id

     JOIN storage.resource_group AS rg ON rg.id = rgm.group_id

     WHERE rg.name = 'WFA_NAS_ProjectVolumes' )

)

UNION ALL

( SELECT DISTINCT SUBSTRING_INDEX(uquota.user_name,'\\',-1) AS target

  FROM storage.user_quota AS uquota

  WHERE NOT STRCMP('${QuotaType}','User Home Drive') AND

  uquota.user_name LIKE CONCAT('${Domain}','\\\%')

)

ORDER BY target

[ag · Accepted solution]

Hey,

This is a known issue and there is a BUG that had been raised to fix it as well.

The SQL formatter fails on the UNION if paranthesis is used.

Try this:

SELECT quota.target AS target

  FROM storage.quota AS quota

  WHERE NOT STRCMP('${QuotaType}','Project Folder') AND

  SUBSTRING_INDEX(SUBSTRING_INDEX(quota.target,'/',-2),'/',1) IN

  (  SELECT vol.name AS target FROM storage.volume AS vol

     JOIN storage.resource_group_member AS rgm ON rgm.volume_id = vol.id

     JOIN storage.resource_group AS rg ON rg.id = rgm.group_id

     WHERE rg.name = 'WFA_NAS_ProjectVolumes' )

UNION ALL

SELECT DISTINCT SUBSTRING_INDEX(uquota.user_name,'\\',-1) AS target

  FROM storage.user_quota AS uquota

  WHERE NOT STRCMP('${QuotaType}','User Home Drive') AND

  uquota.user_name LIKE CONCAT('${Domain}','\\\%')

ORDER BY target

I have just removed the paranthesis from the two queries

[nscherer]

Hi Anil,

I saw the bug and it looks like it still exists in WFA 2.2RC1, so upgrading won't help.

Your recommendation worked, removing the parentheses from the first select statement was sufficient to save the query and it keeps the 'order by' clause working.

Thanks for your help,

Nico

[adaikkap]

Hi Nico,

          You are right bug 806754 is not fixed in 2.2RC1R1 or the GA release as well.

May I request you to open a case with NetApp Support for the same so that this gets prioritized ?

Regards

adai