The substitute operator turns a nullable attribute into a non-nullable attribute and returns a result array with a specified value substituted for null values in an array. 

Synopsis

substitute(nullable_array, substitute_array [,attribute_1 ,attribute_2...]);

Summary

The substitute operator substitutes null values in one array with non-null values from another array. By default, SciDB substitutes the null values of all nullable attributes. Optionally, you can list specific attributes to participate in the substitution. The substitute operator renders attributes in the result array non-nullable.

Examples

Substitute in single attribute array

 To demonstrate substitute operator, do the following:

1. Create an array an array where every other attribute value is null:

   AFL% store(build(<val:double>[i=0:3], iif(i%2=0,i,null)), substitute_example_1);

   
   The output is:

   {i} val
   {0} 0
   {1} null
   {2} 2
   {3} null

2. Substitute the null values of the attribute val in the substitute_example_1 array with: 

   AFL% substitute(substitute_example_1, build(<subval:double NOT NULL>[i=0:0], 99)); 
    
   -- or
    
   AFL% substitute(substitute_example_1, build(<subval:double NOT NULL>[i=0:0], 99), val); 

   
   The output is:

   {i} val
   {0} 0
   {1} 99
   {2} 2
   {3} 99

3. Observe the resulting schema of the substitute operator with NOT NULL attribute:

   show('substitute(substitute_example_1, build(<subval:double NOT NULL>[i=0:0], 99))','afl');

   
   The output is:

   {i} schema
   {0} 'substitute_example_1@1_subst<val:double NOT NULL>[i=0:3:0:1000000]'

Substitute in Multiple attribute Array

1. Create a multiple Attribute array from substitute_example_1 with null values with a new attribute val_2:

   AFL% store(apply(substitute_example_1,val_2,iif((i/2)%2!=0,i*0.75,null)),substitute_example_2);

   
   The output is:

   {i} val,val_2
   {0} 0,null
   {1} null,null
   {2} 2,1.5
   {3} null,2.25

2. Substitute the values in second attribute:

   AFL% substitute(substitute_example_2, build(<subval:double NOT NULL>[i=0:0], 99), val_2);

   
   The output is:

   {i} val,val_2
   {0} 0,99
   {1} null,99
   {2} 2,1.5
   {3} null,2.25

3. Observe the resulting schema:

   AFL% show('substitute(substitute_example_2, build(<subval:double NOT NULL>[i=0:0], 99), val_2)','afl');
   {i} schema
   {0} 'substitute_example_2@1_subst<val:double,val_2:double NOT NULL> [i=0:3:0:1000000]'

4. Substitute the values in both attributes

   substitute(substitute_example_2, build( <subval:double NOT NULL>[i=0:0], 99), val, val_2);

   
   The output is:

   {i} val,val_2
   {0} 0,99
   {1} 99,99
   {2} 2,1.5
   {3} 99,2.25

5. Observe the resulting schema:

   AFL% show(' substitute(substitute_example_2, build(<subval:double NOT NULL>[i=0:0], 99), val, val_2)','afl');

   
   The output is:

   {i} schema
   {0} 'substitute_example_2@1_subst<val:double NOT NULL,val_2:double NOT NULL>[i=0:3:1000000]'

6. Remove the Example Schema:

   AFL% remove(substitute_example_1); remove(substitute_example_2);