[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
>