I am making a conversion from traditional way of creating reports in Excel, and I have been stuck on recreating NTILE function when running SQL in Excel (because NTILE function does not exist there), even though I have succeeded in recreating NTILE function when running in Oracle PL/SQL Developer.
Traditional way:
- SQL scripts are stored in an Excel sheet.
- I write macro to connect to Oracle database, then open a recordset with SQL scripts, and copy that recordset to another Excel sheet.
New way:
- SQL scripts are stored in an Excel sheet.
- A table in Oracle database is saved as a csv file on a hard disk.
- I write macro to connect to the csv file (not open it) using Microsoft.Jet.OLEDB.4.0, then open a record set with SQL scripts, and copy that recordset to another Excel sheet.
In the new way, SQL scripts are modified because some functions and statements in Oracle do not exist when running SQL in Excel (example: NTILE, REMAINDER, TRUNC, CASE). NTILE is rewritten; REMAINDER becomes MOD; TRUNC becomes INT; CASE becomes IIF.
Below is my SQL script of:
1. Creating a sample table
2. NTILE result
3. Recreating NTILE function in Oracle (as intermediate step)
4. Recreating NTILE function in Excel SQL
CREATE TABLE testntile
(
appid NUMBER(2),
sample VARCHAR2(50),
score NUMBER(3)
);
INSERT ALL
INTO testntile (appid, sample, score) VALUES (5, 'A', 4)
INTO testntile (appid, sample, score) VALUES (2, 'B', 6)
INTO testntile (appid, sample, score) VALUES (3, 'C', 8)
INTO testntile (appid, sample, score) VALUES (1, 'C', 1)
INTO testntile (appid, sample, score) VALUES (4, 'B', 2)
INTO testntile (appid, sample, score) VALUES (8, 'C', 3)
INTO testntile (appid, sample, score) VALUES (6, 'C', 8)
INTO testntile (appid, sample, score) VALUES (9, 'B', 9)
INTO testntile (appid, sample, score) VALUES (7, 'C', 7)
INTO testntile (appid, sample, score) VALUES (10, 'B', 5)
INTO testntile (appid, sample, score) VALUES (14, 'A', 2)
INTO testntile (appid, sample, score) VALUES (12, 'C', 7)
INTO testntile (appid, sample, score) VALUES (13, 'C', 7)
INTO testntile (appid, sample, score) VALUES (11, 'A', 9)
INTO testntile (appid, sample, score) VALUES (15, 'C', 3)
INTO testntile (appid, sample, score) VALUES (16, 'C', 12)
INTO testntile (appid, sample, score) VALUES (18, 'C', 12)
INTO testntile (appid, sample, score) VALUES (17, 'C', 16)
INTO testntile (appid, sample, score) VALUES (19, 'C', 12)
INTO testntile (appid, sample, score) VALUES (20, 'D', 14)
INTO testntile (appid, sample, score) VALUES (21, 'D', 11)
SELECT * FROM dual;
COMMIT;
-- desired NTILE result
SELECT
NTILE(3) OVER (PARTITION BY sample ORDER BY score) RangeList,
s.*
FROM testntile s;
-- Recreating NTILE function in Oracle
-- (as intermediate step to recreate NTILE function in Excel SQL)
SELECT
CASE WHEN REMAINDER(
(SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample)
,3) <> 0
THEN CASE WHEN ((SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample) / 3) < 1
THEN (SELECT COUNT(*) +1
FROM testntile t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
)
ELSE 1 + TRUNC((SELECT COUNT(*)
FROM testntile t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
) / ((SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample) / 3))
END
ELSE CASE WHEN ((SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample) / 3) = 1
THEN (SELECT COUNT(*) +1
FROM testntile t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
)
ELSE 1 + TRUNC((SELECT COUNT(*)
FROM testntile t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
) / ((SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample) / 3))
END
END RangeList,
t1.appid,
t1.sample,
t1.score
FROM
testntile t1
ORDER BY
t1.sample,
t1.score,
t1.appid
-- Recreating NTILE function in Excel SQL
SELECT
IIf(MOD(
(SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample)
,3) <> 0
, IIf( ((SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE