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

 

 

 

Remove Duplicate Rows from DataTable in C#

Remove Duplicate Rows from DataTable in C#

While it is best to get distinct values directly from the SQL statement before entering them into a datatable there is an easy way to remove duplicate rows from datatable in C#.

The following code snippet shows how to remove duplicate rows from datatable in C#.

RemoveDuplicate(ref oInfo); //Call to the method/function

.....

internal static void RemoveDuplicate(ref DataTable oTable){
    DataView dv = oTable.DefaultView;
    //passing true here will only return distinct records
    oTable = dv.ToTable(true); 
  }

In the code we pass a datatable to the RemoveDuplicate function by reference. Then we create a DataView with values from the DataTable. Once we have the rows in the DataView we can move them back to the DataTable using ToTable(true) which the only moves disinct rows to the DataTable.

You will want to make sure to pass by reference instead of pass by value so that the values are changed on the DataTable. Pass by reference will change the variable at that memory location instead of making a copy of the variable.

More Info on Passing Parameters

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