JSR-170 SQL query using 'IN' operator in where clause

Rhythmyx Technical Discussion
1

All,

Is it possible to use IN operator in where clause - for example

SELECT rx:sys_contentlastmodifieddate , rx:sys_title
FROM rx:NewsTopic
where rx:sys_contentid IN (3868,4591,2834 ) order by rx:sys_title

According to JSR-170 this is a valid syntax

http://docs.jboss.org/modeshape/latest/manuals/reference/html/jcr-query-and-search.html#jcr-xpath-column-specifiers

Thanks for help
Atul

2

I can’t get this to work. When I attempt to include the “IN” operator in my where clause, as in this query:

select sys_contentid, ovpid, videotitle from rx:mwVideo where rx:ovpid in (“1109924662001”, “1109924662002”)

I get a runtime error:

Query is select sys_contentid, ovpid, videotitle from rx:mwVideo
where rx:ovpid in (“1109924662001”, “1109924662002”)

Unexpected Exception

java.lang.RuntimeException: line 2:7: unexpected token: rx:ovpid
com.percussion.services.contentmgr.impl.query.SqlParser.reportError(Unknown Source)
com.percussion.services.contentmgr.impl.query.SqlParser.logical_factor(Unknown Source)
com.percussion.services.contentmgr.impl.query.SqlParser.logical_term(Unknown Source)
com.percussion.services.contentmgr.impl.query.SqlParser.condition(Unknown Source)
com.percussion.services.contentmgr.impl.query.SqlParser.where_condition(Unknown Source)
com.percussion.services.contentmgr.impl.query.SqlParser.start_rule(Unknown Source)
…

(Anyone know how to stop this editor for turning the character “:” followed by “o” into a smiley?!)

3

To disable smilies in text you must go to the Advanced Reply and check “Disable smilies in text”

4

[QUOTE=kmbailey;19636]I can’t get this to work. When I attempt to include the “IN” operator in my where clause, as in this query:

select sys_contentid, ovpid, videotitle from rx:mwVideo where rx:ovpid in (“1109924662001”, “1109924662002”)

I get a runtime error:

Query is select sys_contentid, ovpid, videotitle from rx:mwVideo
where rx:ovpid in (“1109924662001”, “1109924662002”)

Unexpected Exception

java.lang.RuntimeException: line 2:7: unexpected token: rx:ovpid
com.percussion.services.contentmgr.impl.query.SqlParser.reportError(Unknown Source)
com.percussion.services.contentmgr.impl.query.SqlParser.logical_factor(Unknown Source)
com.percussion.services.contentmgr.impl.query.SqlParser.logical_term(Unknown Source)
com.percussion.services.contentmgr.impl.query.SqlParser.condition(Unknown Source)
com.percussion.services.contentmgr.impl.query.SqlParser.where_condition(Unknown Source)
com.percussion.services.contentmgr.impl.query.SqlParser.start_rule(Unknown Source)
…
[/QUOTE]

Not supported since the IN clause is a feature of jboss modeshape’s JCR-SQL2 spec (https://docs.jboss.org/author/display/MODE/JCR-SQL2#JCR-SQL2-Subquery). Percussion CM implements JCR version 1.0.

5

Link above was for subqueries. Try the JCR 1.0 spec for where clause properties http://www.day.com/specs/jcr/1.0/6.6.3.3_Property_Constraint.html

6

Update: Looks like the CONTAINS function is not supported in Percussion CM.