Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Tuesday, October 30, 2007

Format your Code for Blogger

Finally, I found a solution of how to easily format my Source Code examples for Blogger. I've seen many examples of how other blog services handle this, such as Wordpress, but I don't use Wordpress...

Here is how to easily do this, until Blogger comes up with an easier way:

Copy your source code example and paste it into this page:

Source Code Formatter for Blogger

Click on the "Format Code" button and copy the results.

Switch to "Edit Html" view in your Blogger Edit Post page and paste in the formatted code example.

Here is what a sample PL/SQL example would looks like:

BEGIN
FOR cur IN (select id from test)
LOOP
UPDATE table SET column = "DONE" WHERE id = cur.id where;
COMMIT;
END LOOP;
END:
/

Thanks to Greg Houston for this simple solution.

Sunday, June 17, 2007

Char_Instr - Another Matching Algorithm

I've using Soundex, Double Metaphone, Levenshtein Distance and other string matching algorithms, but somethings it is the simple things that work best.

Here is a function I wrote in PL/SQL utilizing the INSTR() function to traverse through one string, checking each character as to whether or not it exists in the another string.

This has been useful in checking for duplicates in mailing lists as well as identifying potential duplicates that should be reviewed. The function returns the number of characters from the second string that are the same as the first string.

I've found the use of the Soundex to be too loose and the Levenshtein Distance algorithm to be lacking in its handling of transposed characters. Use of this function will give you a count of how many chars are the same regardless of position in the string.


/*==========================================================
Function: char_instr (str1, str2)

Purpose: To check how many characters from one string

exist in another string. The return value is the number
of characters that exist in the in both strings.
==========================================================*/

FUNCTION char_instr
(
str1 IN VARCHAR2,
str2 IN VARCHAR2
)
RETURN NUMBER
AS
v_exists NUMBER := 0;
v_occurs NUMBER := 0;
v_length NUMBER;
BEGIN
/*==========================================================
Get the length of the str2
==========================================================*/
v_length := LENGTH(str2);
/*==========================================================
Loop through str2 and check if char exists in str1
=========================================================*/
IF( v_length > 0 ) THEN
FOR x IN 1..v_length LOOP
v_exists := INSTR(UPPER(str1), SUBSTR(UPPER(str2), x, 1));
IF(v_exists>0) THEN
v_occurs := v_occurs + 1;
END IF;
END LOOP;
END IF;
RETURN v_occurs;
END char_instr;
*

Thursday, June 14, 2007

SQL Cheat Sheet

I've put together this page to give quick links to all the Oracle SQL tricks that I find myself continuing to search for on the net. This is a quick reference guide and will be expanded regularly.

Oracle SQL Cheat Sheet

SQL Introduction and Definitions

INSTR()

Create a Delimited Output in SQL Plus

How to Rename a Column in Oracle SQL

How to Delete a Column from a Table

Use of Variables in SQL Plus or PL/SQL

Format Query Results to Display Currency

Display Seconds Past Midnight

Select Only Top Results in a Query

Use of Wildcards in Oracle SQL

What is NVL and how does it work?

How to Use Decode in Oracle SQL

How to Round Off a Number in Oracle SQL

Debug JDBC Connectivity with Oracle Thin Client

Oracle String Matching Algorithm - CHAR_INSTR

If you have any other tips or suggested entries, let me know!

Thursday, May 24, 2007

How to Round Up in Oracle SQL

Recently I needed to provide a report that had numbers rounded to the nearest thousand. I found the ROUND command but this appeared to be only rounding off decimal points to a specified number of spaces. The syntax for this is:

SELECT ROUND(number, decimals)
FROM table;

If you use 2 for the decimals your result would look like this:
SELECT ROUND(2.123,2) FROM DUAL;
Result: 2.12
SELECT ROUND(2.129,2) FROM DUAL;
Result: 2.13

However, I needed something that would round to the nearest thousandth. So I tried a negative number for the decimals like this and found it did exactly what I wanted:
SELECT ROUND(10234,-3) FROM DUAL;
Result: 10000
SELECT ROUND(10567,-3) FROM DUAL;
Result: 11000

While this is a basic Oracle SQL function, it is not clear that you can do this, hence this post.

Monday, March 19, 2007

Oracle Thin Client JDBC

I've had problems setting connections to an Oracle DB using the thin client JDBC. This post is to track how this was solved.

First you must have the oracle.jar file in your build path.

Next I used the following for the actual connection:



public static Connection getConnection() {

Connection con = null;
try{
Class.forName("oracle.jdbc.OracleDriver");
} catch (Exception e) {
System.out.println("Failed to load driver.");
return con;
}
try {
con = DriverManger.getConnection(
"jdbc:oracle:thin:username/pw@hostname:port:SID"
);
} catch (SQLException ee) {
ee.printStackTrace();
}
return con;
}


I also had imported java.sql.*;

The thing that was causing a problem with this before was that I did not have the host name, port or SID listed out. This was easily located in the tnsnames.ora file in the oracle installation folder.

Tuesday, February 27, 2007

Oracle SQL DECODE

The DECODE function in Oracle SQL is used for if-then-else statements. A very handy tool for powerful queries.

The syntax for the DECODE function is as follows:

DECODE(value, if1, then1, if2, then2, if3, then3, etc., else)

Here is a good example of this from the devx.com website:

Consider the following example in which the Viewable column can have values of 1, 2, and 3:

SELECT FirstName, Viewable
FROM employee;

Results:

FIRSTNAME Viewable
===========================
John 1
Tim 2
Julie 2
Stacy 1
Rahul 3
Leena 4
Amy 1
Bill 3
Teri 3

Now, we can use Decode to display different things in a report based on the values in Viewable.

SELECT Firstname,
Decode(Viewable, 1,'VISIBLE', 2,'INVISIBLE',
3,'UNKNOWN', 'OTHER')
FROM employee;

Results:

FIRSTNAME Viewable
===========================
John VISIBLE
Tim INVISIBLE
Julie INVISIBLE
Stacy VISIBLE
Rahul UNKNOWN
Leena OTHER
Amy VISIBLE
Bill UNKNOWN
Teri UNKNOWN

Decode checks the column values and interprets the provided values in pairs. This is how it works:

Switch viewable:

Case 1:
Result = VISIBLE
Case 2:
Result = INVISIBLE
Case 3:
Result = UNKNOWN
Default:
Result = OTHER
End Case

OR
====================

If Viewable = 1 Then
Result = VISIBLE
Elsif Viewable = 2 Then
Result = INVISIBLE
Elsif Viewable = 3 Then
Result = UNKNOWN
Else
Result = OTHER
End If

One can use Decode in Updates as well. For example, instead of writing 50 different updates to change state names, we can do it in one Update using Decode as follows:

Update employee
set homestate = decode(homestate,
'AL', 'Alabama',
'AK', 'Alaska',
'AS', 'American Samoa',
'AZ', 'Arizona',
. . . . . . . .
. . . . . . . .
'WA', 'Washington',
'WV', 'West Virginia',
'WI', 'Wisconsin',
'WY', 'Wyoming' , homestate)
where homecountry = 'UNITED STATES'

This will replace state abbreviations with state names. And it leaves the abbreviations alone in case it doesn't match any of the values, such as AL, AK, etc.


Most Oracle SQL users do not realize the power of this function. Hopefully this post will enlighten some on it.

Thursday, January 18, 2007

Oracle TO_CHAR function

The oracle TO_CHAR function can display seconds past midnight with the following syntax:

SELECT TO_CHAR(sysdate, 'SSSSS') FROM dual;


Further Oracle SQL/PLSQL topics

Tuesday, January 09, 2007

An SQL Introduction and Tutorial

If you need to freshen up your basics on SQL or if you need to train a new employee on a gradient approach to SQL, the following is a suggested training line-up:

First, clear up each of the following terms:

SQL
SELECT
FROM
AS
WHERE
LIKE
IN
BETWEEN
AND
OR
INTO
GROUP BY
ORDER BY
HAVING
COUNT

Next, go through this online interactive tutorial. This has the student walk through real examples using an online SQL tool so you can practice what you learn online without having to go to another computer where your database is.

An Interactive SQL Tutorial

In learning any new subject it is extremely important to start with the key words and to have these cleared up first, before carrying on with the material. This is covered in Hubbard's, Study Technology.

Here are good basic definitions for each of these SQL terms:

SQL
SQL stands for Structured Query Language and is a computer language that allows you to ask questions (query) or interact with your database in a structured manner.

SELECT
The SELECT statement indicates that you wish to query and retrieve information from a database. The select_list specifies the type of information (or column names) to retrieve. The keyword ALL or the wildcard character asterisk (*) could be used to signify all columns in the table. Also, the keyword DISTINCT could be used to discard duplicate records and retrieve only the unique records for the specified columns.

INTO
The INTO keyword indicates that you are inserting records into another table. For example in Microsoft Access you can create a new table and insert all records from another table by using the following: SELECT * INTO newtablename FROM tablename

FROM
The FROM clause is required in any SELECT statement. The FROM clause specifies the specific tables to retrieve data from.

AS
The AS keyword is used in the SELECT or the FROM clause when you want to refer to a table or column as something else. This is very handy if you have long table or column names and you want to use a shorter name to refer to them. This is commonly used when you are joining multiple tables together and do not want to rewrite the full table name each time you are referring to that table. For example:

SELECT a.column, b.column
FROM table_name_a AS a, table_name_b AS b
WHERE a.ID = b.ID

This is also commonly used to have a nicer display of your results. For example if you have a column called "field17" and you want to display it as "COUNTRY" you can use the following:

SELECT field17 as COUNTRY
FROM table_name

WHERE
The WHERE clause limits the results to those records (or rows) that meet some particular conditions (optional).

LIKE
LIKE is another SQL keyword that is used in the WHERE clause. LIKE allows you to search based on a pattern rather than specifying exactly what is desired The syntax for it is as follows: SELECT * FROM table WHERE column LIKE 'abc%'
The wildcard '%' (in Microsoft SQL and Oracle SQL) or '*' (in Access SQL) indicates any character and can be used in any location within the quotes. This will return any records that match 'abc' as the first three letters and then anything else after that such as 'abc123', 'abcdef', etc.

IN
The IN keyword is used in the WHERE clause when you want to select all records that have specific values such as if you wanted all customers that have either blue, green or hazel eyes you would use the following:

SELECT *
FROM customers
WHERE eye_color IN ('blue', 'green', 'hazel')

You can not use wildcards with the IN keyword.

BETWEEN
If you want to specify a range of values you can use the BETWEEN keyword. This will give you anything between value a and b values. For example, if you wanted any zip codes that were between '90210' and '91420' you would use the following:

SELECT *
FROM address_list
WHERE zip_code BETWEEN '90210' AND '91420'

AND
The AND keyword is used when you want to add additional criteria to your WHERE clause or as part of the BETWEEN clause.

OR
The OR keyword is used when you want either one criteria or another, but not both. Be very careful when using the OR keyword as if you have AND and OR in the same WHERE clause your result will probably not be what you expect, unless you use parentheses (). For example, if you want all people who with blue or hazel eyes that live in California, you would use the following:

SELECT *
FROM address_list
WHERE (eye_color = 'blue' OR eye_color = 'hazel') AND state = 'CA'

If you don't use the parentheses your query will return people in California, people with blue eyes or people with hazel eyes. So remember your math class where you learned to group parts of the equation to avoid confusion (a * b) + y ...

GROUP BY
The GROUP BY clause specifies if you are grouping (or aggregating) any of the columns in your SELECT statement. For example if you want to display a count of all addresses by city you would use the GROUP BY clause to group the results by City. This following example will give you a breakdown by city and sort it by most to least:

SELECT city, COUNT(*)
FROM table
GROUP BY city
ORDER BY COUNT(*) DESC

HAVING
The HAVING clause specifies the specific conditions to group by (optional). For example you may want to group your results by City but only display those Cities that have more than 10 matches. You would do this by saying "HAVING COUNT(*) > 10". HAVING always comes after the GROUP BY clause.

ORDER BY
The ORDER BY clause specifies whether to output the query result in ascending or descending order. This is often used to sort your results in alphabetical or numerical sequence.

COUNT
The COUNT keyword is used in the SELECT or HAVING clauses. This allows you to give a total count of something. This is also referred to as an "aggregate". This means you are grouping things together and displaying aggregated data on the group instead of listing each individual record. Other aggregate keywords are SUM, MIN, MAX, etc. COUNT gives you the count of records, SUM gives you the SUM of the values in a column, MIN gives you the minimum value, MAX gives you the maximum value. Here is an example of the COUNT usage:

SELECT COUNT(*)
FROM table_name
WHERE last_name = 'SMITH'

This will give you a count of all records with the last name of 'SMITH'. Notice the (*) after the keyword. This means you want to give a count of all records meeting the criteria in your WHERE clause. But you may also see COUNT(1) which also means give a total number of the records. The COUNT keyword is very useful in giving reports or breakdowns of various columns. For example if you want a breakdown of all records you have for each eye_color (if you have this column in your table) you could use the following:

SELECT eye_color, COUNT(1)
FROM table_name
GROUP BY eye_color
ORDER BY COUNT(1) desc

Notice the GROUP BY and ORDER BY keywords used as well. This will show you the count by eye_color and will sort it by the the highest number to the lowest.
Here is another simple introduction to SQL.


Monday, December 18, 2006

Oracle SQL Function - INSTR()

I found it. The use of INSTR() simulates the AWK Function INDEX() and does a search of the first argument to see if the second argument exists and then returns the position where this is first found. Here is the syntax:

INSTR(str, chars[,s[,n]])
Find the 'n'th occurence of 'chars' in 'str'
Starting at position 's'
n and s default to 1
This was decribed in the following page which lists out all the Oracle SQL Functions and is a very useful link:

Oracle SQL Functions

To see a full listing of all Oracle SQL Functions you can run the following query:
SELECT distinct object_name
FROM all_arguments
WHERE package_name = 'STANDARD';

Looking for Index() function in Oracle SQL

From my blog you probably can tell I have an infatuation with Awk. Well today I was trying to work out a problem in Oracle SQL where the index() function would have worked perfectly. I'm checking around to find if there is any built in function that searches an existing column for a string and returns the starting point if it exists. Oracle SQL does have the substr() function which I've found quite useful...

Friday, December 01, 2006

SQL Plus Delimited Output File

Ad hoc queries anyone?

Something that has always been a pain is when you need a fast report generated from your latest SQL script. You probably just resort to executing the query in TOAD or another query tool, and then saving it off to a file. That is about 5 mouse clicks too many and you just wish you could click a button to run your script and dump out the results to a nicely formatted text file. Using SQL Plus and a batch script, you can set up an ad hoc query tool for yourself that dumps out the results to a delimited output. This is actually quite simple:

You need three files, a) the SQL query you want to run, b) the SQLPlus script that will run your query and c) a batch program to run your SQLPlus script.

Here is how I did it:

a) queryOut.sql - This contains a query set up for pipe-delimited output. You can change the delimiter to whatever you wish. Here is a sample query -

SELECT field1 || '|' ||
field2 || '|' ||
field3
|| '|' ||
field4
FROM {table}
WHERE {criteria};
b) queryToFile.sql - This contains the SQL Plus script including formatting options so you can dump out a file with your results and no other fluff. Here are the settings I used -
set termout off
set feedback off
set verify off
set echo off
set pagesize 0
set linesize 200
set trimspool on
column dt new_Value mydate noprint
select to_char(sysdate, 'YYYYMMDD') dt from dual;

spool rpt&mydate..txt
@queryOut.sql;
spool off
exit;
c) runQuery.bat - This is the simple batch program that calls SQLPLUS with your username and password and the script to run. Here is what this would look like -
SQLPLUS /@ @queryToFile.sql
I set up the above in a directory for these ad hoc queries and then edit my queryOut.sql file and click on the runQuery.bat which I added to a toolbar. Try it out and let me know if you found this useful or if you have any better ideas...

Friday, November 24, 2006

How to use Decode in Oracle SQL

The use of the decode function in Oracle SQL is pretty straightforward. You simply specify the column that you need conditional logic on and then specify the conditions as follows:

SELECT DECODE (value,
<if this value>, <return this value>,
< if this value>, <return this value>,
....)
FROM dual;


If you have a default value that you want to display if any of the conditions are not met, you can add another comma and specify the default value like this:

SELECT DECODE (value,
if this value, return this value,
if this value, return this value,
otherwise this value
FROM dual;



For a thorough description of all features of this DECODE function, see the Puget Sound Oracle User's Group description.


Here are some further examples from another post on my blog:

Most Oracle SQL users do not realize the power of this function. Hopefully this post will enlighten some on it.

Thursday, November 09, 2006

NVL - Oracle SQL Null Field Handling

An often overlooked function that comes in very handy is the NVL function in Oracle. This allows you to replace a query result that returned NULL, with a value, such as 0. Here is the syntax:

SELECT NVL(fielda, 0) FROM table;

This will always give you either a value or a "0". This will never return a NULL.

Here is a link for further details on this function:

NVL Function

Oracle Wildcards

When searching using wildcards in Oracle SQL the most common use is the "%" character which searches for any number of characters. However a useful but less used wildcard is the "_" character which searches for any single character. These can be used in combination to form complex querying.

If you need to query for something that has a wildcard character in it, use the ESCAPE syntax as follows:

SELECT * FROM table WHERE field LIKE '%\%%' ESCAPE '\'

This will treat the '%' as a literal instead of as a wildcard.

Here is another good oracle sql resource:

Puget Sound Oracle Users Group

Thursday, November 02, 2006

How to Delete a Column from a Table in Oracle SQL

Here is the syntax to delete one column from an existing table in Oracle SQL. As this is not used very often I am noting it here for quick reference.

     ALTER TABLE employee DROP COLUMN vacationPay;
Pretty simple.

Wednesday, November 01, 2006

How to Select Top Results in a Group By

The other day I ran into an interesting problem with Oracle SQL. I used to use Microsoft SQL Server and last year switched over to Oracle and had to adjust to the peculiarities of Oracle SQL. I had a report that gave the top 10 results of an aggregate query. In SQL this was simple and looked something like this:

SET ROWCOUNT 10
SELECT field1, field2, COUNT(*)
FROM table
GROUP BY field1, field2
ORDER BY COUNT(*) DESC
But when trying to do this in Oracle SQL with no "ROWCOUNT" feature, I was stumped at first. Then I came up with the following which is a bit of a hack, but works:
SELECT field1, field2, CNT
FROM (
SELECT field1, field2, COUNT(*) AS CNT
FROM table
GROUP BY field1, field2
ORDER BY COUNT(*) DESC
)
WHERE ROWNUM <= 10;
If anyone knows of a more standard approach, let me know.

Thursday, October 26, 2006

Oracle SQL Tips

I often am in the middle of some off-the-cuff query and run into one of those technical "gotchas" like a failed compare between two text strings due to trailing spaces or that alter table syntax to modify a column name.

Instead of googling for the answer each time, I've consolidated the websites that I use to find the answers to these common SQL questions and have listed these below for my own and others reference.

Oracle FAQ
Oracle Cheat Sheet
Oracle User Guide

These are the most frequently used resources. I may add to this later but the above will solve most all of your Oracle SQL questions.

Wednesday, October 25, 2006

Formatting Query Results

I wanted to format a query result so it displayed as currency. To do this you need to use the TO_CHAR function with the following syntax:

SELECT TO_CHAR(A_NUMBER, 'L999,999,999.99')
FROM TABLE;

This gives you a formatted output in the local currency format.

How to Use Variables in PL/SQL or SQLPlus Scripts

I am researching how to set up my SQLPlus script to be able to output a text report using a generic script that reads in a variable.

I want to set variable on the command line and then have the script reference this throughout, giving me customized counts based on the variable entered. The results should output to a text file that can then be used as the actual report. I am familiar with how to do this in a PL/SQL script but not in a regular SQL script that I would use in SQL Plus.

The following link helped me with the setting up of my variable.

Detail on how to use variables in SQLPlus

Specifically here were the instructions:

9.1 Setting a Substitution Variable's Value

A substitution variable can be set in several ways. The common ways are given below.

1. The DEFINE command sets an explicit value:

define myv = 'King'

This creates a character variable "myv" set to the value "King".

2. The ACCEPT command:

accept myv char prompt 'Enter a last name: '

prompts you for a value and creates a character variable "myv" set to the text you enter.

3. Using "&&" before an undefined variable prompts you for a value and uses that value in the statement:

select first_name from employees where last_name = '&&myuser';

If the substitution variable "myuser" is not already defined then this statement creates "myuser" and sets it to the value you enter.

4. Using COLUMN NEW_VALUE to set a substitution variable to a value stored in the database:

column last_name new_value mynv
select last_name from employees where employee_id = 100;

This creates a substitution variable "mynv" set to the value in the "last_name" column.



When outputting to a file, I like to use include the date in the file name:


Using SYSDATE you can query the current date and put it in a substitution variable. The substitution variable can then be used in a SPOOL command:

column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDD') dcol from dual;

spool &mydate.report.txt
-- my report goes here
select last_name from employees;
spool off


Be sure to use the double period after the variable name in your file name:

If SET CONCAT is a period (.) and you want to append a period immediately after a substitution variable then use two periods together. For example:

define mycity = Melbourne
spool &mycity..log

is the same as:

spool Melbourne.log


Use the variable that you set like this:

define dept = '60'
ttitle left 'Salaries for department &dept'
select last_name, salary from employees where department_id = &dept;


Or pass in the variable on the command line like this:

You can pass parameters on the command line to a SQL*Plus script:

sqlplus hr/my_password @myscript.html employees "De Haan"

They can be referenced in the script using "&1" and "&2". For example, myscript.sql could be:

set verify off
select employee_id from &1 where last_name = '&2';


That handled my main concern and gives me a nice text output file that uses my generic script with a variable.

The next point I sought to resolve was how to have my text output be in a delimited format as I plan to read this from an Excel file.

Here is what I found to do this:

--sets sqlplus so output will be tab-delimited
set colsep ' ' /* that's a tab */
SET LINESIZE 3000
SET PAGESIZE 50000
SET ECHO OFF
SET FEEDBACK OFF
--SET HEADING OFF
set trimspool on
set newpage none
set underline off


Here is someone else's point of view on how this should be done.
Digital Voice

Instead of either of the above a simple handling would be to hard code into the generic script "|" between the columns. This would give me a standard pipe-delimited output.

That sounds like the easiest method and is what I commonly use.

Tuesday, September 26, 2006

Rename Column in Oracle SQL / PLSQL

To rename a column in an existing table use the following:

ALTER TABLE tablename RENAME COLUMN oldcolumn TO newcolumn;