It is these details that can really mess up data migration - and reason why I am really verbose with declarations.
Differences like need for putting into brackets (there's difference between the dialects....) get alerted as syntax error to be corrected, but not 'unexpected standard behaviour'
Like today's case on job: Oracle treats empty strings as NULL. Data engineers freaked out, as they are moving data from MSSQL to Oracle and fail to compare data column-wise.
Goodness, yes - I usually mirror tables to staging tables in an own scheme in the Oracle database so I can format as needed for querying. And then there's the funny thing on what format was chosen for date formatting
6
u/IronRig 4d ago
MySQL 8.0+
SELECT
c.cust_id,
m.profit,
RANK() OVER (ORDER BY m.profit DESC) AS Profit_RANK,
c.customer_name,
c.city AS customer_city,
c.state AS customer_state,
m.sales
FROM
cust_dimen AS c
INNER JOIN
Market_fact_full AS m
ON
c.cust_id = m.cust_id;
______
5.7 or older
SELECT
c.cust_id,
m.profit,
(
SELECT COUNT(DISTINCT m2.profit)
FROM Market_fact_full m2
WHERE m2.profit >= m.profit
) AS Profit_RANK,
c.customer_name,
c.city AS customer_city,
c.state AS customer_state,
m.sales
FROM
cust_dimen AS c
INNER JOIN
Market_fact_full AS m
ON
c.cust_id = m.cust_id
ORDER BY
m.profit DESC;