WFA User Input SQL Query Error

[RoiBeci]

Hi,

I'm trying to run the following query on a user input, but get the error:

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

Do we have some restrictions on a user input query? 

Maybe another idea on how to use this query?

SELECT 
CASE
WHEN EXISTS (
SELECT
    volume.name as 'volname'
FROM
    cm_storage.cluster,
    cm_storage.vserver,
    cm_storage.volume
WHERE
    vserver.cluster_id = cluster.id
    AND volume.vserver_id = vserver.id
    AND (
        cluster.primary_address = '${ClusterName}'
        OR cluster.name='${ClusterName}'
    )
    AND vserver.name = '${VserverName}'
    AND volume.name IN (
        select
            quota_rule.quota_volume
        from
            cm_storage_quota.quota_rule
        where
            quota_rule.vserver_name=vserver.name
            AND quota_rule.user_name='${UserName}'
    ) 
) = 1
THEN (
SELECT
    volume.name as 'volname'
FROM
    cm_storage.cluster,
    cm_storage.vserver,
    cm_storage.volume
WHERE
    vserver.cluster_id = cluster.id
    AND volume.vserver_id = vserver.id
    AND (
        cluster.primary_address = '${ClusterName}'
        OR cluster.name='${ClusterName}'
    )
    AND vserver.name = '${VserverName}'
    AND volume.name IN (
        select
            quota_rule.quota_volume
        from
            cm_storage_quota.quota_rule
        where
            quota_rule.vserver_name=vserver.name
            AND quota_rule.user_name='${UserName}'
    ) 
)
ELSE ( 
Select * from(
SELECT 'fake_vaule' AS value
Union
select 'fake_value_2' AS value) AS t2
)
End

Thanks,

Roi Becidan

NetApp.

[anuk · Accepted solution]

Hi Roi,

     You can try running the query without formating the SQL query.  

     To disable formating of SQL queries  unselcet the option "Enable formating of SQL Queries" under WFA Configuration->Other tab. Then run the user input query. By default formating is enabled. You can turn it off.

Thanks and Regards

anu

[RoiBeci]

Thanks that did the trick 🙂