SQL QUERIES

I want to report:
1. Which device/software that (practically) never shows up some problems.

select item from items_tbl
where item not in (select distinct item from problem_tbl);

(there may be more efficient ways to form this query)

2. A top 10 of the most occuring problems

select problem, count(problem) as num from problem_tbl
group by problem order by num desc limit 10;

3. The need of more time that a employee needs to solve a problem over the
average solving time.

It is expensive to calculate an average from scratch each time you want
to compare a row with it. So I would suggest having a table which holds
the expected times for each problem (or problem type), which you can
then adjust as needed.

begin;
delete from timing_tbl;
select problem, avg(solution_time) as solution_time
into timing_tbl from problem_tbl
group by problem;
end;

select employee as stupid from problem_tbl p, timing_tbl t
where (t.problem = p.problem)
and (p.solution_time > 1.5*t.solution_time);

hth

- Thomas

I have a tabl called 'unit' with a field 'room' of type character
varying(20). I want to change the type to character varying(40).
How do I do this without affecting any data in the table.

Thanks�

--
David A Dickson
david.dickson@mail.mcgill.ca

You can either:

a) create table unittemp as select field1, field2, cast(room as
varchar(20)), ..., fieldn from unit;
drop table unit;
alter table unittemp rename to unit;

b) pg_dump -t unit [dbname] > unit.pgsql
edit unit.pgsql and change varchar(20) to varchar(40) and add a line to
beginning of file "drop table unit"
pgsql [dbname] < unit.pgsql

Frank

At 12:03 PM 2/4/02 -0500, David A Dickson wrote:

You can either:

a) create table unittemp as select field1, field2, cast(room as
varchar(20)), ..., fieldn from unit;
drop table unit;
alter table unittemp rename to unit;

b) pg_dump -t unit [dbname] > unit.pgsql
edit unit.pgsql and change varchar(20) to varchar(40) and add a line to
beginning of file "drop table unit"
pgsql [dbname] < unit.pgsql

Frank

At 12:03 PM 2/4/02 -0500, David A Dickson wrote:

I have a tabl called 'unit' with a field 'room' of type character
varying(20). I want to change the type to character varying(40).
How do I do this without affecting any data in the table.

Has anyone discovered any workaround to the lack of a full ALTER
TABLE that preserves the OID for each row? I am finding that for
insurance I have to assign each table an SERIAL of my own to create
an autoincremented ID that I can control - because using OID as a key
requires me to update the key throughout the database if I have to
drop the table.

Elaine Lindelef

You could add "-o" option to pg_dump in option (b) to preserve oid's.

At 11:08 AM 2/4/02 -0800, Elaine Lindelef wrote:

On Mon, Feb 04, 2002 at 11:08:57AM -0800, Elaine Lindelef wrote:

Has anyone discovered any workaround to the lack of a full ALTER
TABLE that preserves the OID for each row? I am finding that for
insurance I have to assign each table an SERIAL of my own to create
an autoincremented ID that I can control - because using OID as a key
requires me to update the key throughout the database if I have to
drop the table.

Probably a good idea anyway since OIDs are not guarenteed to be unique. At
least by using a SERIAL you will notice when the it wraps around. I think in
7.2 the OID column will be optional.
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/