Fast/Unload standard functions: Difference between revisions

From m204wiki
Jump to navigation Jump to search
m (more conversion cleanup)
m (more conversion cleanup)
Line 365: Line 365:
<td><i>fmt</i> is not a valid datetime format.</td></tr>
<td><i>fmt</i> is not a valid datetime format.</td></tr>
</table>
</table>
In the event of an error:
In the event of a run-time error:
<ul>
<ul>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error,  
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error,  
Line 414: Line 414:
REPORT %X
REPORT %X
</nowiki></p>
</nowiki></p>
<p>
<b>Errors</b></p>
The possible non-zero values of <var class="term">%rc</var>, or terminating conditions,
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<table>
<tr><th>4</th>
<td><var class="term">fmt</var> is not a valid datetime format.</td></tr>


Non-zero values of <i>%rc</i>, or terminating conditions,
<tr><th>8</th>
are shown in the following figure (see the discussion in
<td><var class="term">dat</var> does not match <var class="term">fmt</var> or result date out of range.</td></tr>
[[#funerr|Run-time errors during standard #function calls]]).


<b>Errors:</b>
<tr><th>12</th>
If <var class="term">%rc</var> present, set to corresponding number
<td><var class="term">n</var> is not a number or <var class="term">span</var> is invalid.</td></tr>
and set <var class="term">%odat</var> to MISSING. If absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<tr><th>4</th><td><var class="term">fmt</var> is not a valid datetime format.</td></tr>
<tr><th>8</th><td><var class="term">dat</var> does not match <var class="term">fmt</var> or result date out of range.</td></tr>
<tr><th>12</th><td><var class="term">n</var> is not a number or <var class="term">span</var> is invalid.</td></tr>
</table>
</table>
In the event of a run-time error:
<ul>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error, and <var class="term">%odat</var> is set to <var>MISSING</var>. </li>
<li>If <var class="term">%rc</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
<p>
<p>
<b>Notes</b></p>
<b>Notes</b></p>
Line 487: Line 493:
[[#funerr|Run-time errors during standard #function calls]]).
[[#funerr|Run-time errors during standard #function calls]]).


<b>Errors:</b>
<table>
If <var class="term">%rc</var> present, set to corresponding number
<tr><th>4</th><td><var class="term">fmt</var> is not a valid datetime format.</td></tr>
and set <var class="term">%tst</var> to MISSING. If absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<tr><th>8</th><td><var class="term">dat</var> does not match <var class="term">fmt</var> or result date out of range.</td></tr>
<tr><th>12</th><td><var class="term">n</var> is not a number or <var class="term">span</var> is invalid.</td></tr>
</table>
   
   
<table>
<table>
Line 552: Line 560:
REPORT %X
REPORT %X
</p>
</p>
<p>
<b>Errors</b></p>
The possible non-zero values of <var class="term">%rc</var>, or terminating conditions,
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<table>
<tr><th>4</th>
<td><i>infmt</i> or <var class="term">outfmt</var> is not a valid datetime format.</td></tr>


Non-zero values of <var class="term">%rc</var>, or terminating conditions,
<tr><th>8</th>
are shown in the following figure (see the discussion in
[[#funerr|Run-time errors during standard #function calls]]).
 
<b>Errors:</b>
If <var class="term">%rc</var> present, set to corresponding number
and set <var class="term">%odat</var> to MISSING.
If absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<tr><th>4</th>
<td><i>infmt</i> or <var class="term">outfmt</var> is not a valid datetime format.</td></tr>
 
<tr><th>8</th>
<td><i>dat</i> does not match <var class="term">infmt</var>.</td></tr>
<td><i>dat</i> does not match <var class="term">infmt</var>.</td></tr>


Line 575: Line 577:
<td>Converted datetime value out of range for <var class="term">%outfmt</var>.</td></tr>
<td>Converted datetime value out of range for <var class="term">%outfmt</var>.</td></tr>
</table>
</table>
In the event of a run-time error:
<ul>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error, and <var class="term">%odat</var> is set to <var>MISSING</var>. </li>
<li>If <var class="term">%rc</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
<p>
<p>
<b>Notes</b></p>
<b>Notes</b></p>
Line 612: Line 620:
</nowiki></p>
</nowiki></p>


Non-zero values of <i>%rc</i>, or terminating conditions,
<p>
are shown in the following figure (see the discussion in
<b>Errors</b></p>
[[#funerr|Run-time errors during standard #function calls]]).
The possible non-zero values of <var class="term">%rc</var>, or terminating conditions,
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<table>
<tr><th>4</th>
<td><i>fmta</i> or <i>fmtb</i> is not a valid datetime format.</td></tr>
 
<tr><th>8</th>
<td><i>data</i> does not match <i>fmta</i>, or <i>datb</i> does not match <i>fmtb</i>.</td></tr>


<b>Errors:</b>
<tr><th>12</th>
if <i>%rc</i> present, set to corresponding number
<td><i>span</i> is an invalid <var>CENTSPAN</var> value.</td></tr>
and set <i>%dif</i> to MISSING;
if absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<tr><th>4</th><td><i>fmta</i> or <i>fmtb</i> is not a valid datetime format.</td></tr>
<tr><th>8</th><td><i>data</i> does not match <i>fmta</i>, or <i>datb</i> does not match <i>fmtb</i>.</td></tr>
<tr><th>12</th><td><i>span</i> is an invalid CENTSPAN value.</td></tr>
</table>
</table>
<p><b>Notes</b></p>
In the event of a run-time error:
<ul>
<ul>
<li>Time is ignored in the subtraction. </li>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error, and <var class="term">%dif</var> is set to <var>MISSING</var>. </li>
 
<li>If <var class="term">%rc</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
<p>
<b>Notes</b></p>
<ul>
<li>Time is ignored in the subtraction. </li>


<li>In the example above, even though the input dates have 2-digit years,
<li>In the example above, even though the input dates have 2-digit years,
Line 703: Line 718:
END IF
END IF
</nowiki></p>
</nowiki></p>
<p>
<b>Errors</b></p>
The possible non-zero values of <var class="term">%rc</var>, or terminating conditions,
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<table>
<tr><th>4</th>
<td><i>fmt</i> is not a valid datetime format.</td></tr>


Non-zero values of <i>%rc</i>, or terminating conditions,
<tr><th>8</th>
are shown in the following figure (see the discussion in
<td><i>dat</i> does not match <i>fmt</i> or result date out of range.</td></tr>
[[#funerr|Run-time errors during standard #function calls]]).


<b>Errors:</b>
<tr><th>12</th>
if <i>%rc</i> present, set to corresponding number
<td><i>span</i> is an invalid <var>CENTSPAN</var> value.</td></tr>
and set <i>%num</i> to MISSING;
if absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<tr><th>4</th><td><i>fmt</i> is not a valid datetime format.</td></tr>
<tr><th>8</th><td><i>dat</i> does not match <i>fmt</i> or result date out of range.</td></tr>
<tr><th>12</th><td><i>span</i> is an invalid CENTSPAN value.</td></tr>
</table>
</table>
<p><b>Notes</b></p>
In the event of a run-time error:
<ul>
<ul>
<li>Values returned by #DATE2N </li>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error, and <var class="term">%num</var> is set to <var>MISSING</var>. </li>


will often exceed the range that can be represented
<li>If <var class="term">%rc</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
<p>
<b>Notes</b></p>
<ul>
<li>Values returned by #DATE2N often exceed the range that can be represented
in a 4-byte integer, so you should probably avoid storing the
in a 4-byte integer, so you should probably avoid storing the
value in a BINARY or FLOAT4 field. </li>
value in a BINARY or FLOAT4 field. </li>
Line 763: Line 782:
END IF
END IF
</nowiki></p>
</nowiki></p>
<p>
<b>Errors</b></p>
The possible non-zero values of <var class="term">%rc</var>, or terminating conditions,
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<table>
<tr><th>4</th>
<td><i>fmt</i> is not a valid datetime format.</td></tr>


Non-zero values of <i>%rc</i>, or terminating conditions,
<tr><th>8</th>
are shown in the following figure (see the discussion in
<td><i>dat</i> does not match <i>fmt</i> or result date out of range.</td></tr>
[[#funerr|Run-time errors during standard #function calls]]).


<b>Errors:</b>
<tr><th>12</th>
if <i>%rc</i> present, set to corresponding number
<td><i>span</i> is an invalid CENTSPAN value.</td></tr>
and set <i>%num</i> to MISSING;
if absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<tr><th>4</th><td><i>fmt</i> is not a valid datetime format.</td></tr>
<tr><th>8</th><td><i>dat</i> does not match <i>fmt</i> or result date out of range.</td></tr>
<tr><th>12</th><td><i>span</i> is an invalid CENTSPAN value.</td></tr>
</table>
</table>
<p><b>Notes</b></p>
In the event of a run-time error:
<ul>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error, and <var class="term">%num</var> is set to <var>MISSING</var>. </li>
 
<li>If <var class="term">%rc</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
<p>
<b>Notes</b></p>
<ul>
<ul>
<li>Dates prior to 1 January 1900 will return a negative number. </li>
<li>Dates prior to 1 January 1900 will return a negative number. </li>
Line 815: Line 840:
</nowiki></p>
</nowiki></p>


Non-zero values of <i>%rc</i>, or terminating conditions,
<p>
are shown in the following figure (see the discussion in
<b>Errors</b></p>
[[#funerr|Run-time errors during standard #function calls]]).
The possible non-zero values of <var class="term">%rc</var>, or terminating conditions,
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<table>
<tr><th>4</th>
<td><i>fmt</i> is not a valid datetime format.</td></tr>
 
<tr><th>8</th>
<td><i>dat</i> does not match <i>fmt</i> or result date out of range.</td></tr>


<b>Errors:</b>
<tr><th>12</th>
if <i>%rc</i> present, set to corresponding number
<td><i>span</i> is an invalid CENTSPAN value.</td></tr>
and set <i>%num</i> to MISSING;
if absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<tr><th>4</th><td><i>fmt</i> is not a valid datetime format.</td></tr>
<tr><th>8</th><td><i>dat</i> does not match <i>fmt</i> or result date out of range.</td></tr>
<tr><th>12</th><td><i>span</i> is an invalid CENTSPAN value.</td></tr>
</table>
</table>
<p><b>Notes</b></p>
In the event of a run-time error:
<ul>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error, and <var class="term">%num</var> is set to <var>MISSING</var>. </li>
 
<li>If <var class="term">%rc</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
<p>
<b>Notes</b></p>
<ul>
<ul>
<li>Values returned by #DATE2NM
<li>Values returned by #DATE2NM
Line 874: Line 906:
</nowiki></p>
</nowiki></p>


Non-zero values of <i>%rc</i>, or terminating conditions,
<p>
are shown in the following figure (see the discussion in
<b>Errors</b></p>
[[#funerr|Run-time errors during standard #function calls]]).
The possible non-zero values of <var class="term">%rc</var>, or terminating conditions,
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<table>
<tr><th>4</th>
<td><i>fmt</i> is not a valid datetime format.</td></tr>
 
<tr><th>8</th>
<td><i>dat</i> does not match <i>fmt</i> or result date out of range.</td></tr>


<b>Errors:</b>
if <i>%rc</i> present, set to corresponding number
and set <i>%num</i> to MISSING;
if absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<tr><th>4</th><td><i>fmt</i> is not a valid datetime format.</td></tr>
<tr><th>8</th><td><i>dat</i> does not match <i>fmt</i> or result date out of range.</td></tr>
<tr><th>12</th><td><i>span</i> is an invalid CENTSPAN value.</td></tr>
<tr><th>12</th><td><i>span</i> is an invalid CENTSPAN value.</td></tr>
</table>
</table>
<p><b>Notes</b></p>
In the event of a run-time error:
<ul>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error, and <var class="term">%num</var> is set to <var>MISSING</var>. </li>
 
<li>If <var class="term">%rc</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
<p>
<b>Notes</b></p>
<ul>
<ul>
<li>Values returned by #DATE2NS
<li>Values returned by #DATE2NS
Line 1,223: Line 1,261:
<table>
<table>
<tr><th>datn</th><td>Numeric datetime value expressed as the number of days since 1 Jan 1900 12:00 AM. This argument may not have the MISSING value.</td></tr>
<tr><th>datn</th><td>Numeric datetime value expressed as the number of days since 1 Jan 1900 12:00 AM. This argument may not have the MISSING value.</td></tr>
<tr><th>fmt</th><td>Datetime format string to use for creating <i>%dat</i>.</td></tr>
<tr><th>fmt</th><td>Datetime format string to use for creating <i>%dat</i>.</td></tr>
<tr><th>%rc</th><td>Return code (optional, output)</td></tr>
<tr><th>%rc</th><td>Return code (optional, output)</td></tr>
<tr><th>%dat</th><td>Set to datetime string value, in format specified by <i>fmt</i>, corresponding to <i>datn</i>.</td></tr>
<tr><th>%dat</th><td>Set to datetime string value, in format specified by <i>fmt</i>, corresponding to <i>datn</i>.</td></tr>
</table>
</table>
Line 1,236: Line 1,276:
</nowiki></p>
</nowiki></p>


Non-zero values of <i>%rc</i>, or terminating conditions,
<p>
are shown in the following figure (see the discussion in
<b>Errors</b></p>
[[#funerr|Run-time errors during standard #function calls]]).
The possible non-zero values of <var class="term">%rc</var>, or terminating conditions,
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<table>
<tr><th>4</th>
<td><i>fmt</i> is not a valid datetime format.</td></tr>


<b>Errors:</b>
<tr><th>8</th>
if <i>%rc</i> present, set to corresponding number
<td><i>datn</i> is out of range for <i>fmt</i>.</td></tr>
and set <i>%dat</i> to MISSING;
if absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<tr><th>4</th><td><i>fmt</i> is not a valid datetime format.</td></tr>
<tr><th>8</th><td><i>datn</i> is out of range for <i>fmt</i>.</td></tr>
</table>
</table>
In the event of a run-time error:
<ul>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error, and <var class="term">%dat</var> is set to <var>MISSING</var>. </li>
<li>If <var class="term">%rc</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
<p>
<p>
<b>Notes</b></p>
<b>Notes</b></p>
Line 1,288: Line 1,332:
</nowiki></p>
</nowiki></p>


Non-zero values of <i>%rc</i>, or terminating conditions,
<p>
are shown in the following figure (see the discussion in
<b>Errors</b></p>
[[#funerr|Run-time errors during standard #function calls]]).
The possible non-zero values of <var class="term">%rc</var>, or terminating conditions,
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<table>
<tr><th>4</th>
<td><i>fmt</i> is not a valid datetime format.</td></tr>


<b>Errors:</b>
<tr><th>8</th>
if <i>%rc</i> present, set to corresponding number
<td><i>datn</i> is out of range for <i>fmt</i>.</td></tr>
and set <i>%dat</i> to MISSING;
if absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<tr><th>4</th><td><i>fmt</i> is not a valid datetime format.</td></tr>
<tr><th>8</th><td><i>datn</i> is out of range for <i>fmt</i>.</td></tr>
</table>
</table>
In the event of a run-time error:
<ul>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error, and <var class="term">%dat</var> is set to <var>MISSING</var>. </li>
<li>If <var class="term">%rc</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
<p>
<p>
<b>Notes</b></p>
<b>Notes</b></p>
Line 1,332: Line 1,380:
</nowiki></p>
</nowiki></p>


Non-zero values of <i>%rc</i>, or terminating conditions,
<p>
are shown in the following figure (see the discussion in
<b>Errors</b></p>
[[#funerr|Run-time errors during standard #function calls]]).
The possible non-zero values of <var class="term">%rc</var>, or terminating conditions,
 
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<b>Errors:</b>
if <i>%rc</i> present, set to corresponding number
and set <i>%dat</i> to MISSING;
if absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<table>
<tr><th>4</th><td><i>fmt</i> is not a valid datetime format.</td></tr>
<tr><th>4</th>
<tr><th>8</th><td><i>datn</i> is out of range for <i>fmt</i>.</td></tr>
<td><i>fmt</i> is not a valid datetime format.</td></tr>
 
<tr><th>8</th>
<td><i>datn</i> is out of range for <i>fmt</i>.</td></tr>
</table>
</table>
In the event of a run-time error:
<ul>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error, and <var class="term">%dat</var> is set to <var>MISSING</var>. </li>
<li>If <var class="term">%rc</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
<p>
<p>
<b>Notes</b></p>
<b>Notes</b></p>
Line 1,470: Line 1,522:
</nowiki></p>
</nowiki></p>


Non-zero values of <i>%intlen</i>, or terminating conditions,
<p>
are shown in the following figure (see the discussion in
<b>Errors</b></p>
[[#funerr|Run-time errors during standard #function calls]]).
The possible non-zero values of <var class="term">%intlen</var>, or terminating conditions,
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<table class="thJustBold">
<tr><th>-N</th>
<td><i>N</i> is the length required for the integer part of <i>num</i>, and <i>n</i>><i>intw</i> and <i>opt</i> does not contain the letter "V".</td></tr>
 
<tr><th>0</th>
<td><i>Num</i> can not be converted to a numeric value (<i>%str</i> is set to MISSING).</td></tr>
 
<tr><th>**</th>
<td>Negative or non-numeric value for <i>intw</i> or <i>fracw</i> (this error always cancels <var class="product">Fast/Unload</var>, regardless of the presence of <b>%intlen</b>).</td></tr>
 
<tr><th>**</th>
<td>Invalid character in <i>opt</i> (this error always cancels <var class="product">Fast/Unload</var>, regardless of the presence of <b>%intlen</b>).</td></tr>


<b>Errors:</b>
<tr><th>**</th>
if <i>%intlen</i> present, set to corresponding number;
<td>Length of <i>pad</i> not 1 (this error always cancels <var class="product">Fast/Unload</var>, regardless of the presence of <b>%intlen</b>).</td></tr>
if absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<tr><th>-N</th><td><i>N</i> is the length required for the integer part of <i>num</i>, and <i>n</i>><i>intw</i> and <i>opt</i> does not contain the letter "V".</td></tr>
<tr><th>0</th><td><i>Num</i> can not be converted to a numeric value (<i>%str</i> is set to MISSING).</td></tr>
<tr><th>**</th><td>Negative or non-numeric value for <i>intw</i> or <i>fracw</i> (this error always cancels <var class="product">Fast/Unload</var>, regardless of the presence of <b>%intlen</b>).</td></tr>
<tr><th>**</th><td>Invalid character in <i>opt</i> (this error always cancels <var class="product">Fast/Unload</var>, regardless of the presence of <b>%intlen</b>).</td></tr>
<tr><th>**</th><td>Length of <i>pad</i> not 1 (this error always cancels <var class="product">Fast/Unload</var>, regardless of the presence of <b>%intlen</b>).</td></tr>
</table>
</table>
<blockquote class="note"><b>Note:</b> If you are creating a string to place in the <var class="product">Fast/Unload</var>
In the event of a run-time error:
<ul>
<li>If you specified a <var class="term">%intlen</var> in your #function call, it is set to the return code above that corresponds to the error. </li>
 
<li>If <var class="term">%intlen</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
 
<blockquote class="note">
<p><b>Note:</b> If you are creating a string to place in the <var class="product">Fast/Unload</var>
output file, you can also use the PUT with the AS STRING or AS DECIMAL
output file, you can also use the PUT with the AS STRING or AS DECIMAL
clauses (see [[Fast/Unload Extraction Language (FUEL)#put|PUT]]).
clauses (see [[Fast/Unload Extraction Language (FUEL)#put|PUT]]).</p>
 
<p>
One difference between #NUM2STR and PUT AS STRING or PUT AS DECIMAL
One difference between #NUM2STR and PUT AS STRING or PUT AS DECIMAL
is that all conversion
is that all conversion of fractional values to fixed width output formats
of fractional values to fixed width output formats
in the PUT statement causes low order fraction digits to be dropped
in the PUT statement causes low order fraction digits to be dropped
without rounding, but #NUM2STR offers rounding of dropped low order
without rounding, but #NUM2STR offers rounding of dropped low order digits.</p>
digits.
</blockquote>
</blockquote>


Line 1,524: Line 1,587:
</nowiki></p>
</nowiki></p>


Non-zero values of <i>%rc</i>, or terminating conditions,
<p>
are shown in the following figure (see the discussion in
<b>Errors</b></p>
[[#funerr|Run-time errors during standard #function calls]]).
The possible non-zero values of <var class="term">%rc</var>, or terminating conditions,
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<table>
<tr><th>4</th>
<td><i>fmt</i> is not a valid datetime format.</td></tr>


<b>Errors:</b>
<tr><th>8</th>
if <i>%rc</i> present, set to corresponding number
<td><i>datn</i> is out of range for <i>fmt</i>.</td></tr>
and set <i>%dat</i> to MISSING;
if absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<tr><th>4</th><td><i>fmt</i> is not a valid datetime format.</td></tr>
<tr><th>8</th><td><i>datn</i> is out of range for <i>fmt</i>.</td></tr>
</table>
</table>
<p><b>Notes</b></p>
In the event of a run-time error:
<ul>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error, and <var class="term">%dat</var> is set to <var>MISSING</var>. </li>
 
<li>If <var class="term">%rc</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
<p>
<b>Notes</b></p>
<ul>
<ul>
<li>The inverse of this #function is #DATE2N. </li>
<li>The inverse of this #function is #DATE2N. </li>
Line 1,937: Line 2,005:
<p class="code"><nowiki>ADD REORG_TIME = #TIME()
<p class="code"><nowiki>ADD REORG_TIME = #TIME()
</nowiki></p>
</nowiki></p>
<p>Non-zero values of <i>%rc</i>, or terminating conditions,
<p>
are shown in the following figure (see the discussion in
<b>Errors</b></p>
[[#funerr|Run-time errors during standard #function calls]]).
The possible non-zero values of <var class="term">%rc</var>, or terminating conditions,
</p>
are shown below (see the discussion in [[#funerr|Run-time errors during standard #function calls]]):
<p><b>Errors:</b>
</p>
if <i>%rc</i> present, set to corresponding number
and set <i>%tim</i> to MISSING;
if absent, cancel <var class="product">Fast/Unload</var> with return code 8.
<table>
<table>
<tr><th>4</th><td><i>fmt</i> is not a valid datetime format.</td></tr>
<tr><th>4</th>
<td><i>fmt</i> is not a valid datetime format.</td></tr>
</table>
</table>
<p><b>Notes</b></p>
In the event of a run-time error:
<ul>
<li>If you specified a <var class="term">%rc</var> in your #function call, it is set to the number above that corresponds to the error, and <var class="term">%tim</var> is set to <var>MISSING</var>. </li>
 
<li>If <var class="term">%rc</var> is absent, <var class="product">Fast/Unload</var> is cancelled with return code 8. </li>
</ul>
<p>
<b>Notes</b></p>
<ul>
<ul>
<li>The only difference between #TIME and #DATE is the default value
<li>The only difference between #TIME and #DATE is the default value

Revision as of 20:06, 6 February 2015


This wiki page lists the #functions provided with Fast/Unload. #Functions can be used as the expression in an assignment statement, as described in Assignment statement. Each #function is presented here with a brief phrase denoting its use, a short explanation, the form and types of its arguments and result (or "output value"), one or more examples, any error conditions, and special notes.

In some instances the behavior of a #function is compared to the behavior of a corresponding SOUL $function.

FUEL also contains program entities called "special variables" that might be initially mistaken for #functions. Similar to #functions in appearance and syntax, special variables are described in Special variables.

#Function argument types and evaluation

The arguments to a #function are FUEL entities, and their number and type varies with the #function. The maximum number of arguments in a $function call is 31 (the comma maximum is 30).

The types of the arguments, and some other items important in specifying how to use a #function, are as follows:

required argument If an argument to a #function is required, you must code an entity for it in the call. If you do not code an entity, a compilation error message will be issued and the unload is not performed.
optional argument If an argument to a #function is optional, you need not code an entity for it in the call. If you provide any arguments following it, then a comma must be used to indicate the missing argument; for example:

%P = #VERPOS(%S, %C, , 3)

The third argument, which is optional, has not been coded above.
omitted argument A particular #function may treat an omitted argument differently than a supplied argument that has the "MISSING" value or which has the null string value.
input argument If an argument to a #function is an input argument, you can specify any form of entity for the argument. The #function cannot change the value of an input argument.
argument with "MISSING" value If an argument has the MISSING value (a %variable or a field), the #function algorithm can detect this, although, except as noted, for all arguments of the standard #functions, this is the same as: passing the null string for a string argument, or passing zero for a numeric argument.

Since the numeric value of MISSING is zero, the MISSING value is not allowed for an argument that may not be zero. Also, the MISSING value is not allowed for some other #function arguments, such as the CENTSPAN argument and the first argument in the #Nx2DATE family (for example, #ND2DATE).

#function result The value of an expression that is a #function call is called the #function result. See the evaluation process below for an explanation of how this is set.
output argument In addition to the result, a #function can set additional values. This is provided by output arguments. If you supply an output argument, you must supply a %variable (leading minus sign is not allowed); if you supply an argument which is not a %variable, a compilation error message is issued and the unload will not execute.

The evaluation process, described below, explains how the value of an output argument is modified.

Note: Unless otherwise indicated:

  • All arguments shown in this documentation are required and are input arguments.
  • All input strings of the standard #functions are treated as non-DBCS, and all output strings have type of non-DBCS.

The process of evaluation of a #function call uses "call by copy/result" semantics, that is:

  1. The value of each argument entity is copied to a distinct intermediate variable which the #function can access (this is true of both input and output arguments in the current version, but in terms of the details of #function evaluation, what is important is that output arguments are copied).
  2. The value of the result is set to MISSING.
  3. During the execution of the #function, the argument values (input or output) can be accessed and output arguments can be modified, in the intermediate variables. The result can be modified or accessed by the #function; this is done directly in the %variable to which the #function result is assigned.
  4. After termination of the #function, the value of each intermediate variable corresponding to an output argument is copied to the %variable coded as the output argument.

Run-time errors during standard #function calls

The default approach for handling errors in calls to standard #functions is strict:

  • Fast/Unload treats most errors that the programmer can reasonably avoid as serious and terminates the run, if you have not provided for checking of the error.
  • At termination, Fast/Unload issues a message describing the nature of the error, the input program line number being executed and, if the error occurred during the execution of a #function, a dump of the current value of each #function argument.

Fast/Unload provides an optional output "return code" argument for some #functions. You can test the value set in this argument for errors in the value of the other arguments. Various non-zero values for the return code argument indicate error conditions; unless otherwise indicated, when one of these is set, the #function result is set to the MISSING value. If you omit the return code argument and there is an error in the value of an argument, the program is cancelled.

For example, since ONE WEEK isn't numeric, the following fragment:

%NEW_DT = #DATECHG('MM/DD/YY', '01/01/96', 'ONE WEEK',, %TST) IF %TST NE 0 THEN REPORT 'Error incrementing date:' AND %TST END IF

sets %NEW_DT to the MISSING value and produces the following line on the FUNPRINT data set:

Error incrementing date: 12

The following fragment:

%NEW_DT = #DATECHG('MM/DD/YY', '01/01/96', 'ONE WEEK')

causes Fast/Unload to immediately terminate, issuing an error message that a value is non-numeric, as shown in the following sample from FUNPRINT:

FUNL0053 Unload started. FUNL0116 Value is non-numeric or is out of range in line 203; Fast/Unload cancelled. FUNL0127 Fast/Unload cancelled unloading input record number 0 in file PROCFILE. FUNL0130 Fast/Unload cancelled during execution of line 203. FUNL0128 3 argument positions passed to #function; values : FUNL0129 01=MM/DD/YY. FUNL0129 02=01/01/96. FUNL0129 03=ONE WEEK.

Each #function description in this wiki page has a figure showing the error conditions for the #function and, if there is a "return code" argument, the non-zero values for it. Notice that, in general, there are no run-time errors for missing required arguments or too many arguments, because such checking is done at compile time.

#ABDUMP: End Fast/Unload with ABEND and dump

The #ABDUMP function accepts a numeric argument, and it causes the Fast/Unload job to terminate with user abend code equal to that argument, producing a dump.

%junk = #ABDUMP(ccode)

Where:

ccode Condition code for user ABEND; optional, defaults to 0.
%junk This #function does not return a value, but the FUEL syntax requires a place to store the #function result.

For example, the following fragment abends the job when the input record number is 12345:

IF #RECIN EQ 12345 %FOO = #ABDUMP(99) END IF

Notes

  • The purpose of this #function is to assist in problem diagnosis. Technical Support will direct you if ever it needs to be used.

This #function is new in Fast/Unload version 4.1.

#CONCAT: Concatenate strings

The #CONCAT function accepts two or more arguments, and it returns a string value that is the concatenation of all of its arguments.

%out = #CONCAT(stra, strb ...)

Where:

stra First input string (required).
strb Second input string (required).
... Additional input strings (optional). For versions of Fast/Unload prior to 4.3, total length of all input strings may not exceed 255.
%out Concatenation of inputs.

For example, the following fragment prints the line Bugs Bunny on the FUNPRINT data set:

%RABBIT = #CONCAT('Bugs', ' ', 'Bunny') REPORT %RABBIT

The Fast/Unload run is cancelled with a return code of 8 when:

  • Total length of input strings exceeds 255 and Fast/Unload version is less than 4.3.
  • Argument missing (see "Notes" below).

Notes

  • If the nth argument is present, where n is greater than 1, then the n-1st argument must be also be present. That is, the following results in a run-time error, because the third argument is absent:

    %X = #CONCAT(%A, %B, , %D)

  • See #CONCAT_TRUNC: Concatenate strings, allowing truncation if your version of Fast/Unload is earlier than 4.3 and if you cannot ensure that the total length of the #CONCAT input strings is less than 256.

#CONCAT_TRUNC: Concatenate strings, allowing truncation

The #CONCAT_TRUNC function accepts an optional output argument and two or more string arguments, and it returns a string value that is the concatenation of the string arguments, to a maximum of 255 bytes. It allows the total length of the string arguments to exceed 255, and it returns the total length of those arguments as the absolute value of the output argument, setting it to a negative value if the length exceeds 255.

%out = #CONCAT_TRUNC(%lenrc, stra, strb ...)

Where:

%lenrc Optional %variable, set to total length of stra, strb, ..., or to the negative of that length, if it exceeds 255.
stra First input string (required).
strb Second input string (required).
... Additional input strings (optional).
%out Concatenation of stra, strb, ..., or the first 255 bytes of that concatenation, if it exceeds 255.

For example, the following fragment

%FOO = #CONCAT_TRUNC(%LEN, 'Hello,', ' ', 'World!') REPORT %FOO AND 'length is' AND %LEN

prints the following line on the FUNPRINT dataset:

Hello, World! length is 13

The Fast/Unload run is cancelled with a return code of 8 when:

  • Argument missing (see "Notes" below).

Notes

  • If the nth argument is present, where n is greater than 2, then the n-1st argument must be also be present. That is, the following will result in a run-time error, because the fourth argument is absent:

    %X = #CONCAT_TRUNC(%L, %A, %B, , %D)

  • See #CONCAT: Concatenate strings if you want Fast/Unload to terminate if the total length of the input strings is greater than 255.

This #function is new in Fast/Unload version 4.0.

#C2X: Convert character string to hex representation

The #C2X function expects one required argument, and it returns a string containing the hexadecimal representation of the value in the argument string. Each byte position of the input string is converted to two characters in the result string, using only the characters 0-9 and A-F.

%hex = #C2X(str)

Where:

strString to convert. Must be 127 bytes or less.
%hexString containing hex digits.

For example, the code fragment

%JUNK = #C2X('ABabc') REPORT 'X''' WITH %JUNK WITH ''''

would produce X'C1C2818283', while

%JUNK = #C2X(' 1 ') REPORT 'X''' WITH %JUNK WITH ''''

would produce X'40F140'.

The Fast/Unload run is cancelled with a return code of 8 when:

  • Length of str exceeds 127.

Notes

  • The inverse of this #function is #X2C.
  • The SOUL $C2X function allows a maximum input length of 126, and an error causes the null string to be returned.
  • The $IHexA function ignores any input characters after the first 127.

#DATE: Current date and/or time

The #DATE function accepts an optional datetime format argument and an optional output return code argument and returns the current date and time in the specified (or defaulted) format.

%dat = #DATE(fmt, %rc)

Where:

fmt Optional format for returned date, default is YYYY-MM-DD.
%rc Optional output return code variable.
%dat Datetime string with indicated format.

For example, the following fragment stores the current date in the last occurrence of the REORG_DATE field:

ADD REORG_DATE = #DATE()

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 fmt is not a valid datetime format.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %dat is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

  • The only difference between #TIME and #DATE is the default value for fmt.
  • The default format returned by the SOUL $Date and $Sir_Date functions is "YY–MM–DD"; the default format returned by #DATE is "YYYY–MM–DD".
  • Datetime formats explains valid datetime formats.

#DATECHG: Add some days to datetime

The #DATECHG function adds a specified number of days to an input datetime, returning the incremented datetime. It requires a datetime format argument, a datetime value, and a signed number of days. It accepts an optional CENTSPAN value for interpreting datetimes with two digit years. The incremented datetime is returned in the same format as the input datetime. An optional output return code argument allows the FUEL program to intercept error conditions.

%odat = #DATECHG(fmt, dat, n, span, %rc)

Where:

fmtFormat of dat and %odat.
datDatetime string.
nNumber of days to add to dat.
spanOptional CENTSPAN value.
%rcReturn code variable (optional, output).
%odatSet to dat plus n days.

For example, the following fragment prints the date one week after the run date on the FUNPRINT dataset:

%X = #DATE('DAY Month, YYYY') %X = #DATECHG('DAY Month, YYYY', %X, 7) REPORT %X

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 fmt is not a valid datetime format.
8 dat does not match fmt or result date out of range.
12 n is not a number or span is invalid.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %odat is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

  • Datetime formats explains valid datetime formats and valid dates.
  • CENTSPAN explains CENTSPAN arguments.
  • The SOUL $DateChg function does not provide a call-level argument for interpreting two-digit years, the thread-level CENTSPLT/DEFCENT parameters are used instead.
  • The SOUL $Sir_Datechg function provides a CENTSPAN argument in the same fashion as #DATECHG.

#DATECHK: Check if datetime matches format

The #DATECHK function verifies that a specific datetime value is valid for a given datetime format. It requires a datetime format argument and a datetime value. It accepts an optional CENTSPAN value for interpreting datetimes with two digit years. An optional output return code argument allows the FUEL program to intercept error conditions. The #DATECHK function returns 1 if all arguments are valid and consistent, else 0 if all arguments are valid except for the date.

%tst = #DATECHK(fmt, dat, span, %rc)

Where:

fmt Datetime format string for dat.
dat Datetime string to be validated against fmt.
span Optional CENTSPAN value.
%rc Return code variable (optional, output).
%tst Set to 1 if dat matches fmt, 0 otherwise.

For example, the following fragment prints the string Bad on the FUNPRINT dataset:

%X = #DATECHK('DAY Month, YYYY', '30 February, 1997') IF %X = 1 THEN REPORT 'Good' ELSE REPORT 'Bad' END IF

Non-zero values of %rc, or terminating conditions, are shown in the following figure (see the discussion in Run-time errors during standard #function calls).

4fmt is not a valid datetime format.
8dat does not match fmt or result date out of range.
12n is not a number or span is invalid.
4 Fmt is not a valid datetime format.
12 span is an invalid CENTSPAN value.

Notes

  • Datetime formats explains valid datetime formats and valid dates.
  • CENTSPAN explains CENTSPAN arguments.
  • The SOUL $DateChk function does not provide a call-level argument for interpreting two-digit years, the thread-level CENTSPLT/DEFCENT parameters are used instead.
  • The SOUL $Sir_DateChk function provides a CENTSPAN argument in the same fashion as #DATECHK.

#DATECNV: Convert datetime to different format

The #DATECNV function converts a datetime value from one datetime format to another datetime format. It requires an input datetime value, a corresponding input datetime format string, and an output datetime format string. It accepts an optional CENTSPAN value for interpreting datetimes with two digit years. An optional output return code argument allows the FUEL program to intercept error conditions. The #DATECNV function returns the input datetime value in the format specified by the output datetime format string.

%odat = #DATECNV(infmt, outfmt, dat, span, %rc)

Where:

infmt Datetime format string for dat.
outfmt Datetime format string for %odat.
dat Input datetime string.
span Optional CENTSPAN value.
%rc Return code variable (optional, output).
%odat Set to the value of dat, converted to output format.

For example, this prints the string 19970101 on the FUNPRINT dataset:

%X = #DATECNV('YYMMDD', 'YYYYMMDD', '970101', 1950) REPORT %X

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 infmt or outfmt is not a valid datetime format.
8 dat does not match infmt.
12 span is an invalid CENTSPAN value.
16 Converted datetime value out of range for %outfmt.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %odat is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

#DATEDIF: Difference between two dates

The #DATEDIF function subtracts a second date from a first date and returns the difference in days, ignoring time portions for both dates. It requires a first datetime format string, a first datetime value, and a second datetime value. It accepts an optional second datetime format string and an optional CENTSPAN value for interpreting datetimes with two digit years. An optional output return code argument allows the FUEL program to intercept error conditions.

%dif = #DATEDIF(fmta, data, fmtb, datb, - span, %rc)

Where:

fmtaDatetime format string for data.
dataFirst datetime string.
fmtbOptional second datetime format string for datb. Default is to use fmta.
datbSecond datetime string.
spanOptional CENTSPAN value.
%rcReturn code variable (optional, output).
%difSet to the number of days obtained by subtracting the second date from the first date, ignoring any time components of both dates.

For example, the following fragment prints the string 7 days on the FUNPRINT dataset:

%X = #DATEDIF('YYMMDD', '970308', , '970301') REPORT %X AND 'days'

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 fmta or fmtb is not a valid datetime format.
8 data does not match fmta, or datb does not match fmtb.
12 span is an invalid CENTSPAN value.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %dif is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

  • Time is ignored in the subtraction.
  • In the example above, even though the input dates have 2-digit years, there is no need for a span argument, since the dates are in the same year.
  • As in the SOUL $DATEDIF function, the single span argument is used for both dates; if it is necessary to get the difference between two dates which both have 2-digit years and are in different 100-year windows, you must first use #DATECNV to convert one of them to some 4-digit year format.
  • Datetime formats explains valid datetime formats and valid dates.
  • CENTSPAN explains CENTSPAN arguments.

#DATEFMT: Validate datetime format string

The #DATEFMT function is used to validate a datetime format string. It requires a datetime format string. It returns a value of 1 if the datetime format string is valid, else it returns a value of 0.

%tst = #DATEFMT(fmt)

Where:

fmtDatetime format string to be validated.
%tstSet to 1 if fmt is a valid format string, else set to 0.

For example, the following fragment prints the string Good on the FUNPRINT dataset:

%X = #DATEFMT('CYYDDDHHMISSXXX') IF %X = 1 THEN REPORT 'Good' ELSE REPORT 'Bad' END IF

This #function has no terminating conditions.

Notes

#DATE2N: Convert datetime string to number of seconds*300

The #DATE2N function converts a datetime value in a string format into a numeric form that is the corresponding number of seconds*300 since January 1, 1900. It requires a datetime value string and a corresponding datetime format string. It accepts an optional CENTSPAN value for interpreting datetimes with two digit years. An optional output return code argument allows the FUEL program to intercept error conditions.

%num = #DATE2N(dat, fmt, span, %rc)

Where:

datDatetime value string.
fmtDatetime format string for dat.
spanOptional CENTSPAN value.
%rcReturn code variable (optional, output).
%numSet to the number of seconds*300 since 1 Jan 1900 12:00 AM that corresponds to the datetime value of dat.

For example, the following fragment prints the value BEFORE on the FUNPRINT dataset:

IF #DATE2N('121494', 'MMDDYY') < - #DATE2N('040195', 'MMDDYY') THEN REPORT 'BEFORE' END IF

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 fmt is not a valid datetime format.
8 dat does not match fmt or result date out of range.
12 span is an invalid CENTSPAN value.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %num is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

  • Values returned by #DATE2N often exceed the range that can be represented in a 4-byte integer, so you should probably avoid storing the value in a BINARY or FLOAT4 field.
  • Dates prior to 1 January 1900 will return a negative number.
  • The inverse of this #function is #N2DATE.
  • Datetime formats explains valid datetime formats and valid dates.
  • CENTSPAN explains CENTSPAN arguments.

#DATE2ND: Convert datetime string to number of days

The #DATE2ND function converts a datetime value in a string format into a numeric form that is the corresponding number of days since January 1, 1900. It requires a datetime value string and a corresponding datetime format string. It accepts an optional CENTSPAN value for interpreting datetimes with two digit years. An optional output return code argument allows the FUEL program to intercept error conditions.

%num = #DATE2ND(dat, fmt, span, %rc)

Where:

datDatetime value string.
fmtDatetime format string for dat.
spanOptional CENTSPAN value.
%rcReturn code variable (optional, output).
%numSet to the number of days since 1 Jan 1900 12:00 AM that corresponds to the datetime value of dat.

For example, the following fragment prints the value BEFORE on the FUNPRINT dataset:

IF #DATE2ND('121494', 'MMDDYY') < - #DATE2ND('040195', 'MMDDYY') THEN REPORT 'BEFORE' END IF

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 fmt is not a valid datetime format.
8 dat does not match fmt or result date out of range.
12 span is an invalid CENTSPAN value.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %num is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

  • Dates prior to 1 January 1900 will return a negative number.
  • The inverse of this #function is #ND2DATE.
  • Datetime formats explains valid datetime formats and valid dates.
  • CENTSPAN explains CENTSPAN arguments.

#DATE2NM: Convert datetime string to number of milliseconds

The #DATE2NM function converts a datetime value in a string format into a numeric form that is the corresponding number of milliseconds since January 1, 1900. It requires a datetime value string and a corresponding datetime format string. It accepts an optional CENTSPAN value for interpreting datetimes with two digit years. An optional output return code argument allows the FUEL program to intercept error conditions.

%num = #DATE2NM(dat, fmt, span, %rc)

Where:

datDatetime value string.
fmtDatetime format string for dat.
spanOptional CENTSPAN value.
%rcReturn code variable (optional, output).
%numSet to the number of milliseconds since 1 Jan 1900 12:00 AM that corresponds to the datetime value of dat.

For example, the following fragment prints the value 'BEFORE' on the FUNPRINT dataset:

IF #DATE2NM('121494', 'MMDDYY') < - #DATE2NM('040195', 'MMDDYY') THEN REPORT 'BEFORE' END IF

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 fmt is not a valid datetime format.
8 dat does not match fmt or result date out of range.
12 span is an invalid CENTSPAN value.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %num is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

  • Values returned by #DATE2NM will often exceed the range that can be represented in a 4-byte integer, so you should probably avoid storing the value in a BINARY or FLOAT4 field.
  • Dates prior to 1 January 1900 will return a negative number.
  • The inverse of this #function is #NM2DATE.
  • Datetime formats explains valid datetime formats and valid dates.
  • CENTSPAN explains CENTSPAN arguments.

#DATE2NS: Convert datetime string to number of seconds

The #DATE2NS function converts a datetime value in a string format into a numeric form that is the corresponding number of seconds since January 1, 1900. It requires a datetime value string and a corresponding datetime format string. It accepts an optional CENTSPAN value for interpreting datetimes with two digit years. An optional output return code argument allows the FUEL program to intercept error conditions.

%num = #DATE2NS(dat, fmt, span, %rc)

Where:

datDatetime value string.
fmtDatetime format string for dat.
spanOptional CENTSPAN value.
%rcReturn code variable (optional, output).
%numSet to the number of seconds since 1 Jan 1900 12:00 AM that corresponds to the datetime value of dat.

For example, the following fragment prints the value 'BEFORE' on the FUNPRINT dataset:

IF #DATE2NS('121494', 'MMDDYY') < - #DATE2NS('040195', 'MMDDYY') THEN REPORT 'BEFORE' END IF

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 fmt is not a valid datetime format.
8 dat does not match fmt or result date out of range.
12span is an invalid CENTSPAN value.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %num is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

  • Values returned by #DATE2NS will often exceed the range that can be represented in a 4-byte integer, so you should probably avoid storing the value in a BINARY or FLOAT4 field.
  • Dates prior to 1 January 1900 will return a negative number.
  • The inverse of this #function is #NS2DATE.
  • Datetime formats explains valid datetime formats and valid dates.
  • CENTSPAN explains CENTSPAN arguments.

#DEBLANK: Remove leading and trailing blanks from substring

The #DEBLANK function extracts a substring (starting at pos for length len) and returns it, with leading and trailing blanks removed. It requires a string to be deblanked. It accepts an optional starting position within the string to be deblanked, and an optional length.

%out = #DEBLANK(str, pos, len)

Where:

strString to be deblanked; required.
posOptional position within str to start deblanking. Must be numeric greater than or equal to 1, default is 1.
lenOptional length, starting at pos of string to deblank. Must be numeric greater than or equal to 0, default is the remainder of the input string.
%outSet to the substring within str, starting at pos for length len; leading and trailing blanks are then removed from this substring. Intermediate blanks within the substring are not affected.

Examples:

#DEBLANK(' ABC ') returns 'ABC' #DEBLANK(' ABC ', 1, 2) returns 'A' #DEBLANK(' A BC ', 2, 4) returns 'A B'

The Fast/Unload run is cancelled with a return code of 8 when:

  • pos is not numeric, or less than 1.
  • len is not numeric, or less than 0.

Notes

This #function is new in Fast/Unload version 4.0.

#DELWORD: Remove blank-delimited words from string

The #DELWORD function removes one or more blank-delimited words from a string. It requires an input string and a word number within the string. It accepts an optional number of words to delete.

%out = #DELWORD(str, word, count)

Where:

strString containing words to be deleted; required.
wordNumber of first word within str to remove; required. Must be numeric greater than or equal to 1.
countOptional count of words to remove. Must be numeric greater than or equal to 0, default is the remainder of words in the input string.
%outSet to str with the substring removed which starts with the first character of word number word and ends with the last blank character before word number word+count.

Examples:

#DELWORD('A B C', 1) returns '' #DELWORD('A B C', 2) returns 'A ' #DELWORD(' A B C ', 3) returns ' A B ' #DELWORD('A B C', 4) returns 'A B C' #DELWORD(' ', 1) returns ' ' #DELWORD('A B C', 1, 1) returns 'B C' #DELWORD('A B C', 1, 4) returns '' #DELWORD('A B C', 2, 2) returns 'A '

The Fast/Unload run is cancelled with a return code of 8 when:

  • word is not numeric, or less than 1.
  • count is not numeric, or less than 0.

This #function is new in Fast/Unload version 4.0.

#FIND: Word position of one word sequence within another

The #FIND function determines the word position, within a string of blank-delimited words, of one or more blank-delimited words. It requires an input string to search within and an input sequence of words to search for.

%pos = #FIND(haystack, words)

Where:

haystackString containing words to be searched within; required.
wordsString containing one or more blank-delimited words to find; required.
%posSet to the word position, within haystack, of the first word of a sequence of words that matches the sequence in words. If words is not found, %pos is set to 0.

Examples:

#FIND('A B C', 'B') returns 2 #FIND('A B C', 'D') returns 0 #FIND('A B C', 'A B') returns 1 #FIND(' ', ' ') returns 0 #FIND('A B', 'A B C') returns 0 #FIND('B B B A', 'B B A') returns 2 #FIND('A A B', ' A B ') returns 2 #FIND(' A A B ', 'A B') returns 2

This #function has no cancelling conditions.

Notes

  • Multiple blanks in both input arguments are ignored.
  • This #function is similar to the #ONEOF function (#ONEOF: See if string is in delimited list of strings). Note, however, that both #FIND and #ONEOF are inferior to the SELECT statement, when the only purpose is to test whether an entity has one of several values (see SELECT entity).

This #function is new in Fast/Unload version 4.0.

#FLOAT8: Get 8-byte float, padding 4-byte input with 0

The #FLOAT8 function accepts a numeric argument, and it returns the value of the argument as an 8-byte floating point value. If the argument is a 4-byte floating point value, then the conversion is done by appending binary zeroes; otherwise, it is done by the normal FUEL conversion to an 8-byte floating point value.

%out = #FLOAT8(in)

Where:

%outSet to 8-byte floating point value of input argument.
inNumeric input value.

Notes

  • See Fast/Unload floating point arithmetic and numeric conversion for a specification of conversions to floating point values.
  • Numeric operations in FUEL and in SOUL are based on decimal, not binary, interpretation of floating point values, so this #function is seldom used.

    However, #FLOAT8 may be useful in unusual situations, in particular to perform a file reorganization that expands a FLOAT LEN 4 field to a FLOAT LEN 8 field, using the "raw" floating point conversion (such as can be done in a structured file reorganization using the X'0080' mode bit in FLOD).

    For example, if field FLT is defined in the input as FLOAT LEN 4, and you want to convert it to a FLOAT LEN 8 or FLOAT LEN 16 in a UAI/LAI file reorganization in such a way that the new field's values consist of the old ones with binary zeros added, you can use the following:

    OPEN PRODFILE UAI FOR EACH RECORD FOR I FROM 1 TO FLT(#) CHANGE FLT(I) = #FLOAT8(FLT(I)) END FOR UNLOAD END FOR

    In this example, if the input value of FLT is 411028F6, which is the closest 4-byte floating point value to the decimal value 1.01, it is converted on output to 411028F600000000, which SOUL will display as 1.01000022888184 (demonstrating that #FLOAT8 is only to be used in special circumstances).

    A "normal" UAI/LAI conversion of 411028F6 to a FLOAT LEN 8 field would be to the hexadecimal value 411028F5C28F5C28, which SOUL will display as 1.01.

This #function is new in Fast/Unload version 4.3.

#INDEX: Position of second string within first

The #INDEX function locates the first occurrence, if any, of a search string (needle) within a searched string (haystack). It requires a string to be searched and accepts an optional string to locate. It accepts an optional starting position within the string to be searched. #INDEX returns either the starting position within the searched string of the first occurrence of the string to be located (starting at the specified position), or 0.

%opos = #INDEX(haystack, needle, pos)

Where:

haystackString to be searched; required.
needleString to be located, may be omitted.
posOptional starting position within haystack for search. Must be numeric greater than or equal to 1, default is 1.
%oposSet to starting position of first occurrence of needle within haystack, with search starting at pos, else 0 if not found or if either string length is zero.

Examples:

#INDEX('123xy', '23') returns 2 #INDEX('123xy', '45') returns 0 #INDEX('12312', '12', 2) returns 4 #INDEX('123xy', '23', 3) returns 0

The Fast/Unload run is cancelled with a return code of 8 when:

  • pos is not numeric, or less than 1.

Notes

  • If needle or haystack is the null string, 0 is returned.

#LEFT: Initial substring, followed by pad characters to specified length

The #LEFT function returns a padded initial substring from a source string. It expects a source string argument and a numeric length of the output string. It accepts an optional pad character. #LEFT returns the string that begins at the first position of the source string and is of the specified output length. If the output length is less than or equal to the length of the source string, the first length characters of the source are returned. Otherwise, the source string is padded to the output length by following it with sufficient copies of the pad character.

%out = #LEFT(str, len, pad)

Where:

strSource string.
lenLength of the output string. Must be numeric >= 0.
padOptional pad character. Default is blank. If supplied, must be a string of length 1.
%outIf len is greater then #LEN(str):
  • all of str, followed by len - #LEN(str) copies of the pad character

Otherwise:

  • substring from str starting at position 1 with length len

For example:

#LEFT('ABC', 1) returns 'A' #LEFT('ABC', 3) returns 'ABC' #LEFT('ABC', 4) returns 'ABC ' #LEFT(4.6, 4, 0) returns '4.60'

The Fast/Unload run is cancelled with a return code of 8 when:

  • Len not numeric, or less than 0.
  • Pad supplied, and length not 1.

Notes

This #function is new in Fast/Unload version 4.0.

#LEN: Length of string

The #LEN function returns the length of a string, in number of bytes. It expects a required string argument and returns a numeric result.

%len = #LEN(str)

Where:

strString whose length you want to know. For Fast/Unload version 4.3 and later. str may be longer than 255 bytes.
%lenSet to length of str.

For example, the following fragment prints the string Length: 13 on the FUNPRINT dataset:

%X = #LEN('Hello, world!') REPORT 'Length:' AND %X

This #function has no cancelling conditions.

#LOWCASE: Change uppercase letters of string to lowercase

The #LOWCASE function returns a copy of the input string, with all uppercase EBCDIC letters changed to the corresponding lowercase letters. It expects a source string argument.

%out = #LOWCASE(str)

Where:

strSource string, required.
%outCopy of str, with all uppercase EBCDIC letters changed to their lowercase EBCDIC equivalents.

For example:

#LOWCASE('?abc') returns '?abc' #LOWCASE('?ABC') returns '?abc' #LOWCASE('') returns ''

This #function has no cancelling conditions.

This #function is new in Fast/Unload version 4.0.

#ND2DATE: Convert number of days to datetime string

The #ND2DATE function converts a numeric datetime value expressed as the number of days since January 1, 1900 into a datetime string value according to a specified datetime format string. It requires a datetime numeric value and a datetime format string. An optional output return code argument allows the FUEL program to intercept error conditions.

%dat = #ND2DATE(datn, fmt, %rc)

Where:

datnNumeric datetime value expressed as the number of days since 1 Jan 1900 12:00 AM. This argument may not have the MISSING value.
fmtDatetime format string to use for creating %dat.
%rcReturn code (optional, output)
%datSet to datetime string value, in format specified by fmt, corresponding to datn.

For example, the following fragment prints the string 07/31/84 on the FUNPRINT dataset:

%X = #DATE2ND('8407301230', 'YYMMDDHHMI') %X = %X + 1 /* Add 1 day %X = #ND2DATE(%X, 'MM/DD/YY') REPORT %X

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 fmt is not a valid datetime format.
8 datn is out of range for fmt.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %dat is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

  • The inverse of this #function is #DATE2ND.
  • Datetime formats explains valid datetime formats and valid dates.

#NM2DATE: Convert number of milliseconds to datetime string

The #NM2DATE function converts a numeric datetime value expressed as the number of milliseconds since January 1, 1900 into a datetime string value according to a specified datetime format string. It requires a datetime numeric value and a datetime format string. An optional output return code argument allows the FUEL program to intercept error conditions.

%dat = #NM2DATE(datn, fmt, %rc)

Where:

datn Numeric datetime value expressed as the number of milliseconds since 1 Jan 1900 12:00 AM. This argument may not have the MISSING value.
fmt Datetime format string to use for creating %dat.
%rc Return code (optional, output)
%dat Set to datetime string value, in format specified by fmt, corresponding to datn.

For example, the following fragment prints the string 07/31/84 on the FUNPRINT dataset:

%X = #DATE2NM('8407301230', 'YYMMDDHHMI') %X = %X + 1000 * 60 * 60 * 15 /* Add 15 hours %X = #NM2DATE(%X, 'MM/DD/YY') REPORT %X

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 fmt is not a valid datetime format.
8 datn is out of range for fmt.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %dat is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

#NS2DATE: Convert number of seconds to datetime string

The #NS2DATE function converts a numeric datetime value expressed as the number of seconds since January 1, 1900 into a datetime string value according to a specified datetime format string. It requires a datetime numeric value and a datetime format string. An optional output return code argument allows the FUEL program to intercept error conditions.

%dat = #NS2DATE(datn, fmt, %rc)

Where:

datnNumeric datetime value expressed as the number of seconds since 1 Jan 1900 12:00 AM. This argument may not have the MISSING value.
fmtDatetime format string to use for creating %dat.
%rcReturn code (optional, output)
%datSet to datetime string value, in format specified by fmt, corresponding to datn.

For example, the following fragment prints the string 07/31/84 on the FUNPRINT dataset:

%X = #DATE2NS('8407301230', 'YYMMDDHHMI') %X = %X + 60 * 60 * 15 /* Add 15 hours %X = #NS2DATE(%X, 'MM/DD/YY') REPORT %X

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 fmt is not a valid datetime format.
8 datn is out of range for fmt.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %dat is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

  • The inverse of this #function is #DATE2NS.
  • Datetime formats explains valid datetime formats and valid dates.

#NUM2STR: Convert number to string with decimal point

The #NUM2STR function converts a number to a string with an integer part, followed by an optional decimal point and decimal fraction digits, with control over the number of integer and fraction digits, padding, and rounding. It requires a numeric value. Optional arguments specify the width of the resulting integer digits area, the width of the resulting fraction digits area, rounding vs. truncation and fixed width vs. variable/minimal width integer area, and the leading pad character for the integer digits area.

The final optional argument is an output argument whose absolute value is set to the number of integer characters (after leading zeroes are stripped) in the number, plus 1 if the number is negative. Specifying this argument allows the Fast/Unload program to continue if the first argument cannot be represented by the result, either as a result of truncation of the high-order integer digits, or because the first argument can not be converted to a number.

%str = #NUM2STR(num, intw, fracw, opt, pad, %intlen)

Where:

numInput number to be converted.
intwNumber of characters used to express the integer portion and leading minus sign, if any, of num. Optional, must be 0 or more if specified. If omitted, only the characters needed are used to represent the integer part: a leading minus sign, if num is negative, is followed by the integer portion with leading zeroes stripped (except if the integer portion is zero, a single digit 0 is used).

If the fourth argument (opt) contains the letter 'V', intw is the minimal width used for the integer part, and the number of characters needed is used if that exceeds intw.

fracwNumber of digits used to express the fraction portion of num. Optional, must be 0 or more if specified. If this argument is omitted:
  • only the characters needed are used to represent the fraction part
  • if the fraction part is 0, no decimal point occurs in the result
  • if the fraction part is not 0, trailing zeroes are removed
%optOne or two characters, optional, with one choice from either of the following two pairs:
  • R (round) or T (trunc)
    RRound up the final digit of the result, if the most significant discarded fraction digit is 5 or more. This is the default.
    TFinal digit of result unaffected by any discarded fraction digits.

    This character has no meaning if argument three (fracw) is omitted, since in that case no fraction digits are discarded.

  • F (fixed width) or V (variable width)
    F The integer portion of the result is fixed width; that is, as many characters are used for the integer (and leading minus sign) as the value of argument two (intw). This is the default.
    V The integer portion of the result is variable/minimal width. That is, if w characters are needed for the integer (and leading minus sign) with leading zeroes removed, then w characters are used for the integer if w>intw; intw characters are used otherwise.

    This character has no meaning if argument two (intw) is omitted, since in that case exactly the characters needed for the integer are used.

padThe leading pad character used to fill the integer portion to the width specified by argument two (intw). Optional, must be one character, defaults to blank. If the pad character is blank, a leading minus sign follows any blank pad characters; otherwise a leading minus sign precedes any pad characters.
%intlenSet to the value len, where the absolute value of len is the number of characters needed for the integer part of the first argument (num), with any leading zeroes stripped (or one zero if the integer part is zero), including one additional character if num is negative.

If %intlen is returned with a negative value, that indicates truncation of the integer part of num; this occurs if all of the following conditions hold:

  • Argument two (intw) is specified.
  • The absolute value of len is greater than intw.
  • Intw>0, or num<0, or num>=1.
  • Argument four (opt) does not contain the letter 'V'.

If num cannot be converted to a number, then len is returned as zero, and the result (value of %str) of #NUM2STR is the MISSING value.

Otherwise, len is positive.

As these rules indicate, %intlen, if supplied, allows the Fast/Unload program to continue when the first argument is non-numeric or the integer part is truncated in the result. If either of these conditions occur and %intlen is omitted, the Fast/Unload program is cancelled.

Examples:

%N_HUMAN = 6 * 1000 * 1000 * 1000 #NUM2STR(%N_HUMAN) -> "6000000000"

%PI = 3.14159265 #NUM2STR(%PI) -> "3.14159265" #NUM2STR(%PI, , 3) -> "3.142" #NUM2STR(%PI, , 3, 'T') -> "3.141"

%PCT7_5 = 7.5/100 #NUM2STR(%PCT7_5) -> "0.075" #NUM2STR(%PCT7_5, , 4) -> "0.0750" #NUM2STR(%PCT7_5, 0, , , '*', %W) -> ".075" with ... %W = 1 #NUM2STR(%PCT7_5, 2, , , '*', %W) -> "*0.075" with ... %W = 1

%NEGP7_5 = - %PCT7_5 #NUM2STR(%NEGP7_5) -> "-0.075" #NUM2STR(%NEGP7_5, 1, , , '*') -> "-.075" #NUM2STR(%NEGP7_5, 2, , , '*') -> "-0.075" #NUM2STR(%NEGP7_5, 3, , , '*') -> "-*0.075" #NUM2STR(%NEGP7_5, 3, , , ' ') -> " -0.075"

%TBIL = 6.025 %VISA = 18.5 #NUM2STR(%TBIL, 1, 3, , '0') -> "6.025" #NUM2STR(%VISA, 1, 3, , '0', %W) -> "8.500" with ... %W = -2 #NUM2STR(%VISA, 1, 3, 'V', '0', %W) -> "18.500" with ... %W = 2

%BAD = 'PIZZA' #NUM2STR(%BAD, , , , , %W) -> MISSING value with ... %W = 0

Errors

The possible non-zero values of %intlen, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

-N N is the length required for the integer part of num, and n>intw and opt does not contain the letter "V".
0 Num can not be converted to a numeric value (%str is set to MISSING).
** Negative or non-numeric value for intw or fracw (this error always cancels Fast/Unload, regardless of the presence of %intlen).
** Invalid character in opt (this error always cancels Fast/Unload, regardless of the presence of %intlen).
** Length of pad not 1 (this error always cancels Fast/Unload, regardless of the presence of %intlen).

In the event of a run-time error:

  • If you specified a %intlen in your #function call, it is set to the return code above that corresponds to the error.
  • If %intlen is absent, Fast/Unload is cancelled with return code 8.

Note: If you are creating a string to place in the Fast/Unload output file, you can also use the PUT with the AS STRING or AS DECIMAL clauses (see PUT).

One difference between #NUM2STR and PUT AS STRING or PUT AS DECIMAL is that all conversion of fractional values to fixed width output formats in the PUT statement causes low order fraction digits to be dropped without rounding, but #NUM2STR offers rounding of dropped low order digits.

This #function is new in Fast/Unload version 4.0.

#N2DATE: Convert number of seconds*300 to datetime string

The #N2DATE function converts a numeric datetime value expressed as the number of seconds*300 since January 1, 1900 into a datetime string value according to a specified datetime format string. It requires a datetime numeric value and a datetime format string. An optional output return code argument allows the FUEL program to intercept error conditions.

%dat = #N2DATE(datn, fmt, %rc)

Where:

datnNumeric datetime value expressed as 300 times the number of seconds since 1 Jan 1900 12:00 AM. This argument may not have the MISSING value.
fmtDatetime format string to use for creating %dat.
%rcReturn code (optional, output)
%datSet to datetime string value, in format specified by fmt, corresponding to datn.

For example, the following fragment prints the string 07/31/84 on the FUNPRINT dataset:

%X = #DATE2N('8407301230', 'YYMMDDHHMI') %X = %X + 300 * 60 * 60 * 15 /* Add 15 hours %X = #N2DATE(%X, 'MM/DD/YY') REPORT %X

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 fmt is not a valid datetime format.
8 datn is out of range for fmt.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %dat is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

  • The inverse of this #function is #DATE2N.
  • Datetime formats explains valid datetime formats and valid dates.

#ONEOF: See if string is in delimited list of strings

The #ONEOF function determines whether a string is found in a delimited list of strings. It requires an input string to search for and a delimited list of strings to search within. It accepts an optional delimiter character, which is used to separate the strings in the list.

%test = #ONEOF(str, list, delim)

Where:

strString to find; required.
listString containing words to be searched within; required.
delimCharacter used to separate strings in list; optional. Default is semi-colon (;).
%testSet to 1 if str is one of the strings in list, delimited by delim. Otherwise, %test is set to 0.

Examples:

#ONEOF('HOW', 'HOW NOW', ' ') returns 1 #ONEOF('NOW', 'HOW;NOW') returns 1 #ONEOF('ABC', 'ABC') returns 1 #ONEOF('', 'HOW;;NOW') returns 1 #ONEOF('', ';HOW;NOW') returns 1 #ONEOF('', 'HOW;NOW;') returns 1 #ONEOF('', 'HOW;NOW') returns 0 #ONEOF('NO', 'HOW;NOW') returns 0 #ONEOF('HOW;NOW', 'HOW;NOW') returns 0 #ONEOF('', '') returns 0

The Fast/Unload run is cancelled with a return code of 8 when:

  • Delim supplied, and length not 1.

Notes

  • If argument 1 (str) contains delim, 0 is returned.
  • If argument 2 (list) is the null string, 0 is returned.
  • If argument 1 (str) is the null string, 1 is returned if, and only if, there is a leading, trailing, or two adjacent copies of, delim.
  • This #function is similar to the #FIND function (#FIND: Word position of one word sequence within another). Note, however, that both #FIND and #ONEOF are inferior to the SELECT statement, when the only purpose is to test whether an entity has one of several values (see SELECT entity).

This #function is new in Fast/Unload version 4.0.

#PAD: Final substring, preceded by pad characters to specified length

The #PAD function returns a padded final substring from a source string. It expects a source string argument and a numeric length of the output string. It accepts an optional pad character. #PAD returns the string that ends at the last position of the source string and is of the specified output length. If the output length is less than or equal to the length of the source string, the last length characters of the source are returned. Otherwise, the source string is padded to the output length by preceding it with sufficient copies of the pad character.

%out = #PAD(str, pad, len)

Where:

strSource string.
padOptional pad character. Default is blank. If supplied, must be a string of length 1.
lenLength of the output string. Must be numeric >= 0.
%outIf len is greater then #LEN(str):
  • len - #LEN(str) copies of the pad character, followed by all of str

Otherwise:

  • substring from str starting at position #LEN(str) - len+1 with length len

For example:

#PAD('ABC', , 0) returns '' #PAD('ABC', , 1) returns 'C' #PAD('ABC', , 3) returns 'ABC' #PAD('ABC', , 4) returns ' ABC' #PAD(456, 0, 4) returns '0456'

The Fast/Unload run is cancelled with a return code of 8 when:

  • Len not numeric, or less than 0.
  • Pad supplied, and length not 1.

Notes

This #function is new in Fast/Unload version 4.0.

#PADR: Initial substring, followed by pad characters to specified length

The #PADR function returns a padded initial substring from a source string. It expects a source string argument and a numeric length of the output string. It accepts an optional pad character. #PADR returns the string that begins at the first position of the source string and is of the specified output length. If the output length is less than or equal to the length of the source string, the first length characters of the source are returned. Otherwise, the source string is padded to the output length by following it with sufficient copies of the pad character.

%out = #PADR(str, pad, len)

Where:

strSource string.
padOptional pad character. Default is blank. If supplied, must be a string of length 1.
lenLength of the output string. Must be numeric >= 0.
%outIf len is greater then #LEN(str):
  • all of str, followed by len - #LEN(str) copies of the pad character

Otherwise:

  • substring from str starting at position 1 with length len

For example:

#PADR('ABC', , 1) returns 'A' #PADR('ABC', , 3) returns 'ABC' #PADR('ABC', , 4) returns 'ABC ' #PADR(4.6, 0, 4) returns '4.60'

The Fast/Unload run is cancelled with a return code of 8 when:

  • Len not numeric, or less than 0.
  • Pad supplied, and length not 1.

Notes

This #function is new in Fast/Unload version 4.0.

#REVERSE: Get reverse of string

The #REVERSE function returns a copy of the input string, with the order of the bytes reversed. It expects a source string argument.

%out = #REVERSE(str)

Where:

strSource string; required.
%outCopy of str, with all the last input byte first, followed by the next to last input byte, etc.

For example:

#REVERSE('abc') returns 'cba'

This #function has no cancelling conditions.

This #function is new in Fast/Unload version 4.0.

#RIGHT: Final substring, preceded by pad characters to specified length

The #RIGHT function returns a padded final substring from a source string. It expects a source string argument and a numeric length of the output string. It accepts an optional pad character. #RIGHT returns the string that ends at the last position of the source string and is of the specified output length. If the output length is less than or equal to the length of the source string, the last length characters of the source are returned. Otherwise, the source string is padded to the output length by preceding it with sufficient copies of the pad character.

%out = #RIGHT(str, len, pad)

Where:

strSource string.
lenLength of the output string.
padOptional pad character. Default is blank. If supplied, must be a string of length 1. Must be numeric >= 0.
%outIf len is greater then #LEN(str):
  • len - #LEN(str) copies of the pad character, followed by all of str

Otherwise:

  • substring from str starting at position #LEN(str) - len+1 with length len

For example:

#RIGHT('ABC', 0) returns '' #RIGHT('ABC', 1) returns 'C' #RIGHT('ABC', 3) returns 'ABC' #RIGHT('ABC', 4) returns ' ABC' #RIGHT(456, 4, 0) returns '0456'

The Fast/Unload run is cancelled with a return code of 8 when:

  • Len not numeric, or less than 0.
  • Pad supplied, and length not 1.

Notes

This #function is new in Fast/Unload version 4.0.

#SNDX: Create SOUNDEX code for string

The #SNDX function calculates a SOUNDEX code for a string, producing the same result as the Model 204 $SNDX function. It expects a single argument and returns the SOUNDEX code for the string value of the argument.

%out = #SNDX(str)

Where:

strString, presumed to contain a name.
%outSet to the SOUNDEX code corresponding to str.

For example, the following fragment will build values for an INVISIBLE KEY field which is the $SNDX value of the field NAME:

UAI OINDEX FOR EACH RECORD FOR I FROM 1 TO NAME(#) ADD NAME_SNDX = #SNDX(NAME(I)) END FOR UNLOAD END FOR

This #function has no cancelling conditions.

#STRIP: Remove leading and/or trailing copies of pad character

The #STRIP function removes leading, trailing, or both, copies of a pad character from a string. It requires a string to be stripped. It accepts an optional specification of which characters to strip (Leading, Trailing, or Both). It accepts an optional pad character argument, specifying the character to be stripped.

%out = #STRIP(str, B|L|T, pad)

Where:

strString to be stripped; required.
B|L|TOptional indicator of the type of strip:
  • 'B...' (any string beginning with uppercase B): to strip Both leading and trailing pad characters.
  • 'L...' (any string beginning with uppercase L): to strip Leading pad characters only.
  • 'T...' (any string beginning with uppercase T): to strip Trailing pad characters only.

Defaults to B.

%outSet to a copy of str, with leading, trailing, or both, as specified, copies of pad removed.

Examples:

#STRIP(' ABC ') returns 'ABC' #STRIP(' ABC ', 'L') returns 'ABC ' #STRIP(' ABC ', 'T') returns ' ABC' #STRIP('000123', 'L', '0') returns '123'

The Fast/Unload run is cancelled with a return code of 8 when:

  • B|L|T does not begin with either uppercase B, uppercase L, or uppercase T.
  • Pad specified and length is not 1.

Notes

This #function is new in Fast/Unload version 4.0.

#SUBSTR: Substring

The #SUBSTR function returns a substring from within a source string. It expects a source string argument and a numeric byte position within the source string. It accepts an optional numeric maximum length of the output substring. #SUBSTR returns the string that begins at the indicated position of the source string and ends either at the end of the source string or when the supplied maximum length has been reached.

%out = #SUBSTR(str, pos, len)

Where:

strSource string. For Fast/Unload version 4.3 and later. str may be longer than 255 bytes.
posBeginning position within the source string for the desired substring. Must be numeric >= 1.
lenOptional maximum length of substring; default is 255. Must be numeric >= 0.
%outSubstring from str starting at position pos with length that is the minimum of len and #LEN(str) +1 - pos.

For example, you might want to get the "right hand half" of a string (it will be to the right of the middle character if the string length is odd):

%LEN = #LEN(%STR) %RIGHT = %LEN / 2 + 1 /* See note below %RIGHT = #SUBSTR(%STR, %RIGHT)

The Fast/Unload run is cancelled with a return code of 8 when:

  • Pos not numeric, or less than 1.
  • Len not numeric, or less than 0.

Notes

#TIME: Current time and/or date

The #TIME function returns the current date and time in a datetime string. It accepts an optional datetime format string, with a default that returns just time information. An optional output return code argument allows the FUEL program to intercept error conditions.

%tim = #TIME(fmt, %rc)

Where:

fmtOptional datetime format string for %tim. Defaults to HH:MI:SS.
%rcReturn code (optional, output).
%timSet to datetime string with current date and time, using fmt.

For example, the following fragment stores the current time in the last occurrence of the REORG_TIME field:

ADD REORG_TIME = #TIME()

Errors

The possible non-zero values of %rc, or terminating conditions, are shown below (see the discussion in Run-time errors during standard #function calls):

4 fmt is not a valid datetime format.

In the event of a run-time error:

  • If you specified a %rc in your #function call, it is set to the number above that corresponds to the error, and %tim is set to MISSING.
  • If %rc is absent, Fast/Unload is cancelled with return code 8.

Notes

  • The only difference between #TIME and #DATE is the default value for fmt.
  • Datetime formats explains valid datetime formats.

#TRANSLATE: Change characters of string using from/to pairings

The #TRANSLATE function returns a copy of the input string, with all characters which are contained in the input table translated to the corresponding characters in the output table. It expects a source string argument, and at least one of 3 optional string arguments. It accepts optional output table, input table, and pad character (used to extend the output table if it is shorter than the input table).

%out = #TRANSLATE(str, tbl_out, tbl_in, pad)

Where:

strSource string; required.
tbl_outString of "to" characters (optional). Default is null string. Trailing pad characters are added to this string, if needed, so that its length is equal to the length of tbl_in.
tbl_inString of "from" characters, optional. Defaults to 256 characters consisting of all byte values, in order, that is, X'00010203...FCFDFEFF'.
padPad character for tbl_out (optional, must be length 1). Default blank.
%outCopy of str, with all characters which are contained in the input table translated to the corresponding characters in the output table.

For example:

#TRANSLATE('ab', 'x', 'b') returns .ax. #TRANSLATE('ab', , '', 'z') returns .ab. #TRANSLATE('ab', , , 'z') returns .zz. #TRANSLATE('ab', 'AB', , 'z') returns .zz. #TRANSLATE('ab', '', , 'z') returns .zz.

The Fast/Unload run is cancelled with a return code of 8 when:

  • Only str argument supplied.
  • Pad supplied, and length not 1.

Notes

  • As a consequence of the default rules, if neither tbl_out nor tbl_in are supplied, %out is set to as many copies of pad as #LEN(str).
  • If a character occurs more than once in tbl_in, all instances of it after the first are ignored.
  • As noted, the default tbl_in is a 256 byte string; it is not possible to specify such a value in Fast/Unload. If you want to specify a translation for all 256 characters, you can do the following:

    %TBL_OUT = ... /* A 255 byte value %TBL_FF = ... /* Value that X'FF' translates to %OUT = #TRANSLATE(%STR, %TBL_OUT, , %TBL_FF)

This #function is new in Fast/Unload version 4.0.

#UPCASE: Change lowercase letters of string to uppercase

The #UPCASE function returns a copy of the input string, with all lowercase EBCDIC letters changed to the corresponding uppercase letters. It expects a source string argument.

%out = #UPCASE(str)

Where:

strSource string; required.
%outCopy of str, with all lowercase EBCDIC letters changed to their uppercase EBCDIC equivalents.

For example:

#UPCASE('?abc') returns '?ABC' #UPCASE('?ABC') returns '?ABC' #UPCASE('') returns ''

This #function has no cancelling conditions.

This #function is new in Fast/Unload version 4.0.

#VERPOS: Position in string of character not in or in list

The #VERPOS function scans a search string from an optional starting position and then finds either the first character that is Not in a list of target characters, or the first character that Matches a character in a list of target characters. It expects a string argument that is searched and a string comprising the target characters. It accepts an optional argument indicating the type of search to perform and an optional position at which to start the search. #VERPOS returns the starting position of the identified character, or 0 as follows:

  • For the Not matched (default) search, returns the position in the search string of the first character not in the target string, or 0 if all characters in the search string are in the target string.
  • For the Matched search, returns the position in the search string of the first character which is in the target string, or 0 if no characters in the search string are in the target string.

%opos = #VERPOS(search, target, NorM, pos)

Where:

searchString to be searched.
targetString containing set of target characters.
NorMOptional indicator of the type of search being formed:
  • 'N...' (any string beginning with uppercase N): to find position of first character of search not present in target.
  • 'M...' (any string beginning with uppercase M): to find position of first character of search present in target.

Defaults to N.

posOptional position within search to begin scanning. Must be a positive numeric value, default is 1.
%oposSet to position of first character in search that is not in ('N') or is in ('M') target, at or after the position identified by pos.

Examples:

#VERPOS('SIRIUS', 'ETANOISHRDLU') returns 0 #VERPOS('HOMER', 'MRH') returns 2 #VERPOS('HOMER', 'MRH', 'M') returns 1 #VERPOS('PLATO', 'AEIOU', , 3) returns 4 #VERPOS('FRED', '', 'N', 2) returns 2 #VERPOS('BETTY', '', , 'M', 3) returns 0 #VERPOS('WILMA', 'AEIOU', 'M', 3) returns 5

The Fast/Unload run is cancelled with a return code of 8 when:

  • NorM does not begin with either uppercase N or uppercase M.
  • Pos not numeric, or less than 1.

Notes

  • If search is null, #VERPOS returns 0, as long as the other arguments are not in error. If pos is greater than #LEN(search), #VERPOS returns 0.
  • If search is non-null and target is null, #VERPOS returns 0 if NorM starts with 'M', otherwise #VERPOS returns :DT.pos.
  • The function which performs this service is usually called "verify", but #VERPOS is used to distinguish it from the SOUL $Verify function:
    • $VERIFY has only a "Boolean" (0 or 1) return; #VERPOS returns a position or 0 to indicate search "failed".
    • $VERIFY only has a "nomatch" type of search.
    • $VERIFY does not have a pos argument.
    • $VERIFY returns 0 if there is a character in search which is not in target; #VERPOS (Nomatch) returns 0 if there is no such character.
    • $VERIFY returns "some character in search is not in target" (0) if search is null and target is not null; #VERPOS returns "all characters in search are in target" if search is null, whether or not target is null.

#WORD: Return nth blank-delimited word from string

The #WORD function returns the designated blank-delimited word within a string. It requires an input string and a word number within the string.

%out = #WORD(str, word)

Where:

strString containing blank-delimited words; required.
wordNumber of word within str to return; required. Must be numeric greater than or equal to 1.
%outSet to the wordth blank-delimited word within str. If word is greater than the number of words in str, the null string is returned.

Examples:

#WORD('A B C', 1) returns 'A' #WORD('A B C', 2) returns 'B' #WORD('A B C', 3) returns 'C' #WORD('A B C', 4) returns '' #WORD(' ', 1) returns ''

The Fast/Unload run is cancelled with a return code of 8 when:

  • word is not numeric, or less than 1.

Notes

  • The User Language $WORD function also has an optional delimiter character argument, and its "word" argument is argument number 3.

This #function is new in Fast/Unload version 4.0.

#WORDS: Count number of blank-delimited word in string

The #WORDS function returns the number of blank-delimited word in a string. It requires an input string.

%count = #WORDS(str)

Where:

strString containing blank-delimited words; required.
%countSet to number of word within str.

Examples:

#WORDS('A B C') returns 3 #WORDS('ABC') returns 1 #WORDS(' ') returns 0

This #function has no cancelling conditions.

Notes

  • The User Language $WORDS function also has an optional delimiter character argument.

This #function is new in Fast/Unload version 4.0.

#X2C: Convert hex representation to character string

The #X2C function converts a string of hexadecimal characters (the digits 0-9 and the letters A-F) to the corresponding byte string. Each two characters in the input string become one character in the result string, with a leading 0 added to input strings of odd length.

%str = #X2C(hex)

Where:

hexString to convert; contains only the characters 0-9 and A-F.
%strSet to the string of bytes represented by hex.

For example, in

%JUNK = #X2C('F1F2F3')

%JUNK would be set to the string 123 (EBCDIC X'F1F2F3'), and in

%JUNK = #X2C('102')

%JUNK would be set to EBCDIC X'0102' which is a non-displayable string.

The Fast/Unload run is cancelled with a return code of 8 when:

  • Non-hexadecimal digit character in hex.

Notes

See also

Fast/Unload topics