search

VEuPathDB / EdaSubsettingService

A REST service to provide data and subsetting in the Exploratory Data Analysis Workspace
Apache License 2.0
0 stars 0 forks source link

Microbiome UD download: unable to run SQL #133

Open aurreco-uga opened 5 months ago

aurreco-uga commented 5 months ago

UD download: https://microbiomedb.org/mbio/app/workspace/analyses/EDAUD_4104431/LGyynNS/download

500 in microbiome, Unable to run SQL ORA-00904: "EUPATH_0009353_BACTERIA_BACTERIA_UNCLASSIFIED_ACTINOBACTERIA": invalid identifier

(this is not the full SQL, see the ....)

WITH MBIOTEMP_Source as ( SELECT Source_stable_id FROM apidbuserdatasets.ancestors_auto_20240202143046_HCD8_1_Source ), subset AS ( SELECT distinct MBIOTEMP_Source.Source_stable_id FROM MBIOTEMP_Source ), wide_tabular AS ( select rownum as r, wt. from ( select Source_stable_id, "EUPATH_0009353_Bacteria_Bacteria_unclassified_Actinobacteria", "EUPATH_0009353_Bacteria_Bacteria_unclassified_Aquificae", "EUPATH_0009353_Eukaryota_Metazoa_Arthropoda", "EUPATH_0009257_unclassified_ed_1_unclassified_1_unclassified_1_1_unclassified_1_1_unclassified_1_1_1_unclassified_1_1_1", ..... "MBIOTEMP_collection_date", "MBIOTEMP_collection_location", "MBIOTEMP_nucleotide_type", "MBIOTEMP_sample_type", "MBIOTEMP_water_control", ea.stable_id from apidbuserdatasets.attributes_auto_20240202143046_HCD8_1_Source ea, apidbuserdatasets.ancestors_auto_20240202143046_HCD8_1_Source a where ea.stable_id in (select from subset) and ea.stable_id = a.Source_stable_id order by EUPATH_0009353_Bacteria_Bacteria_unclassified_Actinobacteria ASC ) wt ) select Source_stable_id, "EUPATH_0009353_Bacteria_Bacteria_unclassified_Actinobacteria", "EUPATH_0009353_Bacteria_Bacteria_unclassified_Aquificae", "EUPATH_0009353_Eukaryota_Metazoa_Arthropoda", "EUPATH_0009353_Eukaryota_Fungi_Ascomycota", "EUPATH_0009353_Bacteria_Bacteria_unclassified_Bacteria_unclassified_1", "EUPATH_0009353_Bacteria_Bacteria_unclassified_Bacteroidetes", "EUPATH_0009353_Bacteria_Bacteria_unclassified_Candidatus_Saccharibacteria", ..... MBIOTEMP_water_control" from wide_tabular where r > 0 and r <= 10 order by EUPATH_0009353_Bacteria_Bacteria_unclassified_Actinobacteria ASC

dmgaldi commented 5 months ago

It looks like we're trying to select a column that doesn't exist. I wonder if the "Unclassified" in the column name is a hint?

SELECT EUPATH_0009353_BACTERIA_BACTERIA_UNCLASSIFIED_ACTINOBACTERIA FROM apidbuserdatasets.attributes_auto_20240202143046_HCD8_1_Source ea, apidbuserdatasets.ancestors_auto_20240202143046_HCD8_1_Source a
dmgaldi commented 5 months ago

I'm not sure what was wrong with my first query but this one does work which suggests the column is present in the attributes_auto_20240202143046_HCD8_1_Source table

SELECT "EUPATH_0009353_Bacteria_Bacteria_unclassified_Actinobacteria" FROM apidbuserdatasets.attributes_auto_20240202143046_HCD8_1_Source