Category Archives: SQL

Determine Day of Week from Date

A colleague had an issue today where she needed to exclude records from a query who had a date in a specific column that fell on the weekend. She knew Oracle had an easy way to get this, but wasn’t sure exactly how. She was very much correct, and I suggested adding the following to… Read More »

NVL vs. COALESCE

Lewis Cunningham just posted about timing differences between NVL and COALESCE. It reminded me of a very important difference that I didn’t know about until a couple years ago that can make the timing differences even greater. NVL will evaluate both inputs, where COALESCE will stop as soon as it finds a non-null value. The… Read More »

NULLIF function

I just learned about NULLIF not too long ago, and again today found a good use case for it. I took me a while to find the function again though, so I figure if I put it here it will be easier to find next time. Basically the function NULLIF compares 2 values. If they… Read More »

NOT IN, NOT EXISTS and NULLs

Last week I was teaching a junior programmer about the differences between not in and not exists and the issue of nulls came up. This isn’t the most intuitive thing, so I thought I would post about it as well. For a simple example, I will just create 2 simple data sets of one column… Read More »

Surprises with RPAD

In putting together the test script for my last post, I noticed something I didn’t expect while using the RPAD function. My original script to populate my test table looked like: insert into t1(c1, c2) select rownum, rpad(‘test’, 5000, ‘!’) from dual connect by level

LONG is still not gone

Today I was asked by a colleague how to solve this problem, so I thought I would share. While the LONG datatype should no longer be used going forward, on occassion you do still run into it from time to time. My colleague was given the task of creating an archive process for a table,… Read More »

NoCOUG SQL Challenge #2 – My Submission

Here is my (slightly modified) submission to the NoCOUG SQL Challenge. Read below for my description of what the query is doing. with words as ( select word1, word2, word3, sys_connect_by_path(case when word2 = prior word1 then ‘1’ when word2 = prior word3 then ‘3’ when prior word2 is null then ‘0’ end, ‘.’) ordered_path… Read More »

NoCOUG SQL Challenge #2 – Submitted

I have submitted my answer to the NoCOUG SQL Challenge. It really wasn’t as terrible as I thought once I had a strategy down, even though I was only using 10gR2 functionalities. I will post my answer with my explanation for what each step does once the contest is over or I hear back from… Read More »

NoCOUG SQL Challenge #2 – It’s On!

The 2nd NoCOUG SQL Challenge is officially underway! After some thought, I figured out the pattern in decoding the message. Luckily I remembered that Iggy likes recursive SQL, so I had a feeling that would be involved. Using a quick PL/SQL script, I proved that my theory for decoding the message actually words, so now… Read More »

Notes for 1Z0-047 Part V

Continuation of my notes to review before taking the 1Z0-047 certification. Hierarchical Queries fork is the term for a node that has 2 or more children If you have both JOIN and CONNECT BY, the join is processed before the connect by Order of START WITH and CONNECT BY clauses don’t matter with respect to… Read More »