All the Database Management Systems supported by
SAP Advanced SQL Migration (Oracle, SQL Server, IBM DB2 LUW, Teradata and Netezza) allow users to explicitly perform data conversions and also to format outputs when presenting data to the clients. For conversion they use to provide Built-in Functions like cast/convert and while for format they have other Built-in Functions like to_char, check these 2 examples from Teradata:
BTEQ -- Enter your SQL request or BTEQ command:
select to_char (12345.67, '$99999,999.99');
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
to_char(12345.67,'$99999,999.99')
---------------------------------
$12,345.67
BTEQ -- Enter your SQL request or BTEQ command:
select cast (to_char (12345, '$99999,999.99') as float);
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
to_char(12345,'$99999,999.99')
------------------------------
1.23450000000000E 004
But among them all Teradata has a kind of slightly different extra way to do it beside the generic way using the typical functions, they official name in Teradata language is the
FORMAT PHASE
The FORMAT PHRASE is en expression using parenthesis located after the column and expression and in it you can specify things like:
DATA TYPE for the output:
BTEQ -- Enter your SQL request or BTEQ command:
SELECT 12345 (FLOAT);
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
12345
----------------------
1.23450000000000E 004
DEFINE A HEADING/COLUM NAME for printed results ('TITLE' clause):
BTEQ -- Enter your SQL request or BTEQ command:
SELECT 12345 (TITLE 'Col1');
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
Col1
------
12345
DEFINE A TEMPORARY NAME for the EXPRESSION/COLUMN ('NAMED' clause):
BTEQ -- Enter your SQL request or BTEQ command:
SELECT 12345 (NAMED Col1) where Col1 > 1;
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
Col1
------
12345
NOTE: the difference between 'TITLE' and 'NAMED' is that the given name from 'TITLE' is valid only for visualization purposes while the name given with 'NAMED' can be used later in the query in other clauses like the "WHERE" or the "GROUP BY" clauses.
DEFINE A FORMAT FOR THE EXPRESSION/COLUMN (FORMAT CLAUSE):
BTEQ -- Enter your SQL request or BTEQ command:
SELECT 12345 (FORMAT '$999,999');
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
12345
--------
$012,345
SAP Advanced SQL Migration is able to convert these different parts from the FORMAT PHRASE at least for the cases we have seen in the source code we have analyzed. Please don't expect to get this converted in all cases because some situations can be very difficult to handle especially when complex expressions are involved.
Here you have some examples for source queries and how they are converted. This test table has been created a populated with this following rows for testing purposes:
create table javier.test (id int, c varchar (10), d timestamp);
insert into javier.test values (1, 'value1', current_timestamp);
insert into javier.test values (2, 'value2_1', current_timestamp);
insert into javier.test values (2, 'value2_2', current_timestamp);
insert into javier.test values (3, 'value3_1', current_timestamp);
insert into javier.test values (3, 'value3_2', current_timestamp);
insert into javier.test values (3, 'value3_3', current_timestamp);
insert into javier.test values (3, 'value3_4', current_timestamp);
insert into javier.test values (4, 'value4_1', current_timestamp);
insert into javier.test values (4, 'value4_2', current_timestamp);
QUERY 1 (explicit cast):
#TERADATA:
BTEQ -- Enter your SQL request or BTEQ command:
SELECT id, COUNT(*)(FLOAT)
from javier.test
group by id;
*** Query completed. 4 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
id Count(*)
----------- ----------------------
3 4.00000000000000E 000
1 1.00000000000000E 000
4 2.00000000000000E 000
2 2.00000000000000E 000
#CONVERSION TO HANA:
hdbsql SP4=> SELECT id, CAST(COUNT(*) AS FLOAT)
> FROM javier.test
> GROUP BY id;
> go
ID,CAST(COUNT(*) AS DOUBLE)
2,2
1,1
3,4
4,2
4 rows selected (overall time 114,613 msec; server time 2113 usec)
QUERY 2 (TITLE):
#TERADATA:
BTEQ -- Enter your SQL request or BTEQ command:
SELECT id (TITLE 'ID_COL'), COUNT(*) (TITLE 'Num rows')
from javier.test
group by id;
*** Query completed. 4 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
ID_COL Num rows
----------- -----------
3 4
1 1
4 2
2 2
#CONVERSION TO HANA:
hdbsql SP4=> SELECT id AS "ID_COL", COUNT(*) AS "Num rows"
> FROM javier.test
> GROUP BY id;
> go
ID_COL,Num rows
2,2
1,1
3,4
4,2
4 rows selected (overall time 114,896 msec; server time 1739 usec)
QUERY 3 (NAMED):
#TERADATA:
BTEQ -- Enter your SQL request or BTEQ command:
SELECT id (NAMED col1), COUNT(*)
from javier.test
group by col1
order by col1;
*** Query completed. 4 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
col1 Count(*)
----------- -----------
1 1
2 2
3 4
4 2
#CONVERSION TO HANA:
hdbsql SP4=> SELECT id AS col1, COUNT(*)
> FROM javier.test
> GROUP BY id /* sapdbmtk: Replaced column alias 'col1' by 'id' */
> ORDER BY col1;
> go
COL1,COUNT(*)
1,1
2,2
3,4
4,2
4 rows selected (overall time 135,889 msec; server time 21,592 msec)
QUERY 4 (FORMAT):
NOTE: the format is more complex to handle because the format mask can have different characters and they are not the same that to_char is using so and RTC
(What are RTCs ?) has been created for this, namely sp_f_dbmtk_format_numeric_to_string.
#TERADATA:
BTEQ -- Enter your SQL request or BTEQ command:
SELECT id (NAMED col1), COUNT(*) (FORMAT 'ZZZZZZ.ZZZZ')
from javier.test
group by col1
order by col1;
*** Query completed. 4 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
col1 Count(*)
----------- -----------
1 1.0000
2 2.0000
3 4.0000
4 2.0000
#CONVERSION TO HANA:
hdbsql SP4=> SELECT id AS col1,
> sapdbmtk.sp_f_dbmtk_format_numeric_to_string(COUNT(*),'999999.9999')
> FROM javier.test
> GROUP BY id /* sapdbmtk: Replaced column alias 'col1' by 'id' */
> ORDER BY col1;
> go
COL1,SAPDBMTK.SP_F_DBMTK_FORMAT_NUMERIC_TO_STRING(COUNT(*),'999999.9999')
1, 1.0000
2, 2.0000
3, 4.0000
4, 2.0000
4 rows selected (overall time 896,820 msec; server time 742,881 msec)
hdbsql SP4=>
Other related posts:
