Darren D
darren at activebilling.com.au
Wed Mar 4 17:48:22 CST 2009
Hi David I'm not sure about most of the q's you ask I did mention the tildes to him - He says it's a MySQL thing - I Dunno Overall he says the query runs - This doesn't seem to be the issue It's when the query syntax is passed to the "Create View" function this issue of the sub query comes up Darren -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee Sent: Thursday, 05 March 2009 10:16 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] OT: Creating a MySQL View I've never used MySQL, only MS SQL, but nothing sticks out at me. Does MySQL support sub queries in Views? If he comments out the Subquery it will create the view? If so, can he copy and paste the inner/sub query and run that sucessfully? What if you remove the Sum & Group By (making the sub query simpler). Is there any reason the `start_date` has tildes around it? Does MySQL require the Join to be like this: ON du.account_no = Tot.account_no David On Wed, Mar 4, 2009 at 2:55 PM, Darren D <darren at activebilling.com.au>wrote: > Hi All > > > > A colleague is trying to create a 'view' in MySQL but is having issues > because > of the 'embedded' sub query in one of the joins > > He asked if 'my list' might be able to look at it (I brag about this list > all > the time :-)) > > I realise this is OT so if any MySQL gurus want to have a look at this - > Please > send any replies straight back to me if that's OK - thanks > > > > Here 'tis - Thanks in advance team > > > > Darren > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > CREATE VIEW v_weighting AS > > SELECT du.account_no, du.exchange_id, ex.name AS Exchange, > SUM(du.num_completed_calls) AS Exchange_Calls, Tot.Account_calls, > > d.measure_ref, d.value, d.comparative_value AS Exchange_Percent, da.value > AS > Aus_Value, da.comparative_value AS Aus_Percent, > > (d.comparative_value - da.comparative_value)/ da.comparative_value AS > Exchange_Trend, SUM(du.num_completed_calls)/Tot.Account_Calls AS > Exchange_Call_Weighting, > > ((d.comparative_value - da.comparative_value)/ da.comparative_value) * > SUM(du.num_completed_calls)/Tot.Account_Calls AS Weighted_Exchange_Trend > > FROM daily_usage_exchange du > > JOIN exchanges ex > > ON du.exchange_id = ex.id > > JOIN demographics d > > ON ex.name = d.exchange_name > > JOIN demographics_views dv > > ON ( d.measure_ref = dv.measure_ref ) > > JOIN v_Aus_demographics da > > ON d.measure_ref = da.measure_ref > > JOIN (SELECT account_no, sum(u.num_completed_calls) as Account_Calls > > FROM daily_usage_exchange u > > WHERE u.`start_date` >= ADDDATE(CURDATE(), -30) > > GROUP BY account_no) Tot > > ON Tot.account_no = du.account_no > > WHERE ( du.`start_date` >= ADDDATE(CURDATE(), -30) > > AND dv.data_type = 'Population' > > AND dv. demographic_view = 'DETAIL' > > ) > > GROUP BY account_no, exchange_id, d.measure_ref > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com