Search code examples
sqloracle-databasecasespace-efficiency

Oracle SQL - Is there are more efficient way to organise a massive case statement

Currently I have a report which looks at different types of documents. Each document has an assigned timescale it should be completed by (i.e. 2 days, 4 days, etc). There are more than 100 types of document. Currently, this assigned timescale for each document is held in an excel spreadsheet and matched to the data in excel using a vlookup formula (based on assessment ID). Unfortunately there is no place in our database to put this assigned timescale, but I would like to be able to run a report from the database and just send it to users without having to do this extra manipulation in excel. I know that I could achieve this by writing a massive case statement (below is just an example)

i.e.

SELECT
ID,
CASE WHEN ID = 1 then '1 day' 
WHEN ID = 2 then '42 days'
WHEN ID = 3 then '16 days'
ELSE 'CHECK' end as 'Timescale'
FROM TABLE1

But I did wonder if there was a more efficient way of doing this in the SQL (besides requesting an additional field in the database to record this!)? It might be that there isn't, but thought it was worth asking! Thanks.

Solution

  • So you want to join between an Oracle table and an excel sheet...

    I think this is not entirely impossible. There are 2 ways.

    Way 1. You can do the join in Oracle. That means that you have to write a Java Stored Procedure that can read the excel sheet. The next step is to create PL/SQL wrappers for wrapping this Java Stored Procedure. After that you can write an SQL statement that calls the Java Stored Procedure via the PL/SQL wrappers, this SQL statement can make a join with your Oracle-table.

    Yes indeed, this is very complex.

    Way 2. I think you can connect from an excel sheet to Oracle via ODBC. It should be possible to fetch data from Oracle within excel. So excel can do the join for you.

    Yes indeed, this is very complex.

    You can also put this extra data in a new Timescale table (like Bob Jarvis suggested) but you will have to synchronize between the excel sheet and the Oracle table.

    You can also move all data to Oracle. Or maybe you can move all data to excel (probably not) ?