Error: operand should contain 1 column(s)

[raindrops87]

hi can anybody help me with this query?

 

select rawmatno, rawmatname, qty from compofrawmat join rawmaterials on compofrawmat.rawmatno = rawmaterials.rawmatid where ProductId in(select ProductId, sum(Quantity)

from (customerorderform join cofdetails on customerorderform.OrderNo = cofdetails.OrderNo) natural join finishedproduct where customerorderform.DatePickUp >= adddate(curdate(), interval 3 month) and customerorderform.DatePickUp <= adddate(curdate(), interval 6 month) group by ProductName)

 

when i use this i get an error that says operand should contain 1 column(s) but if i remove sum(Quantity) it works but i need to add the quantity of the finishedproducts by id to multiply it to the qty of its raw materials.

 

any suggestions?

[requinix]

If you need both pieces of data then an IN is not what you should be using. Try a JOIN instead.

[raindrops87]

what i need is to get the quantity of raw materials that comprises the finished products for the next 4-6 months that are found in the customer order form.

 

do i need a join or in?

 

[requinix]

do i need a join or in?

Yes.

 

With your current query an IN will not work. So you have [more than these] two options: try a JOIN instead, or keep your IN but change the query to be able to use it.

[raindrops87]

but if i use join another error comes up, column productid in field list is ambiguous

[awjudd]

Then fully qualify the field name (table.field) in your returned table?

 

~judda