[select] genus= SELECT DISTINCT genus family= SELECT DISTINCT family commonName = SELECT DISTINCT vernacularName collection = SELECT DISTINCT collection_code institution = SELECT DISTINCT institution_code country = SELECT DISTINCT countryName collector= SELECT DISTINCT biorecord.collector higherTaxon = SELECT DISTINCT higherTaxonName recordBasis = SELECT COUNT(distinct bioRecordKey),recordBasis ##taxon= SELECT DISTINCT taxon.taxonName,taxon.taxonKey,taxon.unitCount taxon= SELECT taxon.taxonName,taxon.taxonKey,taxon.unitCount bioRecordKey = SELECT DISTINCT bioRecordKey countRecordBasis = SELECT COUNT(distinct bioRecordKey),recordBasis full = SELECT taxonName,genus,family,'','',countryName,CAST(localityText AS TEXT),collection_code,institution_code,gatheringYear,CAST(dbo.fn_getURLs(bioRecordKey) AS TEXT),bioRecordId,bioRecordResourceKey,bioRecordKey,biorecord.geoLongitude,biorecord.geoLatitude,taxonAuthor,recordBasis,biorecord.collector,biorecord.collectorNumber,fieldNumber,resourceName FROM bioRecord INNER join taxon ON taxon.taxonKey = bioRecordTaxonKey LEFT join isoCountry on isoCountryCode = bioRecordCountryCode LEFT JOIN resource on resourceKey = bioRecordResourceKey nbMulti = SELECT COUNT(DISTINCT imagerecordkey) FROM imageRecord specimenOfTheDay = SELECT TOP 1 taxonName,bioRecordId,collection_code,institution_code,bioRecordResourceKey,which_day,bioRecordKey FROM specimenOfTheDay s JOIN imageRecord i ON i.imagerecordkey = s.imagerecordkey JOIN bioRecord ON i.imagerecordbiorecordkey = bioRecordKey JOIN taxon ON bioRecordTaxonKey = taxonKey ORDER BY which_day DESC countbioRecordKey = SELECT COUNT(distinct bioRecordKey) unitsDetails = SELECT bioRecordId,institution_code,collection_code,bioRecordResourceKey FROM bioRecord unitsName = SELECT DISTINCT taxon.taxonName countUnits = SELECT ROWS FROM sysindexes WHERE id = OBJECT_ID('bioRecord_base') AND indid < 2 [from] genus= FROM taxon family= FROM taxon commonName= FROM vernacularName, taxon collection= FROM bioRecord institution= FROM bioRecord collector = FROM bioRecord country = FROM bioRecord higherTaxon = FROM higherTaxon recordBasis = FROM bioRecord taxon = FROM taxon bioRecord = FROM bioRecord countRecordBasis = FROM bioRecord imageRecord = FROM imageRecord genusbioRecord= FROM bioRecord,taxon familybioRecord= FROM bioRecord,taxon commonNamebioRecord= FROM bioRecord,vernacularName collectionbioRecord= FROM bioRecord institutionbioRecord= FROM bioRecord countrybioRecord = FROM bioRecord higherTaxonbioRecord = FROM bioRecord,higherTaxon recordBasisbioRecord = FROM bioRecord taxonbioRecord = FROM bioRecord,taxon bioRecordbioRecord = FROM bioRecord countRecordBasisbioRecord = FROM bioRecord countrybioRecord = FROM bioRecord,isocountry taxonHigher=FROM taxon extension= FROM bioRecord_ext [where] genus = WHERE taxonKey family = WHERE taxonKey genusbioRecord = WHERE bioRecordTaxonKey familybioRecord = WHERE bioRecordTaxonKey commonName = WHERE vernacularTaxonKey = taxonKey commonNamebioRecord= WHERE bioRecordTaxonKey collection = WHERE bioRecordTaxonKey institution = WHERE bioRecordTaxonKey country = WHERE bioRecordTaxonKey collector = WHERE bioRecordTaxonKey higherTaxon = WHERE higherTaxon.taxonKey higherTaxonbioRecord = WHERE bioRecordTaxonKey recordBasis = WHERE bioRecordTaxonKey taxon = WHERE taxonName bioRecordKey = WHERE bioRecordKey bioRecordTaxonKey = WHERE bioRecordTaxonKey countRecordBasis = WHERE bioRecordTaxonKey imagerecordbiorecordkey = WHERE imagerecordbiorecordkey collNumber = WHERE biorecord.collectorNumber fieldNumber = WHERE fieldNumber taxonHigher=WHERE taxon.taxonName [and] taxonKeys = AND bioRecordTaxonKey taxonKeyjoinBR = AND taxon.taxonKey = bioRecordTaxonKey genusbioRecord = AND taxon.taxonKey = bioRecordTaxonKey familybioRecord = AND taxon.taxonKey = bioRecordTaxonKey taxonKeyjoinHigher = AND higherTaxon.taxonKey = bioRecordTaxonKey commonNamebioRecord = AND vernacularTaxonKey = bioRecordTaxonKey commonNametaxon= AND vernacularTaxonKey = taxon.taxonKey institution= AND institution_code collection = AND collection_code locality = AND localityText localityFT = CONTAINS(biorecord_ext.localityText,'%s') country = AND countryName dates = AND gatheringYear BETWEEN %i AND %i dateInf = AND biorecord.gatheringDate < dateEq = AND biorecord.gatheringDate = dateSup = AND biorecord.gatheringDate > coordinates = AND biorecord.geoLatitude < %s and biorecord.geoLatitude > %s AND biorecord.geoLongitude < %s AND biorecord.geoLongitude > %s recordBasis = AND recordBasis bioRecordKey= AND bioRecordKey collector = AND biorecord.collector collectorFT = CONTAINS(biorecord_ext.collector,'%s') family = AND family genus = AND genus taxonName = AND taxonName multimedia = AND bioRecordKey = imagerecordbiorecordkey higherTaxon = AND higherTaxon.higherTaxonName commonName = AND vernacularName taxonKey = AND taxonKey collNumber = AND biorecord.collectorNumber fieldNumber = AND fieldNumber bioRecordId = AND bioRecordId taxonHigherGenus= AND taxon.genus taxonHigherFamily= AND taxon.family bioRecord_ext= AND biorecordkey = brkey [or] taxonName = OR taxonName taxonHigherGenus= OR taxon.genus taxonHigherFamily= OR taxon.family [andGroup] institution= AND (institution_code collection = AND (collection_code locality = AND localityText localityFT = CONTAINS(biorecord_ext.localityText,'%s') country = AND (countryName dateInf = AND (biorecord.gatheringDate < dateEq = AND (biorecord.gatheringDate = dateSup = AND (biorecord.gatheringDate > bioRecordKey= AND (bioRecordKey collector = AND (biorecord.collector collectorFT = CONTAINS(biorecord_ext.collector,'%s') family = AND (family genus = AND (genus taxonName = AND (taxonName higherTaxon = AND (higherTaxon.higherTaxonName commonName = AND (vernacularName recordBasis = AND (recordBasis collNumber = AND (biorecord.collectorNumber fieldNumber = AND (fieldNumber bioRecordId = AND (bioRecordId [leftJoin] higherTaxontaxon = LEFT JOIN higherTaxon ON higherTaxon.taxonKey = taxon.taxonKey higherTaxonbioRecord = LEFT JOIN higherTaxon ON higherTaxon.taxonKey = bioRecordTaxonKey country = LEFT JOIN isoCountry ON isoCountryCode = bioRecordCountryCode commonNamebioRecord = LEFT JOIN vernacularName on vernacularTaxonKey = bioRecordTaxonKey commonNametaxon = LEFT JOIN vernacularName on vernacularTaxonKey = taxon.taxonKey [joinOn] taxonKeyjoinBR = taxon.taxonKey = bioRecordTaxonKey taxonKeyjoinHigher = higherTaxon.taxonKey = bioRecordTaxonKey commonNamebioRecord = vernacularTaxonKey = bioRecordTaxonKey commonNametaxon= vernacularTaxonKey = taxon.taxonKey [list] country = SELECT countryName FROM isoCountry institution = SELECT * from instCodes collection =SELECT * from collCodes family = SELECT family FROM taxon [groupBy] countRecordBasis = GROUP BY recordBasis recordBasis = GROUP BY recordBasis bioRecord = GROUP BY bioRecordKey [orNull] recordBasis = OR recordBasis is null locality = OR localityText is null country = OR (countryName is null or bioRecordCountryCode is null) collector = OR biorecord.collector is null family = OR family is null genus = OR genus is null taxonName = OR taxonName is null higherTaxon = OR higherTaxon.higherTaxonName is null commonName = OR vernacularName is null coordinates = OR (biorecord.geoLatitude is null and biorecord.geoLongitude is null) [andNull] recordBasis = AND recordBasis is null locality = AND localityText is null country = AND (countryName is null or bioRecordCountryCode is null) collector = AND biorecord.collector is null family = AND family is null genus = AND genus is null taxonName = AND taxonName is null higherTaxon = AND higherTaxon.higherTaxonName is null commonName = AND vernacularName is null coordinates = AND biorecord.geoLatitude is null and biorecord.geoLongitude is null [mysql] not = not and = and or = or isnull = is null isnotnull = is not null equals = = notequals = <> like = LIKE lessthan = < lessthanorequals = <= greaterthan = > greaterthanorequals = >= in = IN [escape] '= ' * = %