Query throwing operator and should contain 1 column(s)

Question

Im working on this query and wanted to bring the metric to a date level, but when I ended up adding the filed to the metric subquery it threw me this error. I looked around a bit to see what can be done but still not sure how to resolve it :/

  SELECT MAX(end_d)offer_end_date,
  IF(o.end_d>=[?FD58AFE1416C9D8D7313EA9425FA7CF6[?,1,0) active_of,
  offer_budget,
      (SELECT clip_date, IFNULL(SUM(current_clip_count),0) 
       FROM data_mining.clip_summary stb 
       WHERE stb.offer_id=o.oid AND clip_date>=[..] 
           AND clip_date>=SUBDATE(o.st_d, INTERVAL 1 DAY)
           AND clip_date<=ADDDATE(o.end_d, INTERVAL 1 DAY) 
           AND clip_date<=[..]
           AND is_handraiser<>1 group by clip_date
    ) AS clips_cur
FROM rp.mstr_all_offer_sum o 
JOIN data_mining.clip_summary s ON s.offer_id=o.oid
JOIN mstr_all_clip_red_sum d ON d.offer_id = o.oid
WHERE target_type<>'targeted' 
      AND st_d<=ADDDATE([...]?  ,INTERVAL 1 DAY) 
      AND end_d>=SUBDATE([...]?, INTERVAL 1 DAY)  
       AND clip_date BETWEEN[...]? AND [...]?

I tried adding 'clip_date' to the query but no luck. Any idea how can rework this?

Answer 1

It's this segment beginning on the fourth line:

 (SELECT clip_date, IFNULL(SUM(current_clip_count),0) 

This is in the SELECT clause, and in the SELECT clause nested subquery can only return one column. However, the results from thsi subquery have two columns.

To fix this, you'll either need to run the subquery twice — once for each field — or find a way to work the source data into the JOINs. The other way is rewrite this using LATERAL JOIN for the subquery.

SELECT MAX(end_d)offer_end_date,
   IF(o.end_d>=[?FD58AFE1416C9D8D7313EA9425FA7CF6[?,1,0) active_of,
   offer_budget, lj.clip_date, lj.clips_cur
FROM rp.mstr_all_offer_sum o 
JOIN data_mining.clip_summary s ON s.offer_id=o.oid
JOIN mstr_all_clip_red_sum d ON d.offer_id = o.oid
CROSS JOIN LATERAL (
       SELECT clip_date, IFNULL(SUM(current_clip_count),0) as clips_cur
       FROM data_mining.clip_summary stb 
       WHERE stb.offer_id=o.oid AND clip_date>=[..] 
           AND clip_date>=SUBDATE(o.st_d, INTERVAL 1 DAY)
           AND clip_date<=ADDDATE(o.end_d, INTERVAL 1 DAY) 
           AND clip_date<=[..]
           AND is_handraiser<>1 
       GROUP BY clip_date
) lj
WHERE target_type<>'targeted' 
      AND st_d<=ADDDATE([...]?  ,INTERVAL 1 DAY) 
      AND end_d>=SUBDATE([...]?, INTERVAL 1 DAY)  
       AND clip_date BETWEEN[...]? AND [...]?

I suspect, though, the clips_cur field could be handled better using a windowing function.