how do i filter ancestordisplayname for group?

Hello,

Using SWQL studio and I am unable to find a way to filter values from the AncestorDisplayNames column. The column is just a string but all the values are in the format of [value]. In my where clause, I've tried ['%value%'], '%value%', even [value] (copy and pasted) doesnt work. The query completes, however it produces no values.

TIA

Find more posts tagged with

swql query

sam

SWQL

npm

Group

Accepted answers

All comments

adam.beedell

Most of the time there's an easier column to use

Which table are you working with?

I believe what's happening is the square brackets are a representation of an array, and so dont match their string representation

Something like

"SELECT TOP 10 fs.AncestorDisplayNames
FROM Orion.SRM.FileShares fs
where fs.AncestorDisplayNames+'' like '[thing]%'
"

-produces interesting results. There's some tips and tricks around for variable type forcing but I dont think I know a specific link on the topic.

mrt3385

hello, table in question is the Orion.Container table. You're right, even though it says System.String, there are [] brackets after. Much different than the other standard strings.

mesverrum

The thing to keep in mind about the green attributes in SWQL studio is they aren't exactly "real" in the database. They are constructed by the information service when you query it, so they don't really have to behave the way we might expect. I tend not to use them as the basis of anything important.

I wouldn't be surprised if you can't really interact with that column. It would be kind of calculated on the fly by the info service, probably with some kind of recursive SQL query, but since SWQL doesn't allow for recursive queries you might not be able to just throw a where condition in and have it behave as expected. Maybe could trick it and get away with something like

select Name, ancestorDisplayName

from (

select c.name, c.ancestordisplaynames

from orion.containers c

)

where ancestorDisplayName like '%something%'

But I'm not on VPN to test that today. If that kind of syntax doesn't work you might end up needing to just manually build up a stack of left joins to however many layers your group tree needs to accomplish what you are aiming to do.

mrt3385

Thanks for your reply and explanation

Produces no results. That's been my experience with anything where I filter on the ancestorDisplayNames column unfortunately.

adam.beedell

I think i solved it last time

Quick Links

All Categories
Recent Posts
Activity
Unanswered
Groups
Help
Best Of