outer joins

Say we have two tables:

tab1  tab2
1          2
2         3
4         4
5

[sql]select t1.col1, t2.col2
from tab1 t1, tab2 t2
where t1.col1 = t2.col2(+) –pl/sql

–Informix SQL: …from tab1 t1, outer tab2 t2[/sql]

Result:
t1 t2
1   –
2   2
4   4
5   –

–this will take primary table tab1 rows and match with tab2, including tab2’s nulls

 

useful pl/sql code snippets

Create a “New Line” within a PL/SQL formula, use the following:
[sql]/*
Function to calculate the retirement date of a person
based on person’s date of birth

Parameter: p_birthdate, date
Returns: ret_date, date
*/
CREATE OR REPLACE FUNCTION get_retire_date(p_birthdate IN DATE)
RETURN date IS
ret_date date;
BEGIN
— Calculates with months: 756 = 63 * 12
ret_date := ADD_MONTHS (p_birthdate, 757);
RETURN ret_date;
END get_retire_date;[/sql]

Create a “New Line” within a PL/SQL formula, use the following:
[sql]CHR(10)[/sql]

Use ‘decode()’ with ‘nvl()’ where you can’t use an if statement:
[sql]decode(field_name, test1, test2, else)
decode(pr_grade, nvl(pr_grade, ‘ ‘), ‘,’, ‘ ‘) as grade[/sql]

Update all records in column with specific character(s):
-the following will find char 12 and turn it into a “5”, only if it is a match to “4”
[sql]– records text: “//10.10.10.4/test/myfile34.rtf”
update mytable t
set t.column_a = substr(t.column_a, 1, 11) || ‘5’ || substr(t.column_a, 13, 50)
where substr(t.column_a, 12, 1) = ‘4’;
— result AFTER update: “//10.10.10.5/test/myotherfile.rtf” [/sql]

Search and Replace function:
-the following with search for a string of text and replace
[sql]– records text: “The sky is blue”
update mytable t
set t.column_a = replace(t.column_a, ‘blue’, ‘green’);
— result: “The sky is green”[/sql]

another replace method:
[sql]– records text: “\\10.10.10.5\test\some_doc.doc”
select replace(t.column_a, ‘\\10.10.10.5\test’, ‘k:’) from mytable t
— result: “k:\some_doc.doc”[/sql]

 

how to find and identify duplicates

The following snippet finds all duplicates in a table and then separates them:

[sql]SELECT ID, LastName, FirstName
  FROM (SELECT ID,
               LastName,
               FirstName,
               RANK() OVER(PARTITION BY LastName, FirstName ORDER BY ID) AS SeqNumber
          FROM Customers)
 WHERE SeqNumber > 1;
[/sql]

RANK() assigns a number value in SeqNumber column which separate duplicate rows (SeqNumber > 1), or non-duplicate rows (SeqNumber = 1). It should then be able to find only the rows that need to be deleted and not all duplicates.

how to use exception handling in PL/SQL

Exception handling is followed after an sql command, using ‘Exception’ i.e.:

[sql]begin
DELETE FROM TABLE1 t1 WHERE t1.table_id = variable1;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO ERR_DELETE
(table_id, description)
VALUES
(t1.table_id, variable1);
end;[/sql]