SQL development varies from case to case when it use different database such as Oracle, DB2 and MSSQL. Today we’d like to take “sub-query” as an example to explain the differences.
For example, we want to calculate rate, Oracle support two ways:
1.
select (case total
when 0 then 0
else round(100*comp/total,0) end) as rate
from
(select count(*) as comp
from TABLE_NAME
where status=’COMPLETE’),
(select count(*) as total
from TABLE_NAME)
2.
select (case total
when 0 then 0
else round(100*comp/total,0) end) as rate
from
(select count(*) as comp
from TABLE_NAME
where status=’COMPLETE’) A,
(select count(*) as total
from TABLE_NAME) B
For DB2 and MSSQL , they support the way 2#, but don’t support the way #1. But they support another way:
3.
selec (case total
when 0 then 0
else round(100*comp/total,0) end) as rate
from
(select count(*) as comp
from TABLE_NAME
where status=’COMPLETE’) AS A,
(select count(*) as total
from TABLE_NAME) AS B
Nice article.
In terms of the queries, I think you are talking about which way is not more preferable for developing a SQL statement.
Some comments for you.
Basically, there are some good practices for a PL/SQL engineer.
1. Not using reserved word to be a column name, like ‘Total’, in some database, might not be complied successfully.
2. Using a alias, when involve multiple tables in query. It is not only some essential standard for specific database, like DB2, but also it cause some performance and maintenance issues. However, Oracle can recognize the different column in main query without table name, but it does impact the performance.
Thanks
Franky
some typo, i meant you are talking about the way more preferable for developing a SQL statement.
Thank you, Franky. The suggestions are very useful. I should put it more precise.