• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    UCG Technologies – Vault400

    Do the Math When Looking at IBM i Hosting for Cost Savings

    COVID-19 has accelerated certain business trends that were already gaining strength prior to the start of the pandemic. E-commerce, telehealth, and video conferencing are some of the most obvious examples. One example that may not be as obvious to the general public but has a profound impact on business is the shift in strategy of IBM i infrastructure from traditional, on-premises environments to some form of remote configuration. These remote configurations and all of their variations are broadly referred to in the community as IBM i hosting.

    “Hosting” in this context can mean different things to different people, and in general, hosting refers to one of two scenarios. In the first scenario, hosting can refer to a client owned machine that is housed in a co-location facility (commonly called a co-lo for short) where the data center provides traditional system administrator services, relieving the client of administrative and operational responsibilities. In the second scenario, hosting can refer to an MSP owned machine in which partition resources are provided to the client in an on-demand capacity. This scenario allows the client to completely outsource all aspects of Power Systems hardware and the IBM i operating system and database.

    The scenario that is best for each business depends on a number of factors and is largely up for debate. In most cases, pursuing hosting purely as a cost saving strategy is a dead end. Furthermore, when you consider all of the costs associated with maintaining and IBM i environment, it is typically not a cost-effective option for the small to midsize market. The most cost-effective approach for these organizations is often a combination of a client owned and maintained system (either on-prem or in a co-lo) with cloud backup and disaster-recovery-as-a-service. Only in some cases of larger enterprise companies can a hosting strategy start to become a potentially cost-effective option.

    However, cost savings is just one part of the story. As IBM i expertise becomes scarce and IT resources run tight, the only option for some firms may be to pursue hosting in some capacity. Whatever the driving force for pursing hosting may be, the key point is that it is not just simply an option for running your workload in a different location. There are many details to consider and it is to the best interest of the client to work with an experienced MSP in weighing the benefits and drawbacks of each option. As COVID-19 rolls on, time will tell if IBM i hosting strategies will follow the other strong business trends of the pandemic.

    When we say do the math in the title above, it literally means that you need to do the math for your particular scenario. It is not about us doing the math for you, making a case for either staying on premises or for moving to the cloud. There is not one answer, but just different levels of cost to be reckoned which yield different answers. Most IBM i shops have fairly static workloads, at least measured against the larger mix of stuff on the public clouds of the world. How do you measure the value of controlling your own IT fate? That will only be fully recognized at the moment when it is sorely missed the most.

    CONTINUE READING ARTICLE

    Please visit ucgtechnologies.com/IBM-POWER9-systems for more information.

    800.211.8798 | info@ucgtechnologies.com

    Article featured in IT Jungle on April 5, 2021

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    VAULT400:  Stuck in the 70's with Tape Backup? Get modern, secure back-up and DR
    Enforcive:  Enterprise security for the IBM i. Formerly Bsafe Information Systems
    SEQUEL Software:  FREE White Paper: The Race To Access Enterprise Data

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    Education Matching Innovation: OCEAN Tech Conference Building a Legacy

    Leave a Reply Cancel reply

Volume 11, Number 19 -- June 22, 2011
THIS ISSUE SPONSORED BY:

SEQUEL Software
ProData Computer Services
WorksRight Software

Table of Contents

  • SQL Implicit Cast of Character Strings and Numeric Values
  • Forcing Keyword Parameters
  • Admin Alert: Things to Think About in a Power i Development Environment

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Big Blue Unveils Spring 2021 IBM i Technology Refreshes
  • Thoroughly Modern: Innovative And Realistic Approaches To IBM i Modernization
  • Guru: Web Services, DATA-INTO and DATA-GEN, Part 2
  • Back To The Future With A New IBM i Logo
  • IBM i PTF Guide, Volume 23, Number 14
  • When Cloud Meets DevOps on IBM i
  • JD Edwards Roadmap Reveals Decisions To Be Made
  • IBM Completes Migration of Knowledge Center to IBM Documentation
  • Four Hundred Monitor, April 7
  • Crazy Idea Number 615: Variable Priced Power Systems Partitions

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2021 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.