SQL Implicit Cast of Character Strings and Numeric Values
June 22, 2011 Skip Marchesani
|
Awhile back I was teaching an SQL class in Fargo, North Dakota. During one of the afternoon lab sessions, one of the students asked if he could use the CONCAT function on two numbers, or two numeric columns, without first casting them to character values. I knew the answer was “No”, and that the numeric values had to be cast to character first before the student concatenated them together. Instead of just giving the student the answer, I played good teacher and I told the student to try it and let me know how it worked. Imaging my surprise when the student called me to his workstation and showed me that the CONCAT had worked without first casting the numeric values to character. That left me scrambling for the SQL reference manual to find out what was going on. After conferring with my DB2 contacts in Rochester, Minnesota, I found that IBM had implemented a new feature in V5R3 called “Implicit Conversion between Strings and Numeric Data Types.” It also can be referred to as “Implicit Cast of Data Types,” or “Implicit Cast” for short. The benefit of Implicit Cast is that you no longer have to cast a numeric column or value to character before operating on it with an SQL string function like SUBSTR or CONCAT. This means that Implicit Cast will result in simplified syntax for an SQL statement in situations like the one mentioned above. The only reference to Implicit Cast I found in IBM documentation was in the “What’s New” section at the beginning of the V5R3 SQL reference manual. There is a single line that mentions this new Implicit Cast feature; it’s the second bullet near the top of page 20 of the PDF file (the actual page number is xviii). If you want to check it out yourself, visit this Website, click on “iSeries Information Center” on the top left, the click on “Database”, then “Printable PDFs”, and scroll down the manual list until you come to the V5R3 SQL reference manual. Let’s see how Implicit Cast can simplify SQL syntax when using SUBSTR to operate on a numeric column. For this example we will use the Employee Master Table with the rows and attributes shown below. We will write an SQL SELECT statement that will return a result set containing the employee number (NBR), employee name (NAM), and the last two digits of the department number (DPT) for that employee. NBR NAM DPT 10 Ed 911 20 Heikki 901 30 John 977 40 Mike 977 50 Marcela 911 60 Frank 990 NBR Dec(2,0) NAM Char(10) DPT Dec(3,0) Prior to Implicit Cast, it was necessary to use the CAST expression to transform DPT from numeric to character before operating on DPT with SUBSTR.
SELECT nbr, nam,
SUBSTR(CAST(dpt AS CHAR(3)),2,2) AS chardpt
FROM emp
Note that the CHAR or DIGITS functions could also be used to transform DPT from numeric to character. With Implicit Cast, SUBSTR can directly operate on DPT without first using CAST (or CHAR or DIGITS) to transform it from numeric to character as shown below. This simplifies the SQL syntax since the CAST is done implicitly by DB2.
SELECT nbr, nam,
SUBSTR(dpt,2,2) AS chardpt
FROM emp
Both SELECT statements return the following result set. NBR NAM CHARDPT 10 Ed 11 20 Heikki 01 30 John 77 40 Mike 77 50 Marcela 11 60 Frank 90 Here’s another example that shows the difference in syntax complexity. The following SQL SELECT statements return a result set containing the employee number (NBR), employee name (NAM), and the employee number concatenated to the department number (DPT) with a hyphen in between the two numbers. The first SELECT statement uses the explicit CAST and the second SELECT statement uses implicit cast.
SELECT nbr, nam,
CAST(nbr AS CHAR(2)) CONCAT '-' CONCAT CAST(dpt as CHAR(3)) AS nbrdpt
FROM emp
SELECT nbr, nam,
nbr CONCAT '-' CONCAT dpt AS nbrdpt
FROM emp
Both SELECT statements return the following result set. NBR NAM NBRDPT 20 Heikki 20-901 10 Ed 10-911 50 Marcela 50-911 40 Mike 40-977 30 John 30-977 60 Frank 60-990 Implicit cast also works with SQL arithmetic operations of addition, subtraction, multiplication, division, and exponentiation. The SQL reference manual states that if one operand of an arithmetic operator is numeric, the other operand can be a string. The string must contain a valid string representation of a number and is first converted to the data type of the numeric operand. This then raises the question of what the data type will be when doing an implicit cast from numeric to character. The answer is that it depends on what string function you are using, and that DB2 will use a “best fit” data type for implicit cast to character. For a detailed discussion on resulting data types, see “Expressions” in Chapter 2: Language Elements in the SQL Reference manual. Specifically, in the “Expressions” section, see the headings “With Arithmetic Operators” and “With the Concatenation Operator”. You might also ask, will the data type of a derived column resulting from Implicit Cast be an issue for you? If you’re using Implicit Cast in a SELECT statement to view or display data, the resulting data type will not be an issue. If you need to manipulate that derived column after it has been created using Implicit Cast, knowing or understanding the resulting data type may be key to the successful manipulation of the data in the derived column. IBM gave us a hidden goodie in V5R3 with Implicit Cast of Data Type. Proper use of this feature can simplify the syntax of SQL statements that you write and therefore increase your productivity. Skip Marchesani retired from IBM after 30 years and decided to pursue a career as a consultant in the IBM i marketplace. He spent much of his IBM career working with the Rochester Development Lab on projects for S/38 and AS/400, and was involved with the development of the AS/400. Skip was part of the team that taught early AS/400 education to customers and IBM lab sites worldwide. Now recognized as an industry expert on DB2 for i (a.k.a. DB2/400), and author of the book “DB2/400: The New AS/400 Database,” Skip specializes in providing customized education for any area of the IBM i and AS/400, does database design and design reviews, and general IBM i, iSeries, and AS/400 consulting. He has been a speaker for user groups, technical conferences, and IBM i, iSeries, and AS/400 audiences around the world, and has received COMMON’s Distinguished Service Award. Skip Recently moved to Vermont and is now president of the Vermont Mid Range User Group (VTMUG). Send your questions or comments for Skip to Ted Holt via the IT Jungle Contact page.
|
