Developments

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.

I recently wanted to combine two tables; Hoping to show all OA employees and any ’1 on 1′ Review data for each. A simple query restricted my results to only show those employees who had review data. So a Left Join was in order. Luckily we’re using Oracle 9 and I didn’t have mess with weird join syntax (see Burleson’s article here: http://www.dba-oracle.com/tips_oracle_left_outer_join.htm).

All I had to work out was how to separate my criteria for the left table from the rest of the query.

Here’s an example:

select
  ROWNUM as "#",
  HRISMGR.EMPLOYEE.ID,
  decode(HRISMGR.EMPLOYEE.PREF_NAME,null,First_Name,HRISMGR.EMPLOYEE.PREF_NAME) as Preferred_Name,
  HRISMGR.EMPLOYEE.LAST_NAME,
  HRISMGR.JOB_DETAIL.JOB_DESC as Position_Title,
  HRISMGR.EMPLOYEE.ORGN_CODE_HOME as Home_Org,
  HRISMGR.JOB_GENERAL.CONTRACT_TYPE,
  HRISMGR.REVIEW.REVT_DATE,
  HRISMGR.REVIEW.REVIEWER_NAME
from
  HRISMGR.JOB_DETAIL,
  HRISMGR.JOB_GENERAL,
  HRISMGR.EMPLOYEE
Left join HRISMGR.REVIEW
  ON HRISMGR.REVIEW.WAREHOUSE_ID = HRISMGR.EMPLOYEE.WAREHOUSE_ID
  AND(  /*Don't included step increases or other types that don't include a 1 on 1 review*/
      (
        HRISMGR.REVIEW.REVT_COMPLETE IS NOT NULL
      )
      and
      (
        HRISMGR.REVIEW.REVIEWER_NAME IS NOT NULL
      )
    )
    and
    (
      HRISMGR.REVIEW.REVT_CODE IN
        (
          'R1',
          'RC'
        )
    )
    and
    (/*Reviews from the last 2 years */
    TO_CHAR(HRISMGR.REVIEW.REVT_DATE, 'YYYY') >= (TO_CHAR(CURRENT_DATE, 'YYYY')-2)
    )
/*end left join*/
where
(
    (
        (
            HRISMGR.JOB_DETAIL.STATUS <> 'T'        /*Not terminated */
        )
    )
    /* Primary job type only */
    and
    (
        HRISMGR.JOB_GENERAL.CONTRACT_TYPE = 'P'
    )
    /*Job Type: P (primary), S (secondary), or O (overload)*/
    and /*Qualify OAs*/
    /*All non-classfied employees*/
    /*(
        HRISMGR.JOB_GENERAL.PCLS_CODE  LIKE 'U%'
    )*/

    /*Non-teaching Postion Clasess from banner NTRPCLS*/
    (
        (
            (
                (
                    HRISMGR.JOB_GENERAL.PCLS_CODE  LIKE 'UE%'
                )or
                (
                    HRISMGR.JOB_GENERAL.PCLS_CODE  LIKE 'UG%'
                )or
                (
                    HRISMGR.JOB_GENERAL.PCLS_CODE  LIKE 'UF%'
                )or
                (
                    HRISMGR.JOB_GENERAL.PCLS_CODE  LIKE 'UH%'
                )or
                (
                    HRISMGR.JOB_GENERAL.PCLS_CODE IN
                    (
                    'UV101',
                    'UV301',
                    'UV401',
                    'UV501',
                    'UV601',
                    'UV701',
                    'UW101',
                    'UW301',
                    'UW401',
                    'UW501',
                    'UW601',
                    'UW701'
                    )
                )
            )
        )
    )

    /*End Non-teaching Postion Clasess from banner NTRPCLS*/
    and
    (
        HRISMGR.JOB_DETAIL.JOB_DESC NOT LIKE '%Stipend%'
    )
)
and /*get current*/
(
    HRISMGR.JOB_DETAIL.EFFECTIVE_DATE = F_JOB_EFF_DATE
    (
        HRISMGR.JOB_DETAIL.WAREHOUSE_ID ,
        HRISMGR.JOB_DETAIL.POSN ,
        HRISMGR.JOB_DETAIL.SUFF ,
        TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY')
    )
)
and
HRISMGR.EMPLOYEE.WAREHOUSE_ID = HRISMGR.JOB_GENERAL.WAREHOUSE_ID
and
HRISMGR.JOB_GENERAL.WAREHOUSE_ID = HRISMGR.JOB_DETAIL.WAREHOUSE_ID
and
HRISMGR.JOB_GENERAL.POSN = HRISMGR.JOB_DETAIL.POSN
and
HRISMGR.JOB_GENERAL.SUFF = HRISMGR.JOB_DETAIL.SUFF
order by
    1
;

The key being to add an “and” to the On statement of the Left Join; after that the Where clause only applied to the left table (all oa’s).

That gives me a list of all the OA’s and populates Review data where applicable.
There’s not much data in there yet but at least I have the framework for a report now.

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…)

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…)

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…)

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…)

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')



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