...
For mammals, the following joins and logic are required
Code Block FROM bdbsa.suvisit sv JOIN bdbsa.supatch sp ON sv."PATCHID" = sp."PATCHID" join bdbsa.suspecies spp on sv."VISITNR" = spp."VISITNR" join bdbsa.vssp vs on vs."NSXCODE" = spp."NSXCODE" full join bdbsa.sutrap str on str."VISITNR" = sv."VISITNR" where (spp."SPECIESTYPE" = 'M' and "TEATS" is null and "VAGINA" is null and "POUCH" is null and "TESTES" is null and "WEIGHT" is null and "NUMPOUCH" is null and "LENGTH" is null)
Convert 'none detected' value in NUMOBSERVED to 0
The following variables are to be ingested
STRATANR, MACROHABNR, species_name, NUMOBSERVED, MICROHABNR, SPCOMM, SEX, ACTIVITY, PLANTEDRELEASED, NUMNIGHTS, NUMELLIOTTRAP, NUMELLIOTNIGHTS, NUMPITTRAP, NUMPITNIGHTS, NUMCAGETRAP, NUMCAGENIGHTS, ISHARPTRAP, ISMISTNET, ISHAIRTUBE, ISBATDETECTOR
Ignore me, working notes
occurrence and survey-based records. The following joins are needed for this data (where SITETYPE = ‘OP’ for opportunistic and ‘SU’ for survey-based methods). Only records with certainty confirmed will be exposed, see where statement below.
...