Manage Select Lists - is there a way to group items via the Select List SQL query?

[Elizabeth_Bond]

Hello everyone,

I'm curious if anyone has experimented with the Manage Select Lists and successfully built a query in them that would allow you to group items then let a user select from that group.

Here is my scenario - I have a list of scan queues that I'd like to group together into smaller subgroups for those applicable areas. Currently since my select statement for my select list is:

select queuename,
queuenum
from hsi.scanqueue
ORDER BY 1

It only provides the user with a list of scan queues to choose from. However, I'd ideally like to only provide a handful of choices based on the grouping we've decided.

Has anyone done that/had success with that/or have ideas how I could accomplish what I'd like to do otherwise?

Thanks for your help!

[Larissa_Armand]

That should be pretty simple then. Assuming all the scan queues have names like "HIM - ...", "Scheduling - ..." (adjust as needed), your main data provider SQL would be along the lines of: 

select *

from SCANQUEUE 

where QUEUENAME like ''|| @{pGroupName} || '%' 

;

and your SQL select list on the parameter configuration would be: 

select distinct substr(QUEUENAME, 1, instr(QUEUENAME, ' -')), substr(QUEUENAME, 1, instr(QUEUENAME, ' -'))
from SCANQUEUE
order by 1
;

That will get a substring of the scan queue name up until the first " -" in the name (again, adjust as needed to get the right segment of the name).

[Elizabeth_Bond]

Thank you so much for all of this great information Larissa, I really appreciate you taking the time to give me the full details, screenshots, and query info so I can be successful in this!

[Laura_Wilke]

You could also try using a case statement in your query.

SELECT scanqueue.queuename,

                 scanqueue.queuenum
FROM Optima3.hsi.scanqueue scanqueue
WHERE CASE queuenum  -- you could also use queuename
                      WHEN 1 THEN 'Group Name'
                      WHEN 2 THEN 'Group Name'
                      WHEN 3 THEN 'Group Name'
                      ELSE 'Misc'
                END =
               'Group Name'
ORDER BY scanqueue.queuename ASC

This has the disadvantage of needing to be maintained if you want to add a new scan queue to the group.

[Elizabeth_Bond]

Thanks Laura, yes I was worried about it becoming an administrative challenge due to the maintenance needed. Thankfully we aren't adding in ones super often but it's always better when we don't have to update constantly.

I'll look into if a case statement would work in the query.  Thanks for your advice!