topic SQL query format help in Active IQ Unified Manager Discussions

SQL query format help

cscott — Thu, 05 Jun 2025 05:31:07 GMT

Hi,

I need to refine an SQL query and have not been able to find syntax that appears to work.

Use Case:

In a workflow an admin selects a controller name from a drop-down

mysite1nodeA

mysite1nodeB

mysite2nodeA

mysite2nodeB

Farther down in the workflow the admin must select a dataset and I need to refine it as too many volumes get put in the first returned.

For any given frame I will have two possible datasets, i.e.:

mysite1nodeA_7_35

mysite1nodeB_7_35

mysite2nodeA_7_35

mysite2nodeB_7_35

mysite1node_0_35

mysite2node_0_35

So selecting mysite1nodeA should be able to return - mysite1nodeA_7_35 or mysite1node_0_35

using this I can get mysite1node as part of the query, but I cannot determine where(or if possible) to place the wild card to match the rest of the dataset name, so I get no result. In lab testing, creating a dataset named mysite1node returns, so the query as it stands is valid, just not what I need.

SELECT

DISTINCT dataset.name

FROM

storage.dataset

WHERE

dataset.name like substr('${src_array}',1,LENGTH('${src_array}')-1) <-- returns the controller name minus the A or B, but I have not been able to place the required "%" that will actually match the dataset.

AND dataset.name NOT LIKE '%_drive'

AND dataset.name NOT LIKE 'SnapMirrorLag'

AND dataset.dfm_name LIKE '${pm_dfm}'

ORDER BY

dataset.name

Any ideas for something other than my very basic SQL query capabilities would be greatly appreciated,

-Scott

Re: SQL query format help

cscott — Wed, 06 Aug 2014 18:55:41 GMT

Ok, this is not really what I want and probably not efficient, but it greatly reduces the number of datasets. I would still welcome a better way to do it.

	AND dataset.name LIKE concat(trim(trailing 'a' from '${src_array}'),'%')
	OR dataset.name like concat(trim(trailing 'b' from '${src_array}'),'%')

Re: SQL query format help

cscott — Wed, 06 Aug 2014 19:30:08 GMT

Does what I need, for those who are unaware "_" is a special character for the SQL query, I had to escape it with "\"

AND dataset.name LIKE concat(trim(trailing 'a' from '${src_array}'),'\_%')

OR dataset.name LIKE concat(trim(trailing 'b' from '${src_array}'),'\_%')

OR dataset.name LIKE '${src_array}%'

Still have to believe there is a better way though.

- Scott