Simple example of converting time-time to hrs/minutes. Change the times or date in the subquery to see what happens. SELECT start_tm ...

In 12c, Oracle released the Fetch/Offset clauses of the Select statement. It can replace "WHERE ROWNUM <" . Besides having mor...

When 'close enough' is 'good enough', try the 12c statement APPROX_COUNT_DISTINCT. (It was actually available in 11g, but n...

It's important to know which SQL statements happen before others. Hopefully, this list will make it obvious why you can't use an ali...

If you need the 1st or last of a set, try this code: SELECT year_code AS "Yea...

This is similar to the post on cumulative sum using the windowing function. This was needed because we had an old application that would on...

I found this bit of code when I was trying to write a report for work. I like it and wanted to save it as sometimes links seem to go away....

If you want to change your login in your code, use the following statement. It can be either 'CONN' or 'CONNECT'. I find t...

This code was written like this because the table names were not constant in which I need a count of a certain field. This sets variables ...

I may be the last one to figure this out, but a WHERE statement causes grief with an outer JOIN. Here I’m trying to join on a code table to ...

I wish I could think of a good reason to have a CASE statement in a WHERE statement -- but I can't so I'll just show something that ...

This is a simple example of using CASE to get a specific order in your output. SELECT ename , job FROM scott.emp ORDER BY CASE job...

This script is to backup a set of common tables. The owner and table_name are converted to a shorter version so there is room to add a date ...

I was asked to find a column on a table that was null that we could use for another purpose. Since on the requested table, there is close ...

I’m building your standard Select statement, but I hate looking at null fields so I’m tossing those out. It does take a little while to ge...

This query -- once you adjust it for the schemas you’re interested in (and tables you’re not) -- will show pertinent information on the tab...

This might come in handy if you have to find all the places in your code that a word is used. The following code generates a SQL query with ...

Rollup usually will show subtotals on every difference in the group. To show only a grand total, add 'grouping sets' and a '1...

If you’re tired of switching your schemas in code from UAT to PRD, you can use ‘USER’, which will return the schema. -- create amper varia...

To get a cumulative sum, you need to use a window function ‘Rows Between Unbounded Preceding and Current Row’, which tells Oracle that the ...

A while ago, the disk on my laptop crashed, and so now I'm trying to download and set up some of the tools I used before. I got SQL Deve...

Just a picture of 99% of the date variations listed in the book, although I'm saving timestamps for another post. The Easy Way   Sele...

I was recently taking an online class in SQL from Oracle. I will admit that sometimes self-joins still baffle my mind. So I decided to try w...

A friend asked me to explain the hierarchical query, and when I went back to look at what I had on the blog, there was little explanation, a...

*I saw this on Uwe Hesse 's blog, and it was too cool not to keep a copy where I knew I could find it again.  The only thing I added to ...