Skip to content
HPCCSystems Solutions Lab
HPCCSystems Solutions Lab

BETWEEN

BETWEEN is an operator to check ranges of values. It is the same as <= and =>. It’s the same as SQL BETWEEN operator.

Syntax

attr_name := seek_val BETWEEN low_val AND high_val;
attr_name := seek_val NOT BETWEEN low_val AND high_val;
ValueDefinition
attribNameThe name by which the variable will be invoked.
seek_valThe value to be checked. Can be a dataset field too.
BETWEENRequired.
NOTOptional, used for certain searches.
Low_valLow value in the range
ANDRequired
high_valHigh value in the range

#### Example
/*
BETWEEN Example:
Examples using set of values.
*/

SomeInt  := 12;
SomeReal := 120.5;

CheckIntOne := SomeInt BETWEEN 10 AND 200;
CheckIntTwo := SomeInt NOT BETWEEN 10 AND 200;

OUTPUT(CheckIntOne, NAMED('CheckIntOne'));
OUTPUT(CheckIntTwo, NAMED('CheckIntTwo'));


CheckRealOne := SomeReal BETWEEN 50 AND 65;
CheckRealTwo := SomeReal NOT BETWEEN 150 AND 203.6;

OUTPUT(CheckRealOne, NAMED('CheckRealOne'));
OUTPUT(CheckRealTwo, NAMED('CheckRealTwo'));
Try Me

Demo Dataset

CityStatePopulation
Dauphin IslandAL1335
GuyAR778
El CentroCA111425
IndioCA417059
EnglewoodCO6183
KeywestFL31401
Manatee RoadFL2670
Villa RicaGA16058
AtlantaGA5449398

/*
BETWEEN Example:
Examples using dataset.
*/

Pop_Layout := RECORD
STRING   City;
STRING   State;
INTEGER  Population;
END;

Pop_DS := DATASET([
               {'Dauphin Island','AL',1335},
               {'Guy','AR',778},
               {'El Centro','CA',111425},
               {'Indio','CA',417059},
               {'Englewood','CO',6183},
               {'Keywest','FL',31401},
               {'Manatee Road','FL',2670},
               {'Villa Rica','GA',16058},
               {'Atlanta','GA',5449398}],
               Pop_Layout);

PopEval_Layout := RECORD
    STRING  City;
    STRING  State;
    BOOLEAN isMiddleSize;
END;

Eval_Town := PROJECT(Pop_DS,
                TRANSFORM(PopEval_Layout,
                SELF.isMiddleSize := LEFT.Population BETWEEN 5000 AND 30000;
                SELF := LEFT;
                ));

OUTPUT(Eval_Town, NAMED('Eval_Town'));
Try Me