ListAgg Function in 11gR2

This function has been around for a while in some other databases. It's mainly for display or reporting purposes - changing a long one dimensional list into a short two dimensional list.

Think of a card game where 13 cards are dealt to 4 players. The cards dealt to a player might look like this

Suit	Card
Heart	King
Heart	Jack
Heart	3
Spade	Ace
Spade	10
Spade	7
Spade	3
Spade	2
Diamond	Ace
Diamond	10
Diamond	8
Diamond	7
Diamond	6

But what you really want to show is this:

Suit	Cards
Heart	King, Jack, 3
Spade	Ace, 10, 7, 3, 2
Diamonds	Ace, 10, 8, 7, 6

now you can with ListAgg

The TrainTrack notation looks like:

Notice in the diagram that there is an optional OVER clause. That should be a hint that this can also be used as an analytic function. So what does that mean to you?

In our card game example, I showed only one player's cards. More likely the table would have a column for player as well, as so.

Player	Suit	Card

What I need to look at is the suit break down by player. I want the suits to repeat for each player. Using the OVER clause I can accomplish that.

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.