Using implicit casting during application enablement

Typecasting is very common in application development. It means changing the data type from one to another. This is required in assignment operations and comparisons.

The necessity of type casting lies in the database or programming language. In this section, we will only focus on databases. Prior to DB2 9.7, databases supported strong typing for comparisons and assignments. For example, you could assign only an integer value to an integer data type. It didn't allow you to assign any other numeric data type to an integer variable without casting it to the integer explicitly. This restriction is known as strong typing.

Starting from DB2 9.7, it indirectly supports weak typing (from a user's point-of-view) but internally it casts the value to the required data type implicitly. This is commonly known as implicit casting. Implicit casting is the automatic conversion of one data type into another. During any comparison operation or assignment operation, if DB2 encounters different data types, then it uses implicit casting to do the required conversion. Implicit casting is based on a predefined set of conversion rules.

How to do it...

We have many types of casting available in DB2, including casting numeric to string, string to numeric, and casting in the BETWEEN predicate and arithmetic operations. We will discuss each one of them in detail with an example.

• Casting numeric to string data types: The EMPNO column in the EMPLOYEE table is of CHAR (6) data type. You can either pass the parameter as a string or as a numeric value and DB2 takes care of implicitly casting it.

SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE WHERE EMPNO = '000250'; SELECT EMPNO, FIRSTNME, LASTNAME FROM EMPLOYEE WHERE EMPNO = 000250; 

• Casting string to numeric data types: The DEPTNUMB column in the ORG table is of the SMALLINT data type. Let's see how DB2 converts a string value to an integer value:

SELECT * FROM ORG WHERE DEPTNUMB=10; SELECT * FROM ORG WHERE DEPTNUMB='10'; 

• Implicit casting in the BETWEEN predicate: The DEPTNUMB column in the ORG table is of the SMALLINT data type. Let's see how we can exploit implicit casting in the BETWEEN predicate of a SELECT query:

SELECT * FROM ORG where DEPTNUMB BETWEEN 10 AND 50; SELECT * FROM ORG where DEPTNUMB BETWEEN '10' AND '50'; 

• Using implicit casting in arithmetic operations: Implicit casting is also supported in arithmetic operations. The SALARY column in the EMPLOYEE table is DECIMAL(9,2). Let's apply some calculations on SALARY using different data types:

SELECT EMPNO, SALARY FROM EMPLOYEE WHERE EMPNO = '000200'; UPDATE EMPLOYEE SET SALARY = SALARY + '1000' + 1500 + BIGINT(2000) WHERE EMPNO = 000200; SELECT EMPNO, SALARY FROM EMPLOYEE WHERE EMPNO = '000200';