NOMAD/UltraQuest DECODE Function Explained
UltraQuest and NOMAD have several powerful functions to make transforming data from one format or type to another easier. The DECODE function is great for doing this type of change. This function is very useful if you are doing data mapping or conversion from one system to another.
The DECODE operator extracts a table value using the value of an expression as a key to find that table value.
Problem: You need to create a conversion file from application 1 to be loaded into another application 2. One of the fields/columns you need to convert is the MARRAGE-CODE from a 3 position alphanumeric code to MARRAGE-STATUS which is a 1 position numeric code. The DECODE function is perfect for this and is easy to create.
|APPLICATION 1||APPLICATION 2|
|blank default||0 default|
To add a column to UltraQuest Reporter, go to the Columns dialog, press [Add (x*y)+2] to open the Expression Column dialog, select DECODE from the ‘Operators & Functions’ drop down choosing ‘Operators’ then scroll down and select ‘expression DECODE ( old1=new1,... oldN=newN, ELSE=newX )’ and press [Insert].
In the ‘Expression’ box change to the template inserted to:
expression DECODE ( old1=new1,... oldN=newN, ELSE=newX )
MARRAGE-CODE DECODE( ‘MAR’ = 1, ‘SIG’ = 2, ‘DIV’ = 3, ‘WID’ = 4, ELSE = 0 )
‘Column Format’ to 9
‘Heading’ to ‘Marriage Status’
You now have your column in your request.
DEFINE MARRIAGE-STATUS AS 9 = MARRAGE-CODE DECODE( ‘MAR’ = 1, ‘SIG’ = 2, ‘DIV’ = 3, ‘WID’ = 4, ELSE = 0 );
Please feel to contact UltraQuest and NOMAD support if you have any additional questions about the DECODE function.
More information about the DECODE function may be found in the "Functions and Operators" section of Chapter 5 of the UltraQuest and NOMAD Reference Manual.
Next week’s topic: ALPHA Function