qiime feature-table filter-samples error SQL format or no ID present --p-where


I am attempting to use the feature-table filter samples to extract the ectomycorrhizal fungi from my table, and I keep having issues. I have done this before, so I am unsure why it is not working this time.

I have used the script below.

qiime feature-table filter-samples \
--i-table core-metrics-results/rarefied_table.qza \
--m-metadata-file FUNGuild/Fungal-T1-T17-table-with-taxonomy.guilds_matched.tsv \
--p-where "[Guild]='Ectomycorrhizal' AND [Confidence Ranking]='Highly Probable'" \
--o-filtered-table EcM/DADA2/Rarefied/EcM-Table-T1-T17-Merged-HP-Rare.qza

and this will output a table, supposedly, but when I try to create the .qzv file, I get the following error.

qiime feature-table summarize \
--i-table EcM/DADA2/Rarefied/EcM-Table-T1-T17-Merged-HP-Rare.qza \
--m-sample-metadata-file Metadata/Fungi-MetaRare.tsv \
--o-visualization EcM/DADA2/Rarefied/EcM-Table-T1-T17-Merged-HP-Rare.qzv

ValueError: ids_to_keep must contain at least one ID.

I have also ran the code using

qiime feature-table filter-samples \
--i-table core-metrics-results/rarefied_table.qza \
--m-metadata-file FUNGuild/Fungal-T1-T17-table-with-taxonomy.guilds_matched.tsv \
--p-where "Guild='Ectomycorrhizal' AND Confidence Ranking='Highly Probable'" \
--o-filtered-table EcM/DADA2/Rarefied/EcM-Table-T1-T17-Merged-HP-Rare.qza

but I get this error

Plugin error from feature-table:

  • Selection of IDs failed with query:
  • SELECT "#OTUID" FROM metadata WHERE [Guild]='Ectomycorrhizal'' AND [Confidence Ranking]='Highly Probable' GROUP BY "#OTUID" ORDER BY "#OTUID";**
  • If one of the metadata column names specified in the where statement is on this list of reserved keywords (SQLite Keywords), please ensure it is quoted appropriately in the where statement.**

The only thing I can think of is that my metadata has #OTU as header, and my feature table has #SampleID, but even when I change the metadata name, it still does not work.

If you could please help me with this issue, I would really appreciate it.


Hi @fabipc!
It appears that the --p-where function entered into qiime feature-table is filtering away all of your samples, hence the ValueError.
Based on your parameter "Ectomycorrhizal" I am guessing your intention is to filter based on the feature "Ectomycorrhizal", in which case I recommend using qiime feature-table filter-features, documentation can be found here.
If this is not your intention please explain the goal for your metadata analysis so I can help you find the best fit.
Also, The SQL formatting used here is correct:

you will want to use the brackets when you try again.

I see.
Yes, I want to keep the fungi classified as Ectomycorrhizal with a highly probable confidence raking.

I went ahead and tried what you suggested, and it worked :slight_smile:

Thanks a ton. I will go ahead and share this information with my lab, as they are also going to be doing the same analysis.


This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.