Clarity

Checking to see if anyone has successfully used "BEFORE" and "AFTER" evaluations with strings in CA PPM, XOG?

The documentation CA PPM, XML Open Gateway Developer Guide Release 13.2.00 suggests that strings are valid:

However, in testing, I could not get any string to evaluate with BEFORE or AFTER.

The XOG ran without error, but returned no results. 

BEFORE and AFTER have been used before with dates.

So I am wondering if the documentation is not quite right (and this should be limited to date fields) - or if I'm just not using this correctly! (A single confirmation of using with a string type would confirm the latter.  )

WORKAROUND

You may use the "EQUALS" command with an asterisk wildcard (*) to get around the limitation.

For example, the following will pull back items beginning with "CA" or ending with "_L2".

Thanks, Kyle_R.

Have not seen BEFORE and AFTER before.

Have used BETWEEN with strings. Worked fine.

It's kind of broken (I'll get to that), but even if it wasn't, it doesn't do what you think.

With an artificial, but comparable, example of it, take this query:

select code from
(
select 1 id, 'abc' code from dual
union all
select 2 id, 'xyz' code from dual
union all
select 3 id, 'def' code from dual
) subq
where
LTRIM(RTRIM(UPPER(subq.code))) < LTRIM(RTRIM(UPPER('b')));

This is the 'BEFORE' clause at work.  My subquery is pretending to be a bunch of resources that have the code abc, def, and xyz.

You're expecting that 'AFTER B' would capture B% or B* but in fact it performs a greater-than check instead.  If you did that in the query above, it would return resources def, and xyz.  'abc' would not be returned because it is equal to or less than B.

The clause I put above for 'BEFORE B' would return only resource 'abc' and exclude the others.  As such, it should work best on dates, rankings, and sequential status/progress indicators.

However it won't because of a bug in that the XOG expressions have an 'ESCAPE' clause added to them as well.  This is the broken part, like this:

...
LTRIM(RTRIM(UPPER(subq.code))) > LTRIM(RTRIM(UPPER('b'))) ESCAPE '/';

This errors out with an ORA-907 missing right parenthesis, which is a bit odd as far as messages go, but it's correct to say this is an error.  The only time you need/want an ESCAPE clause is for LIKE comparisons because they can contain wildcard characters like % and _ and if your input data contains  REAL % or _ you wouldn't want it to be turned into a wildcard by mistake.

These clauses however are not LIKEs and therefore it's not valid to try and ESCAPE them, and can give you results like the following especially on a text field:

         <NikuDataBus xsi:noNamespaceSchemaLocation="../xsd/nikuxog_resource.xsd">
            <Header externalSource="NIKU" version="15.4.1.115"/>
            <Resources/>
            <XOGOutput>
               <Object type="resource"/>
               <Status state="FAILURE"/>
               <Statistics failureRecords="0" insertedRecords="0" totalNumberOfRecords="0" updatedRecords="0"/>
               <Records>
                  <Record>
                     <KeyInformation/>
                     <ErrorInformation>
                        <Severity>FATAL</Severity>
                        <Description>Resource Object read failed</Description>
                        <Exception>SQL Exception with error code : 907</Exception>
                     </ErrorInformation>
                  </Record>
               </Records>
...
</XOGOutput>
         </NikuDataBus>

Thanks Nick. Now the documentation just needs to be updated to match this.

Thanks for the feedback. Can somebody please help me understand what exactly needs to be updated in the XOG documentation?

Thanks, Sarmistha

Nick writes

You're expecting that 'AFTER B' would capture B% or B* but in fact it performs a greater-than check instead.  If you did that in the query above, it would return resources def, and xyz.  'abc' would not be returned because it is equal to or less than B.

However it won't because of a bug in that the XOG expressions have an 'ESCAPE' clause added to them as well.  This is the broken part, like this:

Firstly it sounds like it should be stated that AFTER and BEFORE do not work because of a know bug if they are kept in the list.

Secondly what they are designed to do is equal to greater-than and less-than checks.

Thanks for the clarification! We will update the relevant documentation and update you here when the changes are published.

Best Regards,

Sarmistha

The BUG is that an escape is used when it isn't required.

These should work (without producing a SQL error)

<Filter name="projectID" criteria="AFTER">A</Filter>
<Filter name="projectID" criteria="BEFORE">Z</Filter>

They act as "greater than" (AFTER) or "less than" (BEFORE) and when using that logic on strings it may behave in a manner you didn't expect - but that isn't a bug - that is due to the standards behind SQL.

Filters such as these will work following exactly the same SQL based logic:

<Filter name="start" criteria="AFTER">2007-01-07</Filter>

<Filter name="start" criteria="BEFORE">2009-01-15</Filter>

BETWEEN equates to >= and <=

AFTER equates to >

BEFORE equates to <

and each should work on any data type (as they do in SQL)

ps: "between" in SQL is NOT designed for date ranges or use only with dates, this is a common misconception.