-1

I'm receieving the following error:

[Err] 1349 - View's SELECT contains a subquery in the FROM clause

When creating this view:

  create  view v_rescume_info   as 
   select((case when goods.goods_fee is null then 0 else goods.goods_fee end)
   +(case when rent.rent_fee is null then 0 else  rent.rent_fee end)
   +(case when baoxiao.baoxiao_fee is null then 0 else baoxiao.baoxiao_fee end))
    as rescume_fee,goods.member_id   from (select  sum(product_price * product_quantity) as goods_fee,member_id from product_general_info group by member_id) goods 
    left join (select  sum(house_fee + (case when water_fee is null then 0 else water_fee  end)*water_price 
   +(case when electric_fee is null then 0 else electric_fee  end)*electric_price) rent_fee,member_id from  member_rent_info group by member_id) rent 
    on  goods.member_id  = rent.member_id
   left join (select  sum(finishedoff_price) as baoxiao_fee ,member_id from  member_finishedoff_info group by member_id) baoxiao
Josh Darnell
  • 11,304
  • 9
  • 38
  • 66
Yang Qiaozhao
  • 1
  • possible duplicate of [ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause](http://stackoverflow.com/questions/5416809/error-1349-hy000-views-select-contains-a-subquery-in-the-from-clause) .You would need to rewrite your query without using subquery. – ajreal Sep 07 '11 at 15:47
  • possible duplicate of [MySQL: View with Subquery in the FROM Clause Limitation](http://stackoverflow.com/questions/206062/mysql-view-with-subquery-in-the-from-clause-limitation) – Matt Fenwick Feb 17 '12 at 17:40

1 Answers1

0

Well that's a mess. In order to help you, you can replace those "cases when... then" with coalesce

 create  view v_rescume_info as 
 select(
 Coalesce(goods.goods_fee,0) + Coalesce(rent.rent_fee, 0) + 
 Coalesce(baoxiao.baoxiao_fee,0) as rescume_fee,
  goods.member_id  from v_newView1

Why I put the v_newView here? Well.. you are really trying to select from another select. You can cross tables but it never worked for me this way. This looks like Transact-SQL rather than MySQL sintax.

Before you create views, please test those views outside. You only should put "Create View foo as " once you got the results working.

For what I can see, you need to create another view, "my" v_newView1 in order to get the results from a sub-query. I recommend you to use the join functions (left, right, inner) to achieve what you intend.

Without the database it's hard to figure where is your problem. Also use multiple lines while debugging. It really helps to see where witch line is producing your error.

Mário Rodrigues
  • 833
  • 7
  • 21