3 Replies Latest reply: May 28, 2013 1:04 PM by Mauricio Arango-Oracle RSS

    CQL Question on using Count in Match Recognize

    Clayton Berry
      Hey crew,

      Here's my simplified scenario:

      - Device is sending in data (DeviceName, CurrentValue)
      - CurrentValue is binary (0 or 1)
      - I'm trying to capture when a device sends in the value 1, three times in a row.

      Sample Test file:
      Device1,1
      Device1,0
      Device1,1
      Device1,1
      Device1,1

      The following CQL successfully captures that condition:

      select blah.DeviceName from Channel
      MATCH_RECOGNIZE (
           partition by DeviceName
           MEASURES A.DeviceName as DeviceName
           PATTERN (A B C)
           DEFINE
                A as (A.current_value = 1),
                B as (B.current_value = 1),
                C as (C.current_value = 1)
      ) as blah

      The below CQL does NOT capture it, but I can't figure out why not:

      select blah.DeviceName from Channel
      MATCH_RECOGNIZE (
           partition by DeviceName
           MEASURES A.DeviceName as DeviceName
           PATTERN (A+ B+)
           DEFINE
           A AS (A.current_value = 1),
           B AS count(A.*) >= 3
      ) as blah

      I was using [this example|http://docs.oracle.com/cd/E28280_01/apirefs.1111/e12048/pattern_recog.htm#BEJEICAH] in the CQL guide as a reference (Example 21-6)

      Appreciate any help,
      Clayton
        • 1. Re: CQL Question on using Count in Match Recognize
          Junger He
          Hi Clayton,

          Your latter query is an eager pattern, could you try with (A+, B)? And I think you can have B as count(A.*) = 3

          Thanks
          Junger
          • 2. Re: CQL Question on using Count in Match Recognize
            Clayton Berry
            I just tried (A+ B) to no avail. Feels like I'm missing something obvious, but I can't see any typos and the compiler isn't giving me any errors.
            • 3. Re: CQL Question on using Count in Match Recognize
              Mauricio Arango-Oracle
              Hi Clayton,

              Got it to work by using (A+ B) as pattern and adding MATCH ALL as follows:
                        select blah.deviceName from inputChannel
                                            MATCH_RECOGNIZE (
                                                 partition by deviceName
                                                 MEASURES
                                                      A.deviceName as deviceName
                                                 ALL MATCHES
                                                 PATTERN (A+ B)
                                                 DEFINE
                                                      A AS (A.currentValue = 1),
                                                      B AS (count(A.*) >= 3)
                                            ) as blah

              However, this doesn't exactly match the explanation in example 21-6 and others related in the CQL guide.


              Mauricio