Show results from a SQL Union in order by a specific select statement

I came across an issue at work the other day and I could not find much help on how to accomplish it. So again I want to share it here for others to see.

I needed to union a few SQL statements together. Oracle sorting the results by the first field was not helpful. , I wanted the results sorted in order of specific SQL statements. The solution I found that worked for me was to add a column to the query. For example

A simplified version of the table I was working with looked like this

Enclosure Connection1 Strand1 Connection2 Strand2
133977 2014 1 2826 1
133977 2014 2 2826 2
133977 2014 3 2826 3
133977 2004 1 2825 1
133977 2004 2 2825 2
133977 2004 3 2825 3

So my query for this example would be
Select 1 as sortcol,enclosure,connection1,strand1,connection2,strand2
from Connections where connection1 = 2014
UNION
Select 2 as sortcol,enclosure,connection1,strand1,connection2,strand2
from Connections where connection1 = 2004 order by strand1;

Since Oracle sorts the results by the first column now it will sort by sortcol. Before it would sort by Enclosure which did not return the results ordered how I wanted them.  Since I was using C# to output these results into a DataGridView I wanted the results already sorted and ready to be put into the grid.

You can of course put sortcol at the end of the select statement and then order by that column.

Select enclosure,connection1,strand1,connection2,strand2,1 as sortcol
from Connections where connection1 = 2014
UNION
Select enclosure,connection1,strand1,connection2,strand2,2 as sortcol,
from Connections where connection1 = 2004 order by sortcol,strand1;

 

Oracle Greatest Function

I came across an issue the other day at work that I thought I could not solve without PL/SQL. I researched because I wanted to get the result using a SQL statement. My issue was that I needed to find the max value between two columns in Oracle. Here is an example table

FEAT_ID FEAT1 LOW1 HIGH1 FEAT2 LOW2 HIGH2
133977 2014 120 120 2825 86 86
133977 2013 4 4 2826 12 12

I wanted to find the greatest value between HIGH1 and HIGH2. I found this nice function.


SELECT GREATEST(HIGH1,HIGH2) FROM SPLICE_CONNECT WHERE FEAT_ID = 133977 AND FEAT1 = 2014 AND HIGH1 = 120;

This of course returns 120 since 120 > 86.

 

You can learn more about the greatest function here

 

 

 

Inner Join vs Outer Join

Here I will show the difference between Inner Joins and Outer Joins hence the title Inner Join vs Outer Join.

I have three tables to use for these examples.

Table1: Customers

CUSTID
FNAME
LNAME
ST_ADDRESS
CITY
STATE
HOME_PHONE
EMAIL

Table 2: Orders

ORDID
CUSTID
PARTID
QUANTITY

Table 3: Parts

PARTID
NAME
DESCRIPTION
QUANTITY

 Inner Join Vs Outer Join

Inner Join

select fname,lname,ordid from customers  
INNER JOIN Orders ON customers.custid = orders.custid;

This query will show customers that have placed orders. Here is a Venn Diagram of the query. This query returns everything within the middle of the diagram. So custid that is in the customers table as well as the orders table. It will not show any customers that have not ordered and will not show any orders that do not have a customer record.

Venn Diagram of Inner Join(Inner Join vs Outer Join)

Venn Diagram of Inner Join

 

 

 

 

 

 

 

The result of this query returns

Result of Inner Join

Result of Inner Join

 

 

 

 

 

 

Left Outer Join

select parts.partid,parts.name,parts.quantity,ORDID from parts
LEFT OUTER JOIN Orders ON parts.partid = orders.partid;

 

This query can be shown as

Left Outer Join Venn Diagram

Left Outer Join Venn Diagram

 

 

 

 

 

 

 

And the result of this query is below

Result of Left Outer Join

Result of Left Outer Join

 

 

 

 

 

 

Here you notice it shows all parts from the parts table whether there has been an order for them or not.

 

Right Outer Join

select parts.partid,parts.name,parts.quantity,ORDID from parts  
RIGHT OUTER JOIN Orders ON parts.partid = orders.partid;

Notice this is the same query as above except I am using Right Outer Join.

Right Outer Join Venn Diagram

Right Outer Join Venn Diagram

 

 

 

 

 

 

 

Here is the result of the query

Right Outer Join Result

Right Outer Join Result

 

 

 

 

 

 

 

Old vs New Inner Join/Outer Join syntax

Ask Tom

 

Oracle Application Express Install

In this post I will discuss the steps needed to install Oracle Application Express 4.2.1.

First you need to make sure you have an Oracle DB installed on your computer. You can get a free version here.

Oracle 11G Express Edition

Here you need a login to the Oracle site. If you don’t have one don’t worry, it’s free to create one! Accept the license agreement then download the DB and install(I may write a blog post about how to install later).

Now to install Oracle Application Express.

Installing Oracle Application Express

Just like the Oracle DB, you need to download it from the Oracle site

Oracle Application Express 4.2

Once downloaded you will need to unzip the folder and move to your desired location. You will notice that all the files  inside the folder are  SQL files and no executable files of any kind. Open a DOS windows and navigate to the location of the apex files. Then run SQL*Plus from the cmd prompt. Login as the sysdba(password was set during DB installation)

Oracle Application Express
After connecting to the Oracle DB you need to run the apexins script.
An example of what parameters to use is shown below

@apexins.sql SYSAUX SYSAUX TEMP /i/

The first parameter is the tablesspace to use for the application user
The second parameter is the table space to use for the files user
The third parameter is the temp tablespace
The fourth parameter is the virtual directory for the application images

 

After that script gets everything setup the next step is to change the admin password. Again you run a script to accomplish this

@apxchpwd.sql

Here you will be prompted for a new password. Make sure the password has at least one capital letter, one number and one special character. I think a minimum of 6 characters are required. If you do not meet the password requirements you will not get any notification of it, the script will simply fail.

 

APEX2

After this step you are now ready to kick off Oracle Apex and begin making apps. I will cover this part in a separate blog post later.

Here is the Installation Guide made by Oracle for further reading.

Oracle Apex Installation Guide

 

Oracle Named Loops

When using nested loops in your PL/SQL code it is beneficial to use  Oracle named loops to make the code easier to read.

To name a loop you simply add <<LOOP_NAME>> before the LOOP keyword, then add LOOP_NAME at the end of the END LOOP keyword as shown below

<<MAIN_LOOP>>
LOOP
......
END LOOP MAIN_LOOP;

 

Example using Oracle Named Loops

The code below finds all prime numbers less than 50 while taking advantage of Oracle named loops.

DECLARE

   a number(3);
   b number(3);
BEGIN
   a := 2;
   <<MAIN_LOOP>>
   LOOP
      b:= 2;
      <<SUB_LOOP>>
      LOOP
         exit WHEN ((mod(a, b) = 0) or (b = a));
         b := b +1;
      END LOOP SUB_LOOP;
   IF (b = a ) THEN
      dbms_output.put_line(a || ' is prime');
   END IF;
   a := a + 1;
   exit WHEN a = 50;
   END LOOP MAIN_LOOP;
END;

As you can see the code is much easier to read when using Oracle named loops. I find it easier to keep track of the begin of end of loops when using nested loops. Other developers that read through your code will thank you too.

For more info on loops you can visit this link

Loops