Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Recreating NTILE function on running SQL in Excel

Thuc DaoOct 23 2019 — edited Oct 25 2019

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

Comments

Processing

Post Details

Added on Oct 23 2019
5 comments
1,883 views