University of Oregon

Oracle SQL Custom Order by using Decode

I have a query that returns all employees plus their supervisors (based on reviews from the prior 5 years) then orders by

  1. UOID and
  2. Contract type (Primary, Secondary, Other) and then
  3. Review date

So if the employee has multiple positions we get the primary position first.
If they have had multiple reviews the latest one floats to the top.
Now we can use the first row returned for each EE to determine

  • their current primary org. and
  • their most recent supervisor (if there’s a review on file)

To do the custom order by I used the decode function on the employee’s contract type. *Thanks to http://psoug.org/reference/decode_case.html (More Complex DECODE), and http://psoug.org/reference/orderby.html (Ordering With A Decode)
Here’s the whole order by line:

ORDER BY HRISMGR.EMPLOYEE.ID, DECODE(HRISMGR.JOB_GENERAL.CONTRACT_TYPE, 'P', 'A', 'S', 'B', 'O', 'C', 'Z'), HRISMGR.REVIEW.REVT_DATE DESC;

but let’s isolate the fun part:

DECODE(HRISMGR.JOB_GENERAL.CONTRACT_TYPE, 'P', 'A', 'S', 'B', 'O', 'C', 'Z')

Here I used decode like a case statement:
Switching on the contract_type;
if ‘P’ (Primary) then return ‘A’ for sorting
If ‘S’ (Secondary) then return ‘B’ for sorting
If ‘O’ (Other) then return ‘C’ for sorting
Default case: return ‘Z’ for sorting

It works great.

Updating the Chart of Accounts to Include the Hierarchy Path

Way back when I started, I was working on an application where I wanted to try and get the “Org Code Roll-up” for departments and use that to build a hierarchy in our application.
My search led me to the Chart of Accounts (a Data Warehouse FIS query that’s run periodically) that provides the parent org code for each item but not a full roll-up to the top.
That was good enough to get started but now a few years later I picked up the challenge again.
After following some clues in the Oracle documentation and posts on the topic I found “SYS_CONNECT_BY_PATH”. And that’s just about all it took. I plugged it into my chart of accounts query and now I’ve got the roll-up org!

Then I tried it with the department name and after a bit of fiddling, it worked too.

Here are the two lines of code:

SYS_CONNECT_BY_PATH(o.GQVORGN_ORGN_CODE, '/') AS "Orgn-Roll-up",
SYS_CONNECT_BY_PATH(replace(o.GQVORGN_TITLE,'/','-'), '/') AS "Dept-Roll-up",

I needed to replace any ‘/’ in the title so we could connect the names as path using that symbol.
(more…)

Oracle Data Warehouse using a relative date

Just a quick note pertinent to data warehouse sql.
I wanted to find a list of org codes that have expired in the last 30 days.
The key was: CURRENT_DATE-30.
I put that in a TO_CHAR and put that into TO_DATE function.
Like so: o.GQVORGN_TERM_DATE > TO_DATE(TO_CHAR(CURRENT_DATE-30, ‘MON-YYYY’),’MON-YYYY’). Putting Current_date – 30 into a date format.

I thought it would cause me trouble but it worked the first time.

Here’s the full code.
(more…)

Add an empty row to Oracle / Data Warehouse query results

I have a query I use to capture all the current OA’s from Data Warehouse. I needed a quick way to add a blank row to the top of those results.

So here’s what I found out.
You can specify your own select data and union it to the query data. This works by selecting your elements from DUAL; which is a place holder table for Oracle.
I also amended ROWNUM in my query to ROWNUM + 1.
I wasn’t sure that would work but it worked great!

Thanks to the suggestion by Nebakanezer on this post on stackoverflow.com http://stackoverflow.com/questions/738631/add-row-to-query-result-using-select for the ground work. The
(more…)

Updating an Access 03 switchboard for Access 07 (Win 7)

We were having trouble running with a switchboard from an older version of Access and the solution had previously been to install Access 03. But that won’t work on Windows 7.
In the latest version of windows the switchboard and some necessary marcos won’t run without Microsoft Jet 4.0 SP8.
(more…)

Data Warehouse in Windows 7

I currently use DHWS in Windows 7 beta 32bit (XP Mode not available) and DWHS works fine for the most part.
Queries are indeed the problem. They are accessible but not via the normal means, so my method may not be suitable for your users.
(more…)

Oracle/Data Warehouse – Select today's date

I’m pulling a query from Data Warehouse and I need to qualify today’s date.

Here’s the code I used:

TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY')



Oracle/Data Warehouse – Select columns conditionally

I’m pulling a query from Data Warehouse and I only want to output a single column for first name. There are two columns to choose from Pref_Name (preferred name) and First_Name. Preferred name can be null (blank). Here’s the code I used:

select
...
decode(EMPLOYEE_B.PREF_NAME,null,First_Name,EMPLOYEE_B.PREF_NAME) as First_Name
from
...

For more info on decode see: http://www.orafaq.com/wiki/Decode



Navicat Woes…

In trying to import excel data and create new table I’ve learned several things.

The Navicat-lite version wont import.
The Navicat premium version on MAC wont successfully import xls and has no xlsx option.
The Navicat for MySQL – PC version failed on xlsx but worked with xls.
So that’s more info than I wanted to know but now I won’t have to learn that lesson again.