How to use search results in splunk dbxquery

[Nikitha]

I have obtained the below results from my search.

I want to write a dbxquery in splunk db connect which can use this data as input. For each category there should a different query. The resultant query should be :

1. When Category is "Fruits"
| dbxquery connection="connect" query="select * from abc.def where category="Fruits" and (items="Apple" or items="Orange" or items="Pear")" 

2. When Category is "Colours"

| dbxquery connection="connect" query="select * from abc.def where category="Colours" and (items="Red" or items="Black" or items="Maroon")" 

Is there a way I can write this query ?

[thambisetty]

did you check dbxlookup?

I don't think you can construct the query as you shared but you can still make use of that to get your results. 

you want to do lookup using multi values fields using or condition, that's not possible. But, you can perform "AND" not "OR" condtion.

| your query // don't apply stats to get multi value field.
| dbxlookup connection="connection_name" query="SELECT * FROM \"schemaname\".\"tablename\"" "Category" AS "Category", "Items" AS "Iteams" OUTPUT "specify fields you want to get"

https://docs.splunk.com/Documentation/DBX/3.4.0/DeployDBX/Createandmanagedatabaselookups#Create_a_da...