Errata

The table name is missing from line 2 of the MYSQL solution. it should read:

from emp

instead of just

from

A.M. - For page 36, please remove the following (located at the top of the page):

"As you can see, the condition DEPTNO NOT IN ( 10, 50,NULL) equates to:
not (deptno=10 or deptno=50 or deptno=null)
In the case where DEPTNO is 50, hereís how this expression plays out:
not (deptno=10 or deptno=50 or deptno=null)
(false or false or null)
(false or null)
null"

A.M. - The top of page 36 should begin with the sentence:

"In SQL, ìTRUE or NULLî is TRUE, but ìFALSE or NULLî is NULL!"

A.M. - The sentence on the bottom of the page reads:

"Alternatively, union the results of the two different outer joins:"

A.M. - but it should read:

"Alternatively, since MySQL does not yet have a FULL OUTER JOIN, union the results of the two different outer joins:"

A.M. - a minor adjustment. In the solution section of DB2 and MYSQL on page 73, the code on line 4 reads:

4 where exists ( select null

A.M. - but it should read:

4 where exists ( select *

A.M. - The MySQL solution is the following:

MySQL

Include both EMP and NEW_SAL in the UPDATE clause of the UPDATE statement and join in the WHERE clause:

1 update emp e, new_sal ns
2 set e.sal=ns.sal,
3 e.comm=ns.sal/2
4 where e.deptno=ns.deptno

A.M. - The discussion must be modified on page 74. The DB2 discussion should no longer include MySQL
A.M. - and MySQL should be included in the PostgreSQL and SQL Server discussion.
A.M. - Since there are now 3 rdbms' mentioned in the discussion, the first sentence of the discussion
A.M. - for PostgeSQL, SQL Server, and (now) MySQL should be:

"The syntax is a bit different between these platforms, ..."

A.M. - the word "two" has been removed.

"
For MySQL users, you will need slightly different syntax because you cannot reference the same
table twice in a delete (as of the time of this writing):

delete from dupes
where id not in
(select min(id)
from (select id,name from dupes) tmp
group by name)

"

The query presented for DB2 to 'find the constraints and the columns they are on'
actually finds the contraintns and then assigns each constraint to every column of
the table. I haven't been able to find a query that produces the correct result in
DB2.

AUTHOR'S REPLY:

Here is the correct query for that recipe:

select a.tabname, a.constname, a.colname,
(select b.type from syscat.tabconst b
where a.tabname = b.tabname
and a.tabschema = b.tabschema
and a.constname = b.constname) as type
from syscat.keycoluse a
where a.tabname = 'EMP'
and a.tabschema = 'SMEAGOL'
UNION
select tabname, constname, colname, usage
from syscat.colchecks
where tabname = 'EMP'
and tabschema = 'SMEAGOL'

KEYCOLUSE shows all columns participating in primary,
Foreign, or unique keys.
COLCHECKS shows columns participating in check constraints.

A.M. - the closing parenthesis

The query doesn't give a running difference. It simply returns the first sal (ordered
by empno) then sums the rest and returns them as negative.

upon running it we see:

select a.empno, a.ename, a.sal,
(select case when a.empno = min(b.empno) then sum(b.sal)
else sum(-b.sal)
end
from emp b
where b.empno <= a.empno
and b.deptno = a.deptno ) as rnk
from emp a
where a.deptno = 10

EMPNO ENAME SAL RNK
---------- ---------- ---------- ----------
7782 CLARK 2450 2450
7839 KING 5000 -7450
7934 MILLER 1300 -8750

to get the correct result set, use a view:

create view emp_vw as
select a.deptno, a.empno, a.ename,
case (select count(*)
from emp b
where b.empno <= a.empno
and b.deptno = a.deptno )
when 1 then a.sal else -a.sal end as sal
from emp a
where a.deptno = 10

and then simply do a running total:

select a.empno, a.ename, a.sal,
(select sum(b.sal)
from emp_vw b
where b.empno <= a.empno
and b.deptno = a.deptno ) as diff
from emp_vw a

EMPNO ENAME SAL DIFF
----- ---------- ---------- ----------
7782 CLARK 2450 2450
7839 KING -5000 -2550
7934 MILLER -1300 -3850

My apologies. This was a silly mistake for an easy query.

When 1/31/2006 is substituted for current_date, the query determines the last day of January to be 1/28/2006.

This indeed an error. To fix the error you can use:

select
(date('2006-01-31') - day(date('2006-01-31')) day + 1 day)
firstday,
(date('2006-01-31')+1 month - day(date('2006-01-31')+1 month) day)
lastday
from t1

running it from db2 cmd line:

db2 => select
db2 (cont.) => (date('2006-01-31') - day(date('2006-01-31')) day + 1
day)
firstday,
db2 (cont.) => (date('2006-01-31')+1 month - day(date('2006-01-31')+1
month)
day) lastday
db2 (cont.) => from t1

FIRSTDAY LASTDAY
---------- ----------
01/01/2006 01/31/2006

1 record(s) selected.

The bug that crept into the original recipe is that it subtracted the numeric day value of the current date, when instead it
should use the numeric day value of the current date after 1 month has been added to it.

So, the new recipe that should be put in print in place of the current db2 recipe is this:

select
(date(current_date) - day(date(current_date)) day + 1 day)
firstday,
(date(current_date)+1 month - day(date(current_date)+1 month) day)
lastday
from t1

also, the discussion should be replaced with the following (for DB2 only ofcourse):

"To find the first day of the month simply find the numeric value of the current day of the month then subtract this from the
current date. For example, if the date is March 14th, the numeric day value is 14. Subtracting 14 days from March 14th gives
you the last day of the month in February. From there, simply add one day to get to the first of the current month. The
technique to get the last day of the month is similar to that of the first; substract the numeric day of the month from the
current date to get the last day of the prior month. Since we want the last day of the current month (not the last day of the
prior month), we need to add one month to the current date."

A.M. - On page 233, for the SQL Server solution, the code on line 3 reads:

3 -day(getdate()),

A.M. - please change that to

3 -day(dateadd(month,1,getdate())),

A.M. - Since I am changing the code a bit, the explanation should be changed as well.
A.M. - In the paragraph explaining the solution the last sentence reads:

"
To get the last day of the month, add one month to the current date, and then substract
from that result the value returned by the DAY function applied to the current date plus 1 month,
again using the functions DAY and DATEADD:
"

A.M. - Additionally, a sentence must be changed on page 234 in the discussion section for the sql server
A.M. - solution. Please change the last sentence for the sql server discussion on page 234 to read:

"Add one month to the current date, then substract from it the value
returned by DAY(DATEADD(MONTH,1,GETDATE())) to get the last day of teh current month.
"

with x (tree,mgr,depth)
as (
select cast(ename as varchar(100)),
mgr, 0
from emp
where ename = 'MILLER'
union all
select cast(x.tree+'-->'+e.ename as varchar(100)),
e.mgr, x.depth+1
from emp e, x
where x.mgr = e.empno
)
select tree leaf___branch__root
from x

NOW READS:
with x (tree,mgr,depth)
as (
select cast(ename as varchar(100)),
mgr, 0
from emp
where ename = 'MILLER'
union all
select cast(e.ename as varchar(100)),
e.mgr, x.depth+1
from emp e, x
where x.mgr = e.empno
)
select tree leaf___branch__root, depth
from x

From the author:
There is only one solution shown using the MODEL clause to create csv output from
rows. The goal was to show two solutions, one without iteration and one using
iteration (to highlight how much leaner code using MODEL can be if you iterate).

here's the alternative solution using MODEL:

select deptno, rtrim(ltrim(emps,','),',') emps
from (
select deptno,ename,idx,emps
from emp
model
partition by (deptno d)
dimension by (row_number()over(partition by deptno order by empno) idx)
measures (cast(null as varchar2(50)) emps, cast(null as varchar2(50)) tmp,
deptno,ename)
rules iterate (1000) until ename[iteration_number+1] is null
(
tmp[iteration_number+1] = ename[iteration_number+1],
emps[1] = emps[1] || ',' || tmp[iteration_number+1],
deptno[iteration_number] = deptno[1]
)
)
where idx = 1
order by 1

DEPTNO EMPS
------ --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

the solution on page 512 is 34 lines long, this one is half that.
that's the point of showing this, that in some cases using iteration can simplify
your sql.

This account is confusing because a single symbol is used to denote "is a member of" and also "is not a member of".

Thus on the line below the one beginning "At this point" the "e" symbol should have a line through it and similarly in the two other occurrences on that page.

If my understanding is wrong then the text needs to be clarified to explain why the same symbol has two different and opposing meanings

Note from the Author or Editor:
This is a typo. Set membership uses the Greek lowercase epsilon. Non-membership uses the Greek lowercase epsilon with a line through it.

So, instead of (x &#949; x) it should be (x ~&#949; x)

but instead of using a tilde, please use a line through on epsilon (a strikethrough or whatever it's called).

Likewise,

(x &#949; y | x &#949; x) should be changed to (x &#949; y | x ~&#949; x)

and

(y &#949; y | y &#949; y) should be changed to (y &#949; y | y ~&#949; y)