Ok i have simplified my query into sections i have discovered that
mysql5 doesnt like user variables in a select statement when creating a
view, i am required to setup user variables so i can send the current
row value primary key to a sub query, i really wished i could just send
the field to the sub query !
CREATE VIEW current_month AS
SELECT @customerID:=c.customerID, @month:=fu.month AS month,
DATE_FORMAT(fu.month,'%M') AS month_long,
@total_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE
[EMAIL PROTECTED] AND [EMAIL PROTECTED]),
@feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE
[EMAIL PROTECTED] AND [EMAIL PROTECTED] AND fu.feedID IN (SELECT
feedID FROM producers_join WHERE producerID IN (3))),
@percentage:=((@feed_bandwidth/@total_bandwidth)) AS percentage,
@month_totals:=(SELECT month_total FROM month_totals WHERE
[EMAIL PROTECTED] AND DATE_FORMAT(month,"%m")[EMAIL PROTECTED] GROUP BY
month, customerID) AS month_totals,
@count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED]
AND [EMAIL PROTECTED]),
@feed_count:=(SELECT count(*) FROM feed_usage WHERE
[EMAIL PROTECTED] AND [EMAIL PROTECTED] AND feedID IN (SELECT feedID
FROM producers_join WHERE producerID IN (3))),
ROUND(( IF(c.bandwidth_limit=0,
((c.monthly_price/@count)*f.percentage_paid)[EMAIL PROTECTED],
((@[EMAIL PROTECTED])*f.percentage_paid) )),0) AS providers_cut
FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID
INNER JOIN feeds f ON fu.feedID=f.feedID WHERE f.feedID IN (SELECT
feedID FROM producers_join WHERE producerID IN (3)) GROUP BY
fu.month,fu.customerID ORDER BY fu.month DESC
gives me
View's SELECT contains a variable or paremeter , any ideas ?
What im having to do , is create a view for a current months summary
which is a select statement from one table , and then a second view of
the previous months summary which is a select statement from another
table which stores the totals statically. I have no idea what to do now
:\
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
