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
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]
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.
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]
[sql]SELECT emp, COUNT(emp) AS NumOccurrences
FROM users GROUP BY emp
HAVING ( COUNT(emp) > 1 )[/sql]
also, find only one record:
[sql]SELECT emp
FROM users GROUP BY emp
HAVING ( COUNT(emp) = 1 )[/sql]
Comments