MySQL temptable view containing subquery

Question

I am trying to create a view with a with a subquery. According to the MySQL docs I cannot do this when my view uses the MERGE algorithm, but can do this if I use the temptable algorithm. Any pointers what I'm doing/reading wrong?

CREATE ALGORITHM =  temptable VIEW `vw_prod_placementinfo_destination` AS
select 
    d.branch,
    d.media_plan_name,  
    d.placement,
    case c.country WHEN null or 'n/a' then d.one else d.three end as Creative,
    case length(d.four)-length(replace(four, "x", '')) > 0 when true then d.four else Null end as AdSize
from (
    select 
        branch, 
        media_plan_name, 
        placement,
        split_str(placement, '_', 1) as One,
        split_str(placement, '_', 2) as Two,
        split_str(placement, '_', split_count(placement, '_')-1) as Three,
        split_str(placement, '_', split_count(placement, '_')) as Four
    from campaign_delivery_flat
    where media_plan_name like '%Destinatio%'
    group by branch, media_plan_name, placement ) d
left join country_code c on d.One = c.code

Answer 1

This is a bit long for a comment.

The documentation that you are referencing says nothing about subqueries in the from clause. The appropriate documentation is here. And, it is rather explicit and unambiguous:

Subqueries cannot be used in the FROM clause of a view.

(second paragraph).

If you need to set this up, then create one view for the subquery and one for the outer query.

Or, rewrite the logic so you don't need the subquery. You can use a correlated subquery, for instance, to get the country:

select 
    branch, 
    media_plan_name, 
    placement,
    (case when exists (select 1 from country_code c on d.One = c.code)
          then split_str(placement, '_', 1)
          else split_str(placement, '_', split_count(placement, '_')-1)
     end) as creative
     as One,
     (case length( split_str(placement, '_', split_count(placement, '_')) )-length(replace( split_str(placement, '_', split_count(placement, '_')) , "x", '')) > 0
           when true then split_str(placement, '_', split_count(placement, '_'))
           else Null
      end) as AdSize
from campaign_delivery_flat
where media_plan_name like '%Destinatio%'
group by branch, media_plan_name, placement