Paul wrote:
> On Mar 13, 6:03 am, "Captain Paralytic" <paul_laut...@yahoo.com>
> wrote:
>> On 13 Mar, 00:31, "Paul" <paul_l...@hotmail.com> wrote:
>>
>>
>>
>>> Hi,
>>> This works great - thank you - but only if I run it manually (in a
>>> query window).
>>> If I try to save it as a View, it fails, saying
>>> "View's SELECT contains a subquery in the FROM clause".
>>> Apparently, according tohttp://dev.mysql.com/doc/refman/5.1/en/create-view.html
>>> this is not allowed.
>>> Therefore, do you know how to either:
>>> 1. Do the same thing without using a subquery
>>> OR
>>> 2. Store it inside my MySQL DB as something other than a View? I'm new
>>> to this, but as far as I understand, Procedures don't return values
>>> and UDFs return singular values and not record sets. So how can I
>>> store it so I don't need to send this each time manually from my
>>> application?
>>> Thank you...
>>> On Mar 12, 3:11 pm, "strawberry" <zac.ca...@gmail.com> wrote:
>>>> On 12 Mar, 16:24, "Paul" <paul_l...@hotmail.com> wrote:
>>>>> anyone?..
>>>> Erm...
>>>> SELECT UserID,
>>>> IFNULL(Stage1, 0) AS Stage1, IFNULL(Stage2, 0) AS Stage2,
>>>> IFNULL(Stage3, 0) AS Stage3, IFNULL(Stage4, 0) AS Stage4
>>>> FROM Users LEFT OUTER JOIN
>>>> (SELECT
>>>> Actions.PerformerID,
>>>> COUNT(Actions.ActionID) AS ActionsTotal,
>>>> COUNT((CASE WHEN (ActionStatus.ActionStatusName = 'Stage1') THEN
>>>> Actions.ActionID END)) AS Stage1,
>>>> COUNT((CASE WHEN (ActionStatus.ActionStatusName = 'Stage2') THEN
>>>> Actions.ActionID END)) AS Stage2,
>>>> COUNT((CASE WHEN (ActionStatus.ActionStatusName = 'Stage3') THEN
>>>> Actions.ActionID END)) AS Stage3,
>>>> COUNT((CASE WHEN (ActionStatus.ActionStatusName = 'Stage4') THEN
>>>> Actions.ActionID END)) AS Stage4
>>>> FROM Actions, ActionStatus
>>>> WHERE
>>>> ActionStatus.ActionStatusID = Actions.Status
>>>> GROUP BY Actions.PerformerID)Action_Stats
>>>> ON Users.UserID = Action_Stats.PerformerID;- Hide quoted text -
>>> - Show quoted text -
>> If it's coming from an application, what's the problem with sending it
>> each time?
>
>
> The reason is, I need to select different subsets of that View in
> multiple places in my code. It is much nicer to have a short select
> statement than having a 10-line-long one. And I presume it's faster
> too, not to have to send all this text each time over JDBC.
> Is there a way?
>

(Top posting fixed)

The difference between sending 50 bytes and 500 bytes across the
connection can be measured in microseconds. If you have performance
problems, there are a lot of other places you should look first.

P.S. Please don't top post. Thanks.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================