Re: need help with user variables in where clause of sub query - Dan Rossi - com.mysql.lists.mysql

10 messages in com.mysql.lists.mysqlRe: need help with user variables in ...

From	Sent On	Attachments
Dan Rossi	27 Dec 2005 20:39
SGr...@unimin.com	27 Dec 2005 21:46
Dan Rossi	27 Dec 2005 22:10
SGr...@unimin.com	28 Dec 2005 07:48
Dan Rossi	28 Dec 2005 20:29
Dan Rossi	28 Dec 2005 20:31
Dan Rossi	28 Dec 2005 20:55
SGr...@unimin.com	28 Dec 2005 22:18
Dan Rossi	29 Dec 2005 04:18
SGr...@unimin.com	29 Dec 2005 07:59

Subject:	Re: need help with user variables in where clause of sub query
From:	Dan Rossi (mys...@electroteque.org)
Date:	12/28/2005 08:55:13 PM
List:	com.mysql.lists.mysql

I just tried to create a Function or Stored Procedure instead of making variables but it didnt even let me do this

CREATE FUNCTION test (customerID, month, producerID) RETURN SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE fu.customerID=customerID AND fu.month=month AND fu.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (producerID))

something like that, so if functions will work instead of variables for sub queries and views, im still needing to send the primary key of the current row to them somehow.

Btwi dont want the column of a view to be a variable, i think thats what it thinks ! Im just needing to send the value of the current primary key field top a sub query !

Read my latest post if i can get around not using variables, and still manage to get the right values of a current row going to a sub query ?

On 29/12/2005, at 2:48 AM, SGr...@unimin.com wrote:

You seem to be coming at SQL with a COBOL perspective. Views are something you typically create just once and they stay updated automatically. They work like tables not like queries. Assigning variables to each column of a view doesn't make any sense (in the SQL sense of "view") as each column could potentially contain several million values, depending on how many rows you identify for your VIEW in your definition statement.

You demonstrated your situation with a conditional branch on a value to produce one of two different . What I think you wanted to do was to UNION two queries together so that you could combine the history of a customer's account with the current month's activity into a single set of results.

Your queries are also insensitive to year changes. What happens when you are in Jan 2006 and you need to review the previous month's data?

Since you are using views I know you are on v5.x or higher. However, I think you have become so entangled with your subqueries that you have managed to confuse yourself. Using a subquery to calculate every value of each row is VERY inefficient and unless you are actually trying to prove just how bad it is, I strongly suggest you modify your approach to become more linear, perhaps completely linear.

Here is an example of how to combine two queries into a single result:

( SELECT fu.customer_id , fu.month , sum(fu.usage) total_bandwidth , sum(if(p.producerID is NULL, 0, fu.usage)) feed_bandwidth , sum(fu.usage)/sum(if(p.producerID is NULL, 0, fu.usage)) percentage , count(distinct f.feedID) count , count(distinct if(p.producerID is null, null, f.feedid) feed_count FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID INNER JOIN feeds f ON fu.feedID=f.feedID LEFT JOIN producers_join p ON f.feedID = p.feedID AND p.producerID IN (3) GROUP BY fu.month,fu.customerID ) UNION ( SELECT customerid , month , total_bandwidth , feed_bandwidth , percentage , count , feed_count FROM month_totals ) ORDER BY fu.month DESC;

See how I disentangled so many of your subqueries? If you are having speed problems, we can work on that after we get the query working, OK? ALSO(!) you don't refer to a value in the outer query by its variable name (because you normally don't use variables in subqueries), you normally use the actual column name in the subquery.

Shawn Green Database Administrator Unimin Corporation - Spruce Pine

Dan Rossi <mys...@electroteque.org> wrote on 12/28/2005 01:10:32 AM:

I have an unfinished query, i am trying to test, basically im required to get the value of the current field in a row and use it for a subquery in that row :| Its not a working query, and im not asking for someone to fix it, however as u can see i need to send the customerID and month to the sub query. What its actually trying to do is tedious to explain, but i have two tables of media usage for a customer, the current month will be in the usage table, so that if the plan changes in that month so does the totals, but for the previous months there is a static month_totals table showing just the totals recorded. So im needing to select two different tables depending on what month is being selected. I hope this helps.

SELECT SQL_CACHE

CASE WHEN MONTH(mt.month) = MONTH(NOW())

THEN

CREATE VIEW current_month AS

SELECT

@customerID:=c.customerID, @month:=fu.month AS month, DATE_FORMAT(fu.month,'%M') AS month_long,

@total_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE fu.customerID=@customerID AND fu.month=@month),

@feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE fu.customerID=@customerID AND fu.month=@month AND fu.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))),

@percentage:=((@feed_bandwidth/@total_bandwidth)) AS percentage,

@month_totals:=(SELECT month_total FROM month_totals WHERE customerID=@customerID AND DATE_FORMAT(month,"%m")=@month GROUP BY month, customerID) AS month_totals,

@count:=(SELECT count(*) FROM feed_usage WHERE customerID=@customerID AND month=@month),

@feed_count:=(SELECT count(*) FROM feed_usage WHERE customerID=@customerID AND month=@month AND feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))),

ROUND(( IF(c.bandwidth_limit=0, ((c.monthly_price/@count)*f.percentage_paid)*@feed_count, ((@month_totals*@percentage)*f.percentage_paid) )),0) AS providers_cut

FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID INNER JOIN feeds f ON fu.feedID=f.feedID WHERE f.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3)) GROUP BY fu.month,fu.customerID ORDER BY fu.month DESC

ELSE

CREATE VIEW previous_months AS

SELECT

@customerID:=c.customerID, @month:=MONTH(mt.month) AS month, DATE_FORMAT(mt.month,'%M') AS month_long, @feeds:=feeds,

@total_bandwidth:=( SELECT bandwidth FROM month_totals WHERE customerID=@customerID AND MONTH(month)=@month),

@feed_bandwidth:=( SELECT bandwidth FROM month_totals INNER JOIN WHERE customerID=@customerID AND fu.month=@month AND fu.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))),

@percentage:=((@feed_bandwidth/@total_bandwidth)) AS percentage,

@month_totals:=(SELECT month_total FROM month_totals WHERE customerID=@customerID AND DATE_FORMAT(month,"%m")=@month GROUP BY month, customerID) AS month_totals,

@count:=(SELECT count(*) FROM feeds WHERE feedID IN (@feeds)),

@feed_count:=(SELECT count(*) FROM month_totals WHERE customerID=@customerID AND month=@month AND feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))),

ROUND(( IF(c.bandwidth_limit=0, ((c.monthly_price/@count)*f.percentage_paid)*@feed_count, ((@month_totals*@percentage)*f.percentage_paid) )),0) AS providers_cut

END

On 28/12/2005, at 4:47 PM, SGr...@unimin.com wrote:

Dan Rossi <mys...@electroteque.org> wrote on 12/27/2005 11:39:57 PM:

Hi there i am trying to use usewr variables in a select statement to add to a where clause in a sub query. Ie

select @id:=id,@month:=month, (select SUM(totals) from table where id=@id and month=@month) as totals from table

its happened on other occasions ie with calculations and sums, whats happened in mysql5 ? It used to work in mysql4 , something i am doing is wrong ? Please let me know thanks.

Is there a great reason why you are using a subquery? I could rewrite this to avoid the subquery and probably eliminate your particular problem:

SELECT id, month, sum(totals) totals FROM TABLE group by id, month;

Unless(!) you oversimplified your original example. In which case, you should post your actual query and I can give you a better response.

Technically, the values of the variables should not be determined until AFTER the row is processed which means that you shouldn't be able to use them for your subquery (at least that's how I remember the SQL:2003 spec but it's late and I could very well be wrong in my recollection)

Personally, I am not that big a fan of subqueries anyway. There are a few types of queries where they make the SQL to achieve a result rather compact and elegant. However, I have never seen a subquery actually outperform a properly constructed linear query. They sometimes match linear performance but most often perform worse to much worse.