IF
Mysql: select if(field1=‘ok’,1,0) AS ‘DR‘
Oracle:
select
case
when field1=‘ok’ then
1
else
0
end AS DR
Date in condition
Mysql: where creation_date>=’2014-06-01‘
Oracle: where trunc(creation_date)>= trunc(to_date(‘2014-06-01’, ‘rrrr-mm-dd’))
In Oracle, we should use trunc besides to_date to compare. For example trunc(sysdate+1).
CONCAT
Mysql: concat(field1, field2, field3)
Oracle: concat(field1, concat(field2, field3)) or field1||field2||field3
GROUP_CONCAT
Mysql: select GROUP_CONCAT(field1 SEPARATOR ‘,’)
Oracle: select listagg(field1, ‘,’) WITHIN group (order by null)
GRUOP BY, Add aggregator in those fields which are not specified in gorup by:
Mysql: select field1, field2, field3 from t1 group by field1
Oracle: select field1, max(field2), max(field3) from t1 group by field1
UNION,
Mysql: select ‘abc’ as field1 union select field1 from t1
Oracle: select ‘abc’ as field1 from dual union select field1 from t1
Other things we need to pay attention between mysql and oracle
Select date in Oracle
In Oracle select clause, use: select to_char(creation_date, ‘mm/dd/rrrr‘), not: select to_date(creation_date, ‘mm/dd/rrrr’)
Oracle select result are all uppercase
Mysql: select field1 as f1 from t1. The result will be:f1
Oracle: select field1 as f1 from t1. The result will be:F1
Select same field for more than one time:
Mysql: select field1, field1 from t1. The result will be:field1, field1
Oracle: select field1, field1 from t1. The result will be:field1, field1_1
TO_DAYS
Mysql: select to_days(curdate()) from dual.
Oracle: select TRUNC(sysdate) – DATE’0000-01-03′ from dual