Skip to main content

Handling nulls with CQs

The following CQ would insert events from PosSource_TransformedStream (see Joining cache data with CQs) for which there was no matching zip code in ZipCache into NoMatchingZipStream:

CREATE TrapZipMatchingErrors
INSERT INTO NoMatchingZipStream
SELECT p.MERCHANTID,
  p.DATETIME,
  p.AUTHAMOUNT
FROM PosSource_TransformedStream p
LEFT OUTER JOIN ZipCache z
ON p.ZIP = z.Zip WHERE z.Zip IS NULL;

The following CQ joins events from two streams using the MATCH_PATTERN (see Using pattern matching) clause. If after 24 hours no matching event has been received, the event is output with <Timeout> and null in place of the matching event's data source name and record.

CREATE CQ MatchedRecordsCQ 
INSERT INTO PatternMatchStream
SELECT a.unique_id, a.data_source_name, a.data_source_record,
  CASE WHEN b IS NOT NULL THEN b.data_source_name ELSE "<Timeout>" END, 
  CASE WHEN b IS NOT NULL THEN b.data_source_record ELSE null END
FROM MergedDataSourcesStream 
  MATCH_PATTERN (T a (b | W))
    DEFINE T = timer(interval 24 hour),
      a = MergedDataSourcesStream(),
      b = MergedDataSourcesStream(unique_id == a.unique_id),
      W = wait(T)
PARTITION BY unique_id;