[AccessD] OT: Creating a MySQL View

Does he get an error, or does it just seem to run forever?

Have him try this: create a view from the inner query.

Select from this new view in the outer query. Does that work?


On Wed, Mar 4, 2009 at 3:48 PM, Darren D <darren at activebilling.com.au>wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>