Hi Folks:
I have data in a table (listed below as tissue_inventory_animals).
Each row includes an animal number and a species. I have an sql statement (below) that is able to find all the animal numbers in the tissue_inventory_animals table that don't exist in the swine.historical table. (and it works fine).
The problem is we have three different historical tables (swine.historical, cattle.historical, and sheep.historical) so the species column listed in the tissue_inventory_animals table will determine which of the three should be checked.
Each species has their own historical table.
This select code (which is currently set to check the swine.historical table) works fine and brings back any numbers that don't exist in the swine historical table (meaning they either entered the animal number wrong, or entered the wrong species (which can be cattle, sheep, or swine).
SELECT animal
FROM genome.tissue_inventory_animals TI
WHERE NOT EXISTS
(SELECT animal
FROM swine.historical
WHERE swine.historical.animal = TI.ANIMAL);
So the select statement is checking the animal number in the tissue_inventory_animals table against the historical table and if it does NOT EXIST it is output.
That works fine, except if the animal is a cattle, then the table to be checked instead of swine.historical would be cattle.historical. For sheep the table to see if the number exists would be sheep.historical.
My idea would be to have an if statement check to see if the species was 'swine', or 'cattle', or 'sheep'. Something like the following:
DECLARE
SPECIES_TYPE VARCHAR2 (3);
BEGIN
SELECT SPECIES INTO :SPECIES_TYPE FROM genome.tissue_inventory_animals;
-- only one species (cattle, sheep, or swine) will ever be in the table at a time)
IF :SPECIES_TYPE = 'swine'
THEN
SELECT animal
FROM genome.tissue_inventory_animals TI
WHERE NOT EXISTS
(SELECT animal
FROM swine.historical
WHERE swine.historical.animal = TI.ANIMAL);
END IF;
IF :SPECIES_TYPE = 'cattle'
THEN
SELECT animal
FROM genome.tissue_inventory_animals TI
WHERE NOT EXISTS
(SELECT animal
FROM cattle.historical
WHERE cattle.historical.animal = TI.ANIMAL);
END IF;
IF :SPECIES_TYPE = 'sheep'
THEN
SELECT animal
FROM genome.tissue_inventory_animals TI
WHERE NOT EXISTS
(SELECT animal
FROM sheep.historical
WHERE sheep.historical.animal = TI.ANIMAL);
END IF;
END;
At this point the select statement that is within the if statement gives an error as too many rows are being returned. Not sure how to proceed. (In my practice data I have several numbers that are purposefully incorrect, and I need to be able to list them all.)
My ultimate goal is to return all non-existing numbers and output them in an apex interactive grid. (or at least list them so the end users can correct the animal numbers.
Here is the code for the tissues_inventory_animals table:
CREATE TABLE GENOME.TISSUE_INVENTORY_ANIMALS
(
ANIMAL VARCHAR2 (9 BYTE),
COLLECTION_DATE DATE,
PROCESSING_DATE DATE,
LOCATION VARCHAR2 (20 BYTE),
SPECIES VARCHAR2 (12 BYTE)
);
Here is the shortened code for the cattle.historical table:
CREATE TABLE CATTLE.HISTORICAL
(
ANIMAL VARCHAR2 (8 BYTE) NOT NULL,
BIRTH_DATE DATE NOT NULL,
SEX NUMBER (1) NOT NULL,
LINE NUMBER (4) NOT NULL );