03
Thu, Oct
2 New Articles

Working with Derived Columns

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Format and name derived columns when you use SQL to do ad hoc analysis of information in an SQL table.

 

I frequently use Interactive SQL or Run SQL Scripts in Navigator to do ad hoc analysis of information in an SQL table, which often results in one or more new columns being derived or created based on existing columns. When I first began doing these types of queries, I was frustrated with the way SQL identified and formatted a derived column in the result set. 

 

Sometimes a numeric (zoned or packed decimal) derived column had a very large number of decimal positions, or a derived column did not have a meaningful name, or the data type was not what I required, and each of these issues could render the derived column useless or make it difficult to work with. 

 

If you know the tricks, these formatting issues for derived columns can be easily resolved using a couple of SQL column and scalar functions. This article will focus on the DEC and ROUND functions used in conjunction with the AS operator to assign a meaningful name to a derived column and change its data format to something more workable.

This article is based on the SQL function available in V5R4 of i5/OS. I'm assuming that you have a basic understanding of relational database concepts and SQL and that you are familiar with basic use of the SELECT statement.

 

The SQL examples in this article use the following syntax:

 

1.      Anything in all UPPERCASE is SQL required syntax.

2.      Anything in all lowercase is SQL parameter data that the end user must supply.

3.      Anything enclosed in single quotes ('S')--upper, lower, or mixed case--is a literal used for comparison purposes and is supplied by the end user.

 

The SQL examples used in this article will reference a table called EMP (Employee Master) to show how to use the DEC and ROUND functions and the AS operator. EMP contains a total of six rows, one for each employee in the company, which are shown in Table 1.

 

EMP (Employee Master)

Nbr 

Name       

Class 

Sex 

Dept 

Salary

10  

Ed           

5    

M   

911  

7000

20  

Heikki       

2    

M   

901  

6000

30  

John         

5    

M   

977  

3200

40  

Mike         

4    

M   

977  

6500

50  

Marcela      

3    

F   

911  

7500

60  

Frank        

2    

M   

990  

6500

Decimal Positions Gone Wild

When you derive a new column by operating on an existing numeric column with a column function such as AVG (average), * (multiply), or / (divide), the resulting derived column can have a frustratingly large number of decimal positions. I am frequently asked where all those decimal positions come from and how to get rid of them.

 

The number of decimal positions for a derived column is determined by the function that created it. For example, the number of decimal places resulting from a multiplication is the sum of the decimal places for the multiplicand and the multiplier. If the multiplicand and multiplier do not have any decimal positions, the multiplication result will not have any. If the multiplicand has three decimal positions and the multiplier has two, the resulting derived column will have five decimal positions.

 

However, the number of decimal positions resulting from a division or the AVG column function will most likely always be large. In both these situations, the total length of a derived numeric column is 31 positions, which includes decimal positions. The number of decimal positions for a numeric derived column can be determined by this formula:

 

31 minus (total length minus number of decimal positions) 

 

As an example, let's use SQL to derive or create the average salary for all employees in the employee master table called EMP. The salary field is defined as packed decimal with a precision (total length) of 5 and a scale (number of decimal positions) of 0 (zero). Using the above formula, the result set for average salary will have 26 decimal positions (31 - (5 - 0) = 26). The SQL statement to derive the average salary is relatively simple and is coded as follows:

 

SELECT  AVG(salary)  

 FROM  emp

 

The results of executing this SQL statement using Interactive SQL derives the column called AVG(SALARY) and is shown below.

 

AVG(SALARY)

            6,116.66666666666666666666666666

 

If you count the number of positions in this number, including the leading zero that is suppressed (don't count the comma and decimal point), you will find there are 31, which is what we would expect it to be. If you count all those sixes after the decimal point you will find that there are 26, which is what the formula (31 - (5-0) = 26) tells us there should be.

Getting the Decimal Positions Under Control

To get the number of decimal positions under control, the data format for average salary has to be changed from a length of 31 with 26 decimal positions to something more reasonable, such as a length of seven with two decimal positions. The scalar function DEC (or DECIMAL) can be used to easily accomplish this. 

 

The DEC function returns the decimal representation of a number with the specified precision (column length or total digits in the number not counting the sign) and scale (number of decimal positions). A decimal value is a packed decimal or zoned decimal number with an implicit decimal point. The precision and the scale of the number determine the position of the decimal point, and the scale cannot be negative or greater than the precision. The DEC function has the following syntax:

 

DEC(expression,  precision,  scale) 

 

The SQL statement to derive the average salary using the DEC function to change the data type of the derived column to have a length of seven with two decimal positions is coded as follows:

 

SELECT  DEC(AVG(salary),  7,  2)    

   FROM  emp

 

The result of executing this SQL statement derives a column or value called DEC that is seven positions long with two decimal positions (remember that the leading zero in the result is suppressed), and is shown below.

 

           DEC

            6,116.66

 

Now you know where all the decimal positions come from and how to change the data format of the derived column to have a workable length and number of decimal positions.

To Truncate or Round?

From a data analysis perspective, in the example we have been discussing, should the derived value be 6,116.66 or 6,116.67? Should the extra decimal positions in the derived column just be truncated or dropped, or should the ROUND scalar function be used to round the rightmost decimal position to derive a more precise number? The ROUND function can be a very useful tool when doing data analysis, and the decision to use it should be made based on the end use for the derived column.  

 

The ROUND function returns a numeric value rounded to some number of places to the right or left of the decimal point. It has the following syntax:

           

     ROUND(expression,  decimal-position)

 

The decimal-position specified in the ROUND function can be a positive or negative number. If it is a positive number, this is the scale or number of decimal positions that will be to the right of the decimal point, with the rightmost decimal position being rounded. If it is a negative number, this is the number of significant digits to the left of the decimal point that will be set to zero, with the next significant digit to the left of the decimal point being rounded. 

 

If rounding is not required, then the SQL statement using the DEC function in the previous example is OK, and the derived column will be truncated (no rounding) to the scale or number of decimal positions specified by the DEC function. If rounding is desired when using the DEC function, the SQL statement would be coded as follows.

 

SELECT  DEC(ROUND(AVG(Salary),  2),  7,  2)  

   FROM  emp

 

Note that the ROUND function is placed between the DEC and AVG functions and that you need to specify the decimal position for which the rounding will occur. In this case, it is the number 2 immediately after (Salary), which says the rounding will occur for the second decimal position. Also note that the decimal-position value for the ROUND function must be equal to or less than the scale value for the DEC function if rounding is to occur. 

 

This SQL statement will derive a column or value called DEC that is seven positions long (leading zero is suppressed), with two decimal positions where the second decimal position is rounded. Therefore, as a result of the rounding, the derived value becomes 6,116.67 instead of 6,116.66.  The result of executing this SQL statement is shown below.

 

                  DEC

            6,116.67

What's in a Name?

When a derived column is created using Interactive SQL, it is given a name related to the function that created it. In the first example, which used the AVG function, the derived column was given the name (and column heading) of AVG(SALARY). In the next two examples, which employed the DEC and ROUND functions, the derived column was given the name of DEC.  While AVG(SALARY) is more meaningful than DEC, neither of these names is very useful, they can be confusing, and they can be difficult to reference in a complex SQL statement. The simple solution is to us the AS operator to provide a name for the derived column when it is created.

The AS Operator

The AS operator is used in SQL to replace an existing name with a new name or to assign a meaningful name to any value derived in an SQL statement. In many cases, the name that is assigned by the AS operator can be referenced later in the SQL statement in which the AS operator is used. 

 

The syntax of the AS operator clause is simple and shown below:

 

     existing-SQL-name  AS  new-SQL-name 

or

     SQL-function  AS  SQL-name

 

If we want the rounded, average salary derived in our last example to have the name of AVGSAL, the SQL statement to do that would be coded as follows:

 

SELECT  DEC(ROUND(AVG(Salary),  2),  7,  2)

      AS  avgsal 

   FROM  emp

 

Note that the AS operator immediately follows the expression or function it is naming, and there is no comma between the expression or function and the AS operator. The result of executing this SQL statement is shown below.

 

        AVGSAL

            6,116.67

Summary

SQL is used to do ad hoc analysis of information in an SQL table, and one or more new columns are often derived as a result of that analysis. Depending on the function used, a numeric (zoned or packed decimal) derived column may not have the desired data type, may not have a meaningful or useful name, and can have a very large number of decimal positions. This can make the derived column unusable or difficult to work with. 

 

The DEC and ROUND functions, in conjunction with the AS operator, can be used to resolve these issues. The DEC function is used to change the length of a derived column and remove any excess decimal positions, the ROUND function is used to round a numeric derived value to a specific number of places to the right or left of the decimal point, and the AS operator is used to change or assign a meaningful and useful name to a derived column. 

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: