Crystal Reports: CAST function to modify data

	Resources
	Best sellers: ● cView ● Report Analyzer ● cViewSERVER ● ReCrystallize
	Crystal Reports: Free trial
	Articles: ● Administration ● Advanced ● Basic ● Crystal eNL ● Database ● Financial ● Problems Solved Books: ● CR Books ● Database Books ● Developer Books
	Tools: ● All CR Tools ● CR Analyzers ● CR Bestsellers ● DataBase Tools ● CR Graphics ● International ● CR Mail UFLs ● ReCrystallizePro ● CR Schedulers ● CR UFLs ● CR Viewers
	Add'l: ● About us ● Contact Us ● cViewSUITE Ppt ● Support
	Crystal Reports on Steroids

Crystal Reports: CAST function to modify data

In another article, we discussed how to use a UNION join to combine data from several tables.

SELECT "First" as rectype, field1 as a, field2 as b FROM firsttable

UNION ALL

SELECT "Second" as rectype, field3 as a, field4 as b FROM secondtable

The question now is when you get these data into your Crystal Report, what sort of field type appears in the "rectype" field. The default is that the field a 254 character string field. If you want a field of a different size or type you can use the CAST function. CAST has the syntax:

CAST value AS type

You could use it in the statement above to reduce the field to 8 chars with:

SELECT CAST("First" AS Char(8)) as rectype, field1 as a, field2 as b FROM firsttable

UNION ALL

SELECT CAST("Second" AS Char(8)) as rectype, field3 as a, field4 as b FROM secondtable

CAST supports SQL Server standard data types--so the field can be a small integer, string of fixed length, floating point or whatever other data format you require.

One point to remember is to make sure that all the data values fit within the new data type. Don’t throw large values into a small integer field or you will overflow the field and cause an error.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.