[AccessD] OT: Creating a MySQL View

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