Active IQ Unified Manager Discussions

SQL query format help

cscott

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

2 REPLIES 2

cscott

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

cscott

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}'),'%')
Announcements
NetApp on Discord Image

We're on Discord, are you?

Live Chat, Watch Parties, and More!

Explore Banner

Meet Explore, NetApp’s digital sales platform

Engage digitally throughout the sales process, from product discovery to configuration, and handle all your post-purchase needs.

Public