Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to retrieve these missing rows ?

Christian PitetJul 26 2016 — edited Sep 21 2016

Hi,

I have a SQL request for fetching data with a lot of jointures involved. The main table is IFF_OBSERVATION. The problem is that this table has 8595 rows but my request retrieves only 8408 rows. That makes 187 rows 'missing'. I don't know how to do to see where my request is missing these rows. This is why I post this thread. Is there a way to detect where the request is wrong ? I am sorry i have not posted the tables data due to the elevated number of tables involved.

SELECT a."ID" "Edit.",


  1 "Qté",


  b.LB_NOM "Nom latin",


  b.NOM_VERN "Nom vernaculaire",


  b.PHYLUM "Phylum",


  b.CLASSE "Classe",


  b.ORDRE "Ordre",


  b.FAMILLE "Famille", 


  c.LIBELLE "Campagne",


  c.DATE_DEBUT "Début campagne",


  c.date_fin "Fin campagne",


  a."DATE_OBSERVATION" "Date observation",


  e."ZONE_" "Zone",


  d.CODE  ||' - '  ||d.DESCRIPTIF "Station",


  a.NICHEUR_SUR_SITE "Nicheur",


  f.HABITAT_STOC "Habitat",


  f.LIEU "Lieu de nidification",


  g.IDF "Statut Ile de France",


  g.IND_RAR_REG "Ind. de rareté régionale",


  g.IND_RAR_DEP "Ind. de rareté départementale",


  (SELECT NOM_PROTOCOLE FROM iff_protocole WHERE iff_protocole.id = a.protocole) AS "Protocole",


  (SELECT NOM FROM iff_structure  WHERE iff_structure.id = (SELECT structure FROM iff_observateur  WHERE iff_observateur.id = a.id_observateur )) AS "Structure"


FROM IFF_OBSERVATION a,


  TAXREF b,


  IFF_CAMPAGNE c,


  IF_2011_STATIONS@SIGESRI_FAUNE d,


  IF_2011_ZONES@SIGESRI_FAUNE e,


  IFF_HABITAT f,


  IFF_LISTE_ROUGE g


WHERE a.CD_NOM      = b.CD_NOM


AND   a.CD_NOM IS NOT NULL


AND   a.ID_CAMPAGNE = c.ID


AND   a.STATION     = d.CODE


AND   a.ZONE        = e.NUMZONE


AND   a.CD_NOM      = f.CD_NOM 


AND   a.CD_NOM      = g.CD_NOM

This is a sample of data 

CREATE TABLE "IFF_OBSERVATION" ("ID" NUMBER, "DATE_OBSERVATION" DATE, "STATION" VARCHAR2(8 BYTE), "CD_NOM" NUMBER, "ID_CAMPAGNE" NUMBER, "ID_OBSERVATEUR" NUMBER(32,0), "COMMENTAIRE" VARCHAR2(128 BYTE), "NICHEUR_SUR_SITE" VARCHAR2(2 BYTE), "NOUVEAU_TAXON" VARCHAR2(64 BYTE), "CREATED_BY" VARCHAR2(255 BYTE), "CREATED_ON" DATE, "UPDATED_BY" VARCHAR2(255 BYTE), "UPDATED_ON" DATE, "PROTOCOLE" NUMBER(*,0), "ZONE" NUMBER)


REM INSERTING into IFF_OBSERVATION


SET DEFINE OFF;


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14659',null,'B4','4355','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14660',null,'B4','4355','162',null,null,'NN',null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14661',null,'B4','3764','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14662',null,'B4','4351','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14663',null,'B4','4525','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14664',null,'B4','4532','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14665',null,'B4','3283','162',null,null,'NN',null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14666',null,'B4','2741','162',null,null,'NN',null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14667',null,'B4','2763','162',null,null,'NN',null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14668',null,'B4','2989','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14669',null,'B4','2975','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14670',null,'B4','3448','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14671',null,'B4','3136','162',null,null,'NN',null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14672',null,'B4','4198','162',null,null,'NN',null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14673',null,'B4','3611','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14674',null,'B4','3630','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14675',null,'B4','3603','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14676',null,'B4','4474','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14677',null,'B4','3420','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14678',null,'B4','3422','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14679',null,'B4','3424','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14680',null,'B4','4564','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14681',null,'B4','4568','162',null,null,'NN',null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14682',null,'B4','3723','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14683',null,'B4','3726','162',null,null,'NN',null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14684',null,'B4','3733','162',null,null,'NN',null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14685',null,'B4','4289','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14686',null,'B4','4280','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14687',null,'B4','4314','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14688',null,'B4','4013','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');


Insert into IFF_OBSERVATION (ID,DATE_OBSERVATION,STATION,CD_NOM,ID_CAMPAGNE,ID_OBSERVATEUR,COMMENTAIRE,NICHEUR_SUR_SITE,NOUVEAU_TAXON,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON,PROTOCOLE,ZONE) values ('14689',null,'B4','4001','162',null,null,null,null,null,to_date('30/03/16 11:43','DD/MM/YY HH24:MI'),null,to_date('22/06/16 14:42','DD/MM/YY HH24:MI'),null,'12');

Thank you.
This post has been answered by Goran Stefanović on Jul 26 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 19 2016
Added on Jul 26 2016
8 comments
148 views