There are lots of times when you need to transform data in a query federation scenario. In this section, I will discuss data transformation capabilities built into Ipedo’s SQL engine.
Ipedo’s SQL engine is based on industry standards and implements SQL92 and SQL99 features. It supports all core data types, SQL statements and built-in functions, thereby providing transformation capabilities for data conversion, data transformation (or conditional transformation) and data manipulations. Furthermore, when used in conjunction with advanced features such as user defined function in the SQL engine user can even integrate or interface with other BPM or messaging products. However, I will postpone the discussion of user defined function to the next post. In this post, let’s look at examples showing various ways data transformation can be implemented using the SQL engine.
Example 1: Adding New Column Data Base on Country
Let’s first execute a query “select customerid, companyname, contactname, address, city, postalcode, country from customers” and look at the output shown below:
Notice the output: it shows a list of customers and the countries where customers are located. However, there is no indication of the region where customer’s country is located. Let’s rewrite the query and add a column with region information based on the country. We’ll use a case expression to conditionally specify region for different countries. For simplicity, we’ll categorize all countries in North and South America as in region “Americas” and all European countries as region “Europe”.
select customerid, companyname, contactname, address, city, postalcode, country,
case when country='Brazil' then 'Americas'
when country='USA' then 'Americas'
when country='Venezuela' then 'Americas'
Else 'Europe'
End as region
from customers
order by region
Now, execute this query and look at the output shown in the screen below. You’ll see a new column “Region” and it display the region values either Europe or Americas. You can use the same technique to check for data errors and other purposes as well.
Example 2: Checking for Error Conditions
In the example above, I’ve made an error that you may have already noticed. I forgot to include countries such as Argentina, Canada and others in the Americas region. As a result, they’re mistakenly being categorized into the Europe region. Let’s write another query to catch those errors.
Again we add another case expression to check for each country and region value. If we find an error, we’ll display “ERROR: WRONG REGION CODE DETACTED” next to the country under a new column “Error”. For countries that are correctly categorized, we’ll display “NO ERROR FOUND”
select customerid, companyname, contactname, address, city, postalcode, country,
case when country='Brazil' then 'Americas'
when country='USA' then 'Americas'
when country='Venezuela' then 'Americas'
Else 'Europe'
End as region,
Case when (country='Argentina' or country='Canada' or country='Mexico')
and region = 'Europe'
then 'ERROR: WRONG REGION CODE DETACTED'
Else 'NO ERROR FOUND'
END AS error
from customers
order by region, country
Execute the query and here’s the output.
Example 3: Concatenate Data and Convert Data Types and Currency
Let’s look at the output below by first executing the query: select orderid, customerid, freight, shipcountry from ADMIN.ORDERS.
Notice the amount of freight charges are all in US dollars for all countries, including both European countries and Americas countries. Secondly, there’s no currency symbol displayed. Here is what we are going to do. We’ll look for all countries in the Europe region (as we classified in our previous queries) and convert their freight charge from US dollars to European common currency Euro and use letter “e” as the symbol for the Euro. We will then convert the freight charge from decimal type into a character string and concatenate the currency symbol with the freight charge in character form. Here is the query:
select orderid, customerid, freight,
case when (select region from exchangerate where shipcountry=country)='Europe'
then 'e' || cast(freight*(select rate from exchangerate where country = shipcountry) as char(20))
when (select region from exchangerate where shipcountry=country)= 'Americas'
then '$' || cast(freight*(select rate from exchangerate where country = shipcountry) as char(20))
end as localizedFreightCharge,
shipcountry
from ADMIN.ORDERS
The “||” symbol is the operator for concatenation and the cast function is used to convert decimal data type to character type. Finally, the currency exchange rate we use for calculation and determine which country uses what currency is by using a sub query lookup.
That’s it. As you can see, you can do a lot data transformation just with the SQL language.
