Have you ever written a query that had a redundant subquery?

You can predefine inline views and then just use the alias in your sql statement. The WITH clause precedes a SELECT statement and looks like this:

WITH
dept_costs AS (
SELECT department_name, SUM(salary) dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) avg
FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total >
(SELECT avg FROM avg_cost)
ORDER BY department_name;

How do I Insert or Update all in one statement?

Isn't it a pain when you've got a bunch of records and some of them you need to insert and some you need to update an existing record? You've got to select out the records which aren't in there and then update the ones that already there. A real pain, no doubt.



If your database is Oracle 9i or higher you can use the Merge statement. It looks like this:


MERGE [hint] INTO [schema.] table [t_alias] USING [schema.]
{ table view subquery } [t_alias] ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

merge_update_clause
UPDATE SET column = { expr DEFAULT } [, column = { expr DEFAULT }]...

merge_insert_clause
INSERT ( column [, column]... ) VALUES ( expr [, expr]... )

EXAMPLE:


MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);

How do I Insert or Update all in one statement?

Isn't it a pain when you've got a bunch of records and some of them you need to insert and some you need to update an existing record? You've got to select out the records which aren't in there and then update the ones that already there. A real pain, no doubt.



If your database is Oracle 9i or higher you can use the Merge statement. It looks like this:


MERGE [hint] INTO [schema.] table [t_alias] USING [schema.]
{ table view subquery } [t_alias] ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;

merge_update_clause
UPDATE SET column = { expr DEFAULT } [, column = { expr DEFAULT }]...

merge_insert_clause
INSERT ( column [, column]... ) VALUES ( expr [, expr]... )

EXAMPLE:


MERGE INTO bonuses D
USING (SELECT employee_id, salary, department_id FROM employees
WHERE department_id = 80) S
ON (D.employee_id = S.employee_id)
WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
VALUES (S.employee_id, S.salary*0.1);

NOT IN.... what a bugger.

Say you've written a query to get a list of employees who have not attended training.

SELECT count(*) FROM employee e
WHERE emp_id NOT IN
(SELECT emp_id FROM trained_emps)

and the result is zero yet you can demonstrate that there are emp_ids in the the employee table which are not in the trained_emps table. What gives?


You need to remember that Oracle uses three value logic, True, False and Null.
1 = 0 is FALSE
1 = 1 is TRUE
1 = NULL is NULL
When you check if something is IN a list, you only need one TRUE for the clause to be TRUE.
When you check if something is NOT IN a list you need ALL FALSE. If you get a NULL that is not a FALSE and therefore it is NOT TRUE that you have ALL FALSE.
If I say, WHERE 1 IN ( 1, 2, 3, 4, NULL), I get 1 TRUE, 3 FALSE, & 1 NULL. I have one TRUE and therefore IN is TRUE.
If I say, WHERE 1 NOT IN ( 2, 3, 4, NULL), I get 3 FALSE, & 1 NULL. I have one NULL and therefore everything isn’t FALSE so NOT IN is FALSE.
However…
NOT EXISTS says that there is no TRUE.
If I say, WHERE 1 NOT EXISTS* ( 2, 3, 4, NULL), I get 3 FALSE, & 1 NULL. I have no TRUE and therefore NOT EXISTS is TRUE.

* You can’t just plug in a list, but you get the drift.
You can also write this without using NOT.

Thank God for the newsgroups of 1997 and Google

A colleague who has since moved on to a new engagement emailed me about a vexing problem with some code which has been working for years and just started to error out. It is VB code retrieving results from Oracle and the error being returned was ORA-01422 Fetch too many rows. Don’t jump to conclusions, this isn’t an INTO statement -- we aren’t looking for just one row.

If mbIsADO Then
'ADO connection
Set oADORSet = moConnection.OpenResultSet(sSql)

I have to assume the comments and the variable names have meaning don’t I? Not so fast. When I look up the object model for ADO, I don’t see an OpenResultSet method of the ADO Connection object. Hmm… Clearly the code says if I’ve set some module level Boolean flag IsADO to TRUE, I expect that what I am using are ADO Objects. Right? Ah, but RDO has that method on it’s connection object. Googling RDO on this topic is more fruitful.

Part way through the back and forth, this friend mentioned that the query worked in SQL*Plus and it returned 122 rows which was the right answer. This got me thinking.

1. The company is growing so this query probably also has grown to fetch more records than it used to
and thus…
2. passed some threshold which is now causing the error.


I found this thread, which says that RDO [queries] have a MaxRows property and the default is 100 rows.

I suggested that they add a single line

oADORSet.MaxRows = 0

They should be fine. I’m going out on a limb publishing this before I know if this is correct. We shall see.

Other references are here and here.