Hi,

This is a great book, and saves lots of time
if want to be a SQL*Plus power user. I'd like
to add one suggestion to p145, the DEFINE
command. Jonathan showed us how to define
CHAR substitution/user variables. Its also
possible to create NUMBER type variables.

-- define rolled_in total, but as number
col RT new_val r_tot format 999999 noprint
select sum(max(rollin_district)) RT
from log_activity
where trunc(first_login_date) = trunc(sysdate-&1)
group by name_district
/

Or, a simple DEFINE to NUMBER with 0 value.

-- define rollin = 0, but as number
col val99 new_val rollin noprint
select to_number(0) val99
from DUAL
/

SQL> define
DEFINE _O_VERSION = "Oracle8 Enterprise Edition Release 8.0.5.1.1 - Production
PL/SQL Release 8.0.5.1.0 - Production" (CHAR)
DEFINE _O_RELEASE = "800050101" (CHAR)
DEFINE R_DATE = "15-Apr-99" (CHAR)
DEFINE R_TOT = 2511 (NUMBER)
DEFINE ROLLIN = 0 (NUMBER)
SQL>

Then you can use these substitution/user
NUMBER variables in SQL statements in your scripts:

select to_char((sum(decode(
to_char(first_login_date,'hh24'),
0,1,1,1,2,1,3,1,4,1,5,1,6,1,0)) +
sum(decode(to_char(first_login_date,'hh24'),
7,1,0)) +
sum(decode(to_char(first_login_date,'hh24'),
8,1,0)))*100/&r_tot,'999.0')||'%' m
from log_activity a

Great job with this book.

Regards,

Mike Thomas