Re: SQL query/update problem

ucfaabe_at_ucl.ac.uk (Mr Angus Beare) writes:

[insignificantly edited]

> I have a brain teaser, well for me anyway.
 

> I have a database which stores species abundance in the following form:-
 

> COUNTS TABLE
 
> SAMPLEID CODE COUNT
> ------------------------------
> 1112 A0023D 18
> 1112 A0021S 1
> " A0034F 2
 

> SAMPLES TABLE
 
> SAMPLEID TOTCOUNT ....etc
> ----------------------------------
> 112 21
> 113 15
> 114 11
> 115 78
 

> Each sample in the SAMPLES table is related to a batch of counts in
> the COUNTS table by unique SAMPLES.SAMPLEID. The COUNTS.CODE field is a
> species code from a taxonomic list. The TOTCOUNT field in SAMPLES is the
> sum(COUNT) from COUNTS.

This begs the question; why are you storing derived attributes? However, ...

> My problem is that I need to check that the sum of COUNTS.COUNT matches the
> TOTCOUNT field in SAMPLES where the SAMPLEID's match. Then give the user
> the option to update the ones that don't.
 

> I created a VIEW which got the sum from COUNTS
 

> # SCRIPT TO CREATE VIEW TOTCOMP */
> # To compare counts totals */
 

> create view TOTALCOMP(TOTAL, SAMPID) as
> (select sum(COUNT), SAMPLEID from COUNTS
> group by COUNTS.SAMPLEID)
 

> and then was able to join this with the SAMPLES.totcount field with the
> following query:-
 

> select TOTALCOMP.TOTAL, SAMPLES.TOTCOUNT, TOTALCOMP.SAMPID, SAMPLES.SITE,
> SAMPLES.SAMPLENAME
> from TOTALCOMP, SAMPLES
> where
> TOTALCOMP.SAMPID = SAMPLES.SAMPLEID
> and TOTALCOMP.TOTAL != SAMPLES.TOTCOUNT
 

> but, although this works adequately, it does not return any rows where
> one of the fields TOTALCOMP.TOTAL or SAMPLES.TOTCOUNT are blank. So, my
> questions are:-
 

> 1. Is there a way of returning the rows where a field is NULL?

The two fields are TOTALCOMP.TOTAL and SAMPLES.TOTCOUNT; I'm assuming that the field COUNTS.COUNT is defined as Not Null. When you say "... one of the fields ... are blank", they "are blank" for different reasons. Firstly, SAMPLES.TOTCOUNT, I assume, is allowed to have Null values; this is overcome by using the NVl function.
Secondly, if SAMPLES.TOTCOUNT really is Not Null, then this field "is blank" means that there are no entries in the COUNTS table with that particular SAMPLEID; (or, possibly, there are no entries in the COUNTS table with that particular SAMPLEID and a non-Null COUNT; a possibility that I am discounting); this is overcome by using an outer join.

Use

select TOTALCOMP.TOTAL, SAMPLES.TOTCOUNT, SAMPLES.SAMPLEID, SAMPLES.SITE,

       SAMPLES.SAMPLENAME
from TOTALCOMP, SAMPLES
where
TOTALCOMP.SAMPID(+) = SAMPLES.SAMPLEID
and NVl (TOTALCOMP.TOTAL, 0) !=

    NVl (SAMPLES.TOTCOUNT, NVl (TOTALCOMP.TOTAL, 0) + 1)

> 2. Can this be done in a more efficient way, in a query without a VIEW?

If you have indexes in the right places on the COUNTS table, then using a view gives you an insignificant performance decrease, and certainly improves the readiblity of your code; and you can bolt further selects on top of your view as well. However, if you really want to, you could use

select sum(COUNTS.COUNT), SAMPLES.TOTCOUNT, SAMPLES.SAMPLEID, SAMPLES.SITE,

       SAMPLES.SAMPLENAME
from COUNTS, SAMPLES
where
COUNTS.SAMPLEID(+) = SAMPLES.SAMPLEID
and NVl (sum(COUNTS.COUNT), 0) !=

    NVl (SAMPLES.TOTCOUNT, NVl (sum(COUNTS.COUNT), 0) + 1) group by SAMPLES.TOTCOUNT, SAMPLES.SAMPLEID, SAMPLES.SITE, SAMPLES.SAMPLENAME

Warning: this code is untested.

> 3. How should I tackle the update problem?

This depends on what sort of user interface you're thinking of; SQL*Plus, Forms, or what, and on whether you want the updates to be automatically derivable values or any old numbers that the user likes, etc.

> Any suggestions would be gratefully received.
 

> Thanks

You're welcome; hope this helps (and works!)

> Angus Beare
> abeare_at_geog.ucl.ac.uk
> UCL, London

Hank Robinson
Oracle DBA
University of Warwick Received on Wed Aug 17 1994 - 16:10:30 CEST