SSELECT syntax question

[chulett]

Have a syntx question on something culled from a Ken Bland utility job that processes jobs by selecting them based on their categories in DS_JOBS. For example:

Code: Select all

SSELECT DS_JOBS WITH F3 = "xxx"

Build a select list for all jobs where the category is equal to "xxx". What I'd like to do is modify it slightly so that it returns those jobs plus all of the jobs from any "subcategories" - i.e. categories under the named one. I can get close using LIKE but nothing quite stogy worthy as of yet.

Code: Select all

SSELECT DS_JOBS WITH F3 LIKE "xxx..."

Mostly works as it gets me the subcategories but it also matches other categories that start with the same name, i.e. "xxxyyy".

Code: Select all

SSELECT DS_JOBS WITH F3 LIKE "xxx\..."

Mostly works as it gets me the subcategories and not other matching categories but it also excludes any jobs in the main "xxx" category rather than in a subcategory.

Any suggestions? There's probably a simple answer here that I'm just not seeing right now...

[ArndW]

Would

Code: Select all

SSELECT DS_JOBS WITH F3 = "xxx" OR F3 LIKE "xxx\..."

not do the trick?

[kduke]

Backslash can be used as a quote character. It sometimes messes up in the middle of a quote. Not sure why or how to stop it. We need a full Wurlod here. It seems to be more consistent in SQL SELECT.

[chulett]

Wasn't sure if you could OR with a WITH... I'll give that shot! Haven't seen an issue with using the backslash yet, Kim, but then I haven't exactly done extensive testing with it either.

[ray.wurlod]

Quote the non-pattern elements (literal text) in the pattern expression.

Code: Select all

SSELECT DS_JOBS WITH F3 = "xxx" OR F3 LIKE "'xxx\'..."

No need for a full Wurlod here; just read about pattern matching in RetrieVe in the UniVerse manual for that language.

Or you could use DataStage/SQL.

Code: Select all

SELECT @ID TO SLIST 0 FROM DS_JOBS WHERE F3 = 'xxx' OR F3 LIKE 'xxx\%' ORDER BY @ID;

[chulett]

Thanks. Ended up extending the statement as shown in the first code snippet and it works fine. Thanks all. :D