Here is an error in this query, how to fix it?

Answer 1, Authority 100%

The whole problem is described in the title of the question. One of your subqueries

(Select Points
  From oc_product_reward pr.
  Where pr.product_id = p.Product_ID
   And customer_group_id = '1') AS REWARD,
 SELECT SS.NAME.
  From oc_stock_status ss.
  Where ss.stock_status_id = p.stock_status_id
   And ss.language_id = '1') as Stock_status,
 (Select wcd.unit.
  From oc_weight_class_description WCD.
  Where P.Weight_Class_id = wcd.weight_class_id
   And wcd.language_id = '1') as weight_class,
 (Select lcd.unit.
  From OC_LENGTH_CLASS_DESRIPTION LCD.
  Where P.Length_Class_ID = lcd.length_class_id
   And lcd.language_id = '1') AS LENGTH_CLASS,

returned more than one value (which is unacceptable). Which one, no one will tell you, because, without seeing the data itself, it is impossible to do. The simplest solution is to add to them Limit 1 , but how correctly it will be correct, solve only you.