Recreating NTILE function on running SQL in Excel

Thuc Dao

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

L. Fernigrini

Why do not just creating a VIEW on Oracle that does whatever you need, and connect to Oracle and query that view from Excel?

Thuc Dao

Hi Fernigrini,

Your suggestion is my traditional way as described. I can either run from Excel the below SQL script or its VIEW on Oracle.

SELECT

            NTILE(3) OVER (PARTITION BY sample ORDER BY score) RangeList,

      s.*

FROM testntile s;

However, my manager request me to make csv file from Oracle table and run SQL script on this file from Excel. I do not want to reinvent the wheel, but it is the job I was assigned.

If you can help me, I am really appreciate your kind support. As you see, I have succeeded in recreating NTILE function on Oracle, but not on Excel. I have searched on Google tons of results but without any clue.

BrunoVroman

Hello,

-A- "bouncing on previous replies" and putting them together: maybe you can create the csv file from database side, with NTILE available at this time, and your work in Excel would be reduced to a simple "SELECT * FROM csv_tbl;" ?

-B- welcome to the forums; note that your "display name" can easily be modified to a more "human-friendly" value, please have a look at    (it takes only a couple of minutes)

Best regards,

Bruno VROMAN.

P.S. I love NTILE ;-)

Mike Kutz

L. Fernigrini wrote:Why do not just creating a VIEW on Oracle that does whatever you need, and connect to Oracle and query that view from Excel?

I've done this ... but - I presented the data to Excel via ORDS.  This way, the Desktop Team didn't have to fumble around with Oracle Client installs.

My $0.02

MK

L. Fernigrini

Well, talk to your manager.

Explain that there may be quicker, safer ways to do this (avoiding the file and getting the data directly into Excel.

Or creating the CSV directly on the database.

Be sure you (and he) are not focusing on a particular (and rather complicated) solution rather than on the problem

http://xyproblem.info/