Null values when using the SQL convert function

You use the convert function to convert a Java object of one class to a Java object of a superclass or subclass of that class.

As shown in “Subtypes in Java-SQL data”, the home_addr column of the emps table can contain values of both the Address class and the Address2Line class. In this example:

select name, home_addr>>street, convert(Address2Line, home_addr)>>line2, 
	home_addr>>zip from emps

the expression “convert(Address2Line, home_addr)” contains a datatype (Address2Line) and an expression (home_addr). At compile-time, the expression (home_addr) must be a subtype or supertype of the class (Address2Line). At runtime, the action of this convert invocation depends on whether the runtime type of the expression’s value is a class, subclass, or superclass:

Adaptive Server evaluates the select statement for each row of the result. For each row:

Hence, the results of the select shows the line2 value for those rows whose home_addr column is an Address2Line and a null for those rows whose home_addr column is an Address. As described in “The treatment of nulls in Java-SQL data”, the select also shows a null line2 value for those rows in which the home_addr column is null.