User variables in update statement
Is it possible to use user variables in an update statement, I can find only scant docs on it, but they do not pertain to what I am trying to do. Given this select: SELECT user_id, order_status, quantity_chosen, month_price, each_price, sales_tax, sales_tax_rate, @NEW_each_price:=(each_price + .06) as NEW_each_price, @NEW_month_price:=(quantity_chosen * @NEW_each_price) as NEW_month_price, @postage_rate:=(0.30) as postage_rate, @cost_of_postage:=(quantity_chosen * @postage_rate) as postage, @taxable_price:=(@NEW_month_price - @cost_of_postage) as taxable, (@taxable_price * sales_tax_rate) as NEW_sales_tax As you can see, I am using variables to make some calcs, sure, I can do it long hand, but it gets long and confusing. Given the above, I end up with some vars like NEW_month_price, NEW_each_price etc, I would like to update ... SET price = NEW_each_price, but of course, that does not work. Suggestions? -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User variables in update statement
Hi Scott, Scott Haneda wrote: Is it possible to use user variables in an update statement, I can find only scant docs on it, but they do not pertain to what I am trying to do. Given this select: SELECT user_id, order_status, quantity_chosen, month_price, each_price, sales_tax, sales_tax_rate, @NEW_each_price:=(each_price + .06) as NEW_each_price, @NEW_month_price:=(quantity_chosen * @NEW_each_price) as NEW_month_price, @postage_rate:=(0.30) as postage_rate, @cost_of_postage:=(quantity_chosen * @postage_rate) as postage, @taxable_price:=(@NEW_month_price - @cost_of_postage) as taxable, (@taxable_price * sales_tax_rate) as NEW_sales_tax As you can see, I am using variables to make some calcs, sure, I can do it long hand, but it gets long and confusing. Given the above, I end up with some vars like NEW_month_price, NEW_each_price etc, I would like to update ... SET price = NEW_each_price, but of course, that does not work. Suggestions? It's possible, but I'm not sure it was ever intended. Still, I have found some very useful applications for it. The trick is to put the assignment inside a function, which will a) make sure the assignment happens b) present the assignment as an expression that can go on the right-hand-side of a SET clause. I explained in detail how it works here: http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/ But only read that if you want the background, which is a little tangential for your purposes. Read this instead: update tbl set -- Set each_price to its present value, while setting @NEW_each_price each_price = greatest(least(0, @NEW_each_price := each_price + .06)), price = @NEW_each_price, You can see some complex examples of that technique in this article: http://www.oreillynet.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html?page=3 Following the examples in that article, you can do a lot more flexible things than I just demonstrated. -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to set User variables (not system/session ones) at database startup
Hi, I want to set certain _user_ variables, which I will create myself, at the database startup time so that all clients can use the values stored [only one time at the beginning] in those variables instead of computing the same each time in triggers or other code. It's just like the package-level host variables of Oracle. How can I do the same in MySQL? I am using 5.0.22 and will switch to 5.0.24 soon. -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with user variables in where clause of sub query
Thanks for your kind words of opinion, if you feel you have a better way please do go ahead , i am going to show you the sql i ended up using which was a union to append the current summary at the end, i then had to use php afterwards to add up the totals as i was getting unexpected results when grouping by month as it tended to play with the calculations. Most of the variables setup are for ease of reading because its such a huge query, i could have easily put the queries into the appropriate places, but i still for instance am required to send say the @customerID variable to the sub queries to return a sum of results, mind you it is not at all possible to do joins for any of this, i was needing to get certain values and caulcations i could not obtain from a sum, group, join of each row. If you think i am an idiot go ahead say so as you already are, im self taught and still learning 6 years later .. Im not perfect and there is always room for improvment hence why Ive posted to the list for help/recommendations or else I usually never post. (SELECT @customerID:=c.customerID, @month:=fu.month AS month, DATE_FORMAT(fu.stats_date,'%M') AS month_long, @total_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND [EMAIL PROTECTED]) AS month_totals, @count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*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)) AND fu.month!=DATE_FORMAT(NOW(),'%m%y') GROUP BY fu.month,fu.customerID ORDER BY fu.month DESC) UNION (SELECT @customerID:=c.customerID, @month:=fu.month AS month, DATE_FORMAT(fu.stats_date,'%M') AS month_long, @total_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] AND fu.feedID IN (SELECT feedID FROM producers_join WHERE producerID IN (3))), @percentage:=((@feed_bandwidth/@total_bandwidth)) AS percentage, @month_totals:=(SELECT (c.monthly_price + (c.exceed_price * IF ([EMAIL PROTECTED] 0,@total_bandwidth-c.bandwidth_limit,0))) FROM feed_usage fu INNER JOIN customers c ON fu.customerID=c.customerID WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] GROUP BY fu.month, fu.customerID) AS month_totals, @count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*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)) AND fu.month=DATE_FORMAT(NOW(),'%m%y') GROUP BY fu.month,fu.customerID ORDER BY fu.month DESC) On 29/12/2005, at 5:19 PM, [EMAIL PROTECTED] wrote: Dan, You need to shoot your SQL tutor. Whoever taught you to write aggregate queries seriously took your money. You DO NOT need to use subqueries to do what you want to do. You do not need to write a full CREATE TABLE statement to create a temporary table (see other response). You do not need a FUNCTION or a STORED PROCEDURE or a VIEW. Please, get back to the basics. Re-read the appropriate parts of the manual and stop trying to make this harder than it should be. At most, this will take anywhere from 2 to 5 statements. You seem to have become so impressed with subqueries that you are trying to make a square peg fit into a round hole. IMHO using subqueries is not the optimal, effective, preferred, recommended, or suggested way to write this query. I am more than willing to help you to refactor your query and I am sure there will be others on this list if you don't want to deal with me any more. But please take my advice and take the simpler, more direct approach. Respectfully, Shawn Green Database
Re: need help with user variables in where clause of sub query
Dan Rossi [EMAIL PROTECTED] wrote on 12/29/2005 07:19:13 AM: Thanks for your kind words of opinion, if you feel you have a better way please do go ahead , i am going to show you the sql i ended up using which was a union to append the current summary at the end, i then had to use php afterwards to add up the totals as i was getting unexpected results when grouping by month as it tended to play with the calculations. big snip Dan, I said I would help and I am offering to do so. If you would rather have someone else take over, please just say so and I will back off. This forum is the best place I have ever found for getting all kinds of crazy help and if you prefer someone else, I understand. I need some information about your table designs. Would you please post the SHOW CREATE TABLE statement results for the following tables: feed_usage, customers, feeds, producers_join, and month_totals? For example: SHOW CREATE TABLE feed_usage\G (the \G makes the output vertical, much less wrapping) I think I understand your various table relationships as you seem to set them up well in your example table reference clause. 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)) AND fu.month!=DATE_FORMAT(NOW(),'%m%y') The funky numbers from your initial attempts will more than likely have been caused by several tables being joined having multiple rows of matching data. Not all data-based reports can exist as single statements. It's a limitation of the SQL language that when you want to do calculations based on the results of aggregation (SUM(), COUNT(), AVG(), etc.) , you need a second or more rounds of processing especially if you are aggregating several sets of data (usage data, bandwidth data, billing data, etc.). Temporary tables are the preferred place to store any intermediate results as they are specific to the connection that creates them (In fact the query engine generates at least one temporary table for every subquery you do). I don't want you to think I am completely against subqueries, I am not. It's just that you were not exactly using them to their full potential. If we keep our wits about us, we can get through this. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: need help with user variables in where clause of sub query
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND DATE_FORMAT(month,%m)[EMAIL PROTECTED] GROUP BY month, customerID) AS month_totals, @count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*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 [EMAIL PROTECTED] AND
issue with user variables in a view
Ok i have simplified my query into sections i have discovered that mysql5 doesnt like user variables in a select statement when creating a view, i am required to setup user variables so i can send the current row value primary key to a sub query, i really wished i could just send the field to the sub query ! 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 [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND DATE_FORMAT(month,%m)[EMAIL PROTECTED] GROUP BY month, customerID) AS month_totals, @count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*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 gives me View's SELECT contains a variable or paremeter , any ideas ? What im having to do , is create a view for a current months summary which is a select statement from one table , and then a second view of the previous months summary which is a select statement from another table which stores the totals statically. I have no idea what to do now :\ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with user variables in where clause of sub query
Um, thast exactly right each select is a list of results , i want to merge them then manipulate the data after putting them into a view, maybe a temp table is needed for this but i dont really want to do an entire create table statement aswell :\ On 29/12/2005, at 2:48 AM, [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND DATE_FORMAT(month,%m)[EMAIL PROTECTED] GROUP BY month, customerID) AS month_totals, @count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*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
Re: need help with user variables in where clause of sub query
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, [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND DATE_FORMAT(month,%m)[EMAIL PROTECTED] GROUP BY month, customerID) AS month_totals, @count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*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
Re: need help with user variables in where clause of sub query
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. On 29/12/2005, at 3:31 PM, Dan Rossi wrote: 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, [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND DATE_FORMAT(month,%m)[EMAIL PROTECTED] GROUP BY
Re: need help with user variables in where clause of sub query
Dan, You need to shoot your SQL tutor. Whoever taught you to write aggregate queries seriously took your money. You DO NOT need to use subqueries to do what you want to do. You do not need to write a full CREATE TABLE statement to create a temporary table (see other response). You do not need a FUNCTION or a STORED PROCEDURE or a VIEW. Please, get back to the basics. Re-read the appropriate parts of the manual and stop trying to make this harder than it should be. At most, this will take anywhere from 2 to 5 statements. You seem to have become so impressed with subqueries that you are trying to make a square peg fit into a round hole. IMHO using subqueries is not the optimal, effective, preferred, recommended, or suggested way to write this query. I am more than willing to help you to refactor your query and I am sure there will be others on this list if you don't want to deal with me any more. But please take my advice and take the simpler, more direct approach. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Dan Rossi [EMAIL PROTECTED] wrote on 12/28/2005 11:55:35 PM: I just tried to create a Function or Stored Procedure instead of making variables but it didnt even let me do this snip On 29/12/2005, at 3:31 PM, Dan Rossi wrote: 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, [EMAIL PROTECTED] 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. snip Dan Rossi [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND DATE_FORMAT(month,%m)[EMAIL PROTECTED] GROUP BY month, customerID) AS month_totals, @count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*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 [EMAIL PROTECTED] AND MONTH(month)[EMAIL PROTECTED]), @feed_bandwidth:=( SELECT bandwidth FROM month_totals INNER JOIN
need help with user variables in where clause of sub query
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 [EMAIL PROTECTED] and [EMAIL PROTECTED]) 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help with user variables in where clause of sub query
Dan Rossi [EMAIL PROTECTED] 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 [EMAIL PROTECTED] and [EMAIL PROTECTED]) 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. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: need help with user variables in where clause of sub query
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 [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_bandwidth:=( SELECT SUM(fu.bandwidth) FROM feed_usage fu WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND DATE_FORMAT(month,%m)[EMAIL PROTECTED] GROUP BY month, customerID) AS month_totals, @count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED]), @feed_count:=(SELECT count(*) FROM feed_usage WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*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 [EMAIL PROTECTED] AND MONTH(month)[EMAIL PROTECTED]), @feed_bandwidth:=( SELECT bandwidth FROM month_totals INNER JOIN WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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 [EMAIL PROTECTED] AND DATE_FORMAT(month,%m)[EMAIL PROTECTED] GROUP BY month, customerID) AS month_totals, @count:=(SELECT count(*) FROM feeds WHERE feedID IN (@feeds)), @feed_count:=(SELECT count(*) FROM month_totals WHERE [EMAIL PROTECTED] AND [EMAIL PROTECTED] 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)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*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 END On 28/12/2005, at 4:47 PM, [EMAIL PROTECTED] wrote: Dan Rossi [EMAIL PROTECTED] 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 [EMAIL PROTECTED] and [EMAIL PROTECTED]) 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
Calculations on user variables
Hi there I am experiencing a wierd bug in mysql 4.1 which is working in Mysql5 beta where i am trying to run calculations on user variables. I use these to pass values to sub queries. FORMAT((IF(c.bandwidth_limit=0, ((c.monthly_price/@count)*f.percentage_paid)[EMAIL PROTECTED], ((@[EMAIL PROTECTED])*f.percentage_paid) )),4) AS providers_cut this is strangely showing up as null or zero. So whats the go , any ideas ? If you want the full sql lemme know. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum number of user variables
Not that I'm aware of. What type of conversions are you doing that you need 30,000 use vars? An easy solution would be to try it and find out :) I need to move multiple linked entries (in around 12 tables) from one running server to another. I'm using auto_increment's all over the place and I have the following problem: I need to move (no replication possible) the information in those 12 tables to an identical functioning system (same software, same db, but other server) and I need to preserve the relations between the tables. The problem that I have is that the systems evolve independently and I can have the same id for 2 different entries on the system (e.g. on the first system I can have the id 10 for User1, but on second system id 10 would be for another user). I want to make a script that dumps the info into an sql file that I can run on the other server, something like this: insert into customers () values (); select @current_customer_id:=max(id) from customers; insert into customer_categ (customer_id, name...) values (@current_customer_id, categ...); And I have around 20-30 000 records that are linked together (I cannot use a single variable because the tables are linked 3 levels deep and I need to keep the intermediate id's in variables). -- mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum number of user variables
- Original Message - From: Neculai Macarie [EMAIL PROTECTED] To: Mysql mysql@lists.mysql.com Sent: Thursday, May 12, 2005 1:20 PM Subject: Re: Maximum number of user variables Not that I'm aware of. What type of conversions are you doing that you need 30,000 use vars? An easy solution would be to try it and find out :) I need to move multiple linked entries (in around 12 tables) from one running server to another. I'm using auto_increment's all over the place and I have the following problem: I need to move (no replication possible) the information in those 12 tables to an identical functioning system (same software, same db, but other server) and I need to preserve the relations between the tables. The problem that I have is that the systems evolve independently and I can have the same id for 2 different entries on the system (e.g. on the first system I can have the id 10 for User1, but on second system id 10 would be for another user). Perhaps after you dump the structure and data into sql files, you could remove temporarily the extra attribut auto increment to those columns. Then start inserting. After that add the auto increment attribut again. OR use bulkcopy(?) if im not mistaken, or any other methods to copy as it is to new dbservers (compress it first). Donny. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum number of user variables
Not that I'm aware of. What type of conversions are you doing that you need 30,000 use vars? An easy solution would be to try it and find out :) I need to move multiple linked entries (in around 12 tables) from one running server to another. I'm using auto_increment's all over the place and I have the following problem: I need to move (no replication possible) the information in those 12 tables to an identical functioning system (same software, same db, but other server) and I need to preserve the relations between the tables. The problem that I have is that the systems evolve independently and I can have the same id for 2 different entries on the system (e.g. on the first system I can have the id 10 for User1, but on second system id 10 would be for another user). Perhaps after you dump the structure and data into sql files, you could remove temporarily the extra attribut auto increment to those columns. Then start inserting. After that add the auto increment attribut again. OR use bulkcopy(?) if im not mistaken, or any other methods to copy as it is to new dbservers (compress it first). Donny, Thanks for the answer. Dumping the structure and data alone (mysqldump style) is not helping me because the tables are linked and I must update the linked tables with the new id's. -- mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum number of user variables
It sounds like you should be doing the link preservation and number update part in php or perl. Neculai Macarie wrote: Not that I'm aware of. What type of conversions are you doing that you need 30,000 use vars? An easy solution would be to try it and find out :) I need to move multiple linked entries (in around 12 tables) from one running server to another. I'm using auto_increment's all over the place and I have the following problem: I need to move (no replication possible) the information in those 12 tables to an identical functioning system (same software, same db, but other server) and I need to preserve the relations between the tables. The problem that I have is that the systems evolve independently and I can have the same id for 2 different entries on the system (e.g. on the first system I can have the id 10 for User1, but on second system id 10 would be for another user). Perhaps after you dump the structure and data into sql files, you could remove temporarily the extra attribut auto increment to those columns. Then start inserting. After that add the auto increment attribut again. OR use bulkcopy(?) if im not mistaken, or any other methods to copy as it is to new dbservers (compress it first). Donny, Thanks for the answer. Dumping the structure and data alone (mysqldump style) is not helping me because the tables are linked and I must update the linked tables with the new id's. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum number of user variables
Neculai Macarie [EMAIL PROTECTED] wrote on 05/12/2005 03:26:33 AM:
Not that I'm aware of. What type of conversions are you doing that
you
need 30,000 use vars? An easy solution would be to try it and find
out
:)
I need to move multiple linked entries (in around 12 tables) from
one
running server to another. I'm using auto_increment's all over the
place
and
I have the following problem: I need to move (no replication
possible)
the
information in those 12 tables to an identical functioning system
(same
software, same db, but other server) and I need to preserve the
relations
between the tables. The problem that I have is that the systems
evolve
independently and I can have the same id for 2 different entries on
the
system (e.g. on the first system I can have the id 10 for User1, but
on
second system id 10 would be for another user).
Perhaps after you dump the structure and data into sql files,
you could remove temporarily the extra attribut auto increment to
those
columns.
Then start inserting.
After that add the auto increment attribut again.
OR
use bulkcopy(?) if im not mistaken, or any other methods to copy as it
is
to
new dbservers
(compress it first).
Donny,
Thanks for the answer. Dumping the structure and data alone (mysqldump
style) is not helping me because the tables are linked and I must update
the
linked tables with the new id's.
--
mack /
Here is a summary of how I have merged hierarchical data structures in the
past. I start by adding a column or two to my destination data tables for
each table in the tree I need to reconstruct. The first new column (I
usually call it something like old_ID) holds the original PK of the
source record. The second (if necessary) will hold the original PARENT's
PK value.
As an example, let's imagine that I need to merge a table that looks like
{ID, PARENT_ID, DATA columns} into a new table with the same structure.
In this table PARENT_ID points to some other record in the same table. I
would add my two columns like this
ALTER TABLE new_table add old_ID int, add old_parentid int;
Then, I would need to map the INSERT like this
INSERT new_table (old_ID, old_parentid, data columns)
SELECT old_table id, parent_id, data columns;
Then I go back and update the PARENT_ID of the records in new_table with
the new ID value of their OLD parent records.
UPDATE new_table nt1
INNER JOIN new_table nt2
ON nt2.old_ID = nt1.old_parentid
SET nt1.PARENT_ID = nt2.ID
WHERE nt1.old_parent_ID is not null;
This re-creates the parent-child relationship that used to exist in
old_table by filling in the new values for the PARENT_ID that were
auto-generated when the old records were merged. Repeat this for each
table in your hierarchy. In this example both parent and child records
were from the same table but they didn't need to be. nt1 is the alias for
the child table while nt2 represents the parent table.
You will need to disable any FK constraints (InnoDB) while you rebuild
your parent-child relationships but once you have filled in the child's
parent_id field, you should be able to re-enable those keys and move on to
the next level. If you get an error, check your data. Working from the
top down, you should not create too many issues, especially if your data
was well-organized to start with. Once you have regenerated your
parent-child links to use the new auto_increment values, you can start
dropping the old_* columns to recover that space and optimize your tables
at the same time (thanks to the behavior of the ALTER TABLE).
ALSO, before you start, make a backup of your data (mysqldump works well
for most people). That way if you hose it up in some major way, you can at
least get back to where you started without too much pain.
If you have any questions, I will be lurking ;-)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Maximum number of user variables
Here is a summary of how I have merged hierarchical data structures in the
past. I start by adding a column or two to my destination data tables for
each table in the tree I need to reconstruct. The first new column (I
usually call it something like old_ID) holds the original PK of the
source record. The second (if necessary) will hold the original PARENT's
PK value.
As an example, let's imagine that I need to merge a table that looks like
{ID, PARENT_ID, DATA columns} into a new table with the same structure.
In this table PARENT_ID points to some other record in the same table. I
would add my two columns like this
ALTER TABLE new_table add old_ID int, add old_parentid int;
Then, I would need to map the INSERT like this
INSERT new_table (old_ID, old_parentid, data columns)
SELECT old_table id, parent_id, data columns;
Then I go back and update the PARENT_ID of the records in new_table with
the new ID value of their OLD parent records.
UPDATE new_table nt1
INNER JOIN new_table nt2
ON nt2.old_ID = nt1.old_parentid
SET nt1.PARENT_ID = nt2.ID
WHERE nt1.old_parent_ID is not null;
This re-creates the parent-child relationship that used to exist in
old_table by filling in the new values for the PARENT_ID that were
auto-generated when the old records were merged. Repeat this for each
table in your hierarchy. In this example both parent and child records
were from the same table but they didn't need to be. nt1 is the alias for
the child table while nt2 represents the parent table.
You will need to disable any FK constraints (InnoDB) while you rebuild
your parent-child relationships but once you have filled in the child's
parent_id field, you should be able to re-enable those keys and move on to
the next level. If you get an error, check your data. Working from the
top down, you should not create too many issues, especially if your data
was well-organized to start with. Once you have regenerated your
parent-child links to use the new auto_increment values, you can start
dropping the old_* columns to recover that space and optimize your tables
at the same time (thanks to the behavior of the ALTER TABLE).
ALSO, before you start, make a backup of your data (mysqldump works well
for most people). That way if you hose it up in some major way, you can at
least get back to where you started without too much pain.
If you have any questions, I will be lurking ;-)
Thanks for this solution. Indeed it's much simpler then needing 30 000 user
variables :).
--
mack /
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Maximum number of user variables
Hi! What is the maximum number of user variables that I can have in MySQL ? (I need to transfer a application from one server to another and I need to rebuild the links between tables with new id's on the new server (I'm using auto_increment extensively), and I have a solution that involves many user variables, around 30 000 in a single connection). -- mack / -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum number of user variables
Not that I'm aware of. What type of conversions are you doing that you need 30,000 use vars? An easy solution would be to try it and find out :) -Eric Neculai Macarie wrote: Hi! What is the maximum number of user variables that I can have in MySQL ? (I need to transfer a application from one server to another and I need to rebuild the links between tables with new id's on the new server (I'm using auto_increment extensively), and I have a solution that involves many user variables, around 30 000 in a single connection). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables in UPDATEs vs. SELECTs
I've got a table (pages) with an order column (pages.iOrder) and and grouping column (pages.iCategoryID). The order column is just a SMALLINT UNSIGNED column, that should contain the values 1-n for each iCategoryID. These are used to order the rows in each category. I'm in the process of writing an UPDATE query that will reorder the columns in reverse order and redefine the order number, so there will not be any duplicate values or gaps in each iOrder group. I think I've actually done what I intended to do, but I was wondering why the UPDATE seemed to work perfectly, while the equivalent (at least it looks equivalent to me) SELECT statement does not work at all. = UPDATE pages SET iOrder = IF(iCategoryID = @mGroup0, @iOrder := @iOrder + 1, @iOrder := 1), iCategoryID = (@mGroup0 := iCategoryID) ORDER BY iCategoryID, iOrder DESC; = SELECT iCategoryID, iOrder, IF(iCategoryID = @mGroup0, @iOrder := @iOrder + 1, @iOrder := 1) as iNewOrder, (@mGroup0 := iCategoryID) as [EMAIL PROTECTED] FROM pages ORDER BY iCategoryID, iOrder DESC; = From what I can tell in the online docs, both of these queries should, in fact, not work. Maybe I'm missing something here. I can get the select to work if I do this: SET @iOrder := 0, @mGroup0 := 0; Before I run the query. But, I also have several other tables I need this query to work on, and sometimes the Grouping column is a string, and if @mGroup0 isn't the same type of value as the Grouping column before I enter the SELECT query, it fails. So I would need to determine the type and each group column, then set @mGroup0 to the appropriate type before I run the select. All this isn't what I'm actually trying to accomplish, it was just a method I was using to develop the proper update statement. It really surprised me when the UPDATE statement works as is, with no prior setting of @iOrder or @mGroup0. How do the User variables in an UPDATE statement act differently than in a SELECT statement? Thanks, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Variables
Does anyone else have any ideas about this topic?
Thanks
Ed Reed [EMAIL PROTECTED] 04/23/05 1:29 pm
Thanks for the reply,
I realize that user variables disappear when the connection closes but I don't
understand why what I'm trying to accomplish can't be done. Doesn't it make
since that if you can load a single file with multiple SQL commands and have
that work succesfully then you should be able to have a single call with
multple SQL commands work just as succesfully? Is there any way to do what I
asked in my original post?
Thanks again for the reply.
Paul DuBois [EMAIL PROTECTED] 4/23/05 12:12:32 PM
At 22:04 -0700 4/22/05, Ed Reed wrote:
Thanks for the reply,
So is there anyway to use User Variables with a single connection.
All my apps are in VB6 and VBA. They all take a query, open a
connection, run query, fill array from query results, close
connection and pass back the array. Because of backward
compatibility there's no way I can change them to do otherwise.
User variables disappear when the connection closes.
Thanks again.
Chris [EMAIL PROTECTED] 04/22/05 7:56 PM
Ed Reed wrote:
If I run the following in MySQLFront v3.1
Set @A='Test';
Select @A;
I get back same result
+--+
| @A |
+--+
| Test |
+--+
If I run the same query in MySQL Query Browser v1.1.6 I get this,
ErrNo 1060, You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax
to use near ';
select @A' at line 1
and If I run the same query in my application I get the same error
as the Query Browser.
Anyone know how I can get my application to give me what I'm looking for?
The command line interface allows you to run multiple commands at once.
The Query Browser and PHP interfaces allow only one query per function
call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
each query separately.
This is certainly the case in your application, even if it's not PHP.
If you ran the queries separately in the Query Browser, you wouldn't get
the results you expect. It would forget the value of @A because it
closes the connection each time. It's possible to keep the connection
open by Starting a transaction (even if you're using MyISAM tables).
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Variables
Ed Reed [EMAIL PROTECTED] wrote on 04/25/2005 02:33:23 AM:
Does anyone else have any ideas about this topic?
Thanks
Ed Reed [EMAIL PROTECTED] 04/23/05 1:29 pm
Thanks for the reply,
I realize that user variables disappear when the connection closes
but I don't understand why what I'm trying to accomplish can't be
done. Doesn't it make since that if you can load a single file with
multiple SQL commands and have that work succesfully then you should
be able to have a single call with multple SQL commands work just as
succesfully? Is there any way to do what I asked in my original post?
Thanks again for the reply.
Paul DuBois [EMAIL PROTECTED] 4/23/05 12:12:32 PM
At 22:04 -0700 4/22/05, Ed Reed wrote:
Thanks for the reply,
So is there anyway to use User Variables with a single connection.
All my apps are in VB6 and VBA. They all take a query, open a
connection, run query, fill array from query results, close
connection and pass back the array. Because of backward
compatibility there's no way I can change them to do otherwise.
User variables disappear when the connection closes.
Thanks again.
Chris [EMAIL PROTECTED] 04/22/05 7:56 PM
Ed Reed wrote:
If I run the following in MySQLFront v3.1
Set @A='Test';
Select @A;
I get back same result
+--+
| @A |
+--+
| Test |
+--+
If I run the same query in MySQL Query Browser v1.1.6 I get this,
ErrNo 1060, You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax
to use near ';
select @A' at line 1
and If I run the same query in my application I get the same error
as the Query Browser.
Anyone know how I can get my application to give me what I'm looking
for?
The command line interface allows you to run multiple commands at once.
The Query Browser and PHP interfaces allow only one query per function
call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
each query separately.
This is certainly the case in your application, even if it's not PHP.
If you ran the queries separately in the Query Browser, you wouldn't
get
the results you expect. It would forget the value of @A because it
closes the connection each time. It's possible to keep the connection
open by Starting a transaction (even if you're using MyISAM tables).
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Dude, you should chill. You originally posted during the weekend, not
everyone keeps up at home. Sometimes it just takes a few days to get all
of the responses to a posting.
In VB (and VBA and VBScript) *you* control when the connection opens and
closes (not like the program query browser which you tried to test
with). Using ADO as an example, this snipped of code identifies a one
hour time window starting 5 minutes before the most recent entry in a log
table (it's a made-up query. I don't actually use this.) then uses that
window to get the actual log records.
set oConn = new ADODB.Connection
set oRS = new ADODB.Recordset
oConn.Open you connection string here
... processing...
oConn.Execute select @A := max(datefieldname)- interval 5 minute, @B :=
max(datefieldname)- interval 65 minute from logtable
more processing ...
sSQL = SELECT * from logtable where datefieldname @A and datefieldname
[EMAIL PROTECTED]
oRS.Open sSQL, oConn
... more processing ...
oRS.close
oConn.close
Until you close the connection, it stays open. That means that all queries
executed _through a particular connection_ have access to any variable
value you have already set with that connection. I set the values of @A
and @B in one query then, lines later, I used them in building a
recordset. Because the recordset is on the same connection the variables
were created with, its query sees them as populated values.
However!! Command.Execute() does not accept chained statements. You cannot
pass in two statements in a row separated by a semicolon. You must split
your SQL and execute it as separate requests. If you didn't split them up,
which statement's results code would Command.Execute() return with? What
if you got an error code as a result? Which query failed? How many
statements executed before failure? How much are you going to need to undo
(assuming you weren't in a transaction) to recover from a failed
statement?
Because the Recordset object supports multiple resultsets, you *MAY* be
able to pass in multiple statements within a single query (but I haven't
tested this!!!). That way, if statements 1,2, 3, and 5 succeed, you might
be able to see that statement 4 failed
Re: User Variables
Ed Reed wrote:
Thanks for the reply,
So is there anyway to use User Variables with a single connection. All my apps
are in VB6 and VBA. They all take a query, open a connection, run query, fill
array from query results, close connection and pass back the array. Because of
backward compatibility there's no way I can change them to do otherwise.
Thanks again.
If you're limited to a single query per connection, you probably can't
make full use of them, but, depending on the query you may be able to do
something like:
SELECT @A := 'Test',CONCAT('@A == ',@A,'')
I'd suggest reading http://dev.mysql.com/doc/mysql/en/variables.html to
see what use you can get out of them.
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Variables
At 22:04 -0700 4/22/05, Ed Reed wrote:
Thanks for the reply,
So is there anyway to use User Variables with a single connection.
All my apps are in VB6 and VBA. They all take a query, open a
connection, run query, fill array from query results, close
connection and pass back the array. Because of backward
compatibility there's no way I can change them to do otherwise.
User variables disappear when the connection closes.
Thanks again.
Chris [EMAIL PROTECTED] 04/22/05 7:56 PM
Ed Reed wrote:
If I run the following in MySQLFront v3.1
Set @A='Test';
Select @A;
I get back same result
+--+
| @A |
+--+
| Test |
+--+
If I run the same query in MySQL Query Browser v1.1.6 I get this,
ErrNo 1060, You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax
to use near ';
select @A' at line 1
and If I run the same query in my application I get the same error
as the Query Browser.
Anyone know how I can get my application to give me what I'm looking for?
The command line interface allows you to run multiple commands at once.
The Query Browser and PHP interfaces allow only one query per function
call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
each query separately.
This is certainly the case in your application, even if it's not PHP.
If you ran the queries separately in the Query Browser, you wouldn't get
the results you expect. It would forget the value of @A because it
closes the connection each time. It's possible to keep the connection
open by Starting a transaction (even if you're using MyISAM tables).
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Variables
Thanks for the reply,
I realize that user variables disappear when the connection closes but I don't
understand why what I'm trying to accomplish can't be done. Doesn't it make
since that if you can load a single file with multiple SQL commands and have
that work succesfully then you should be able to have a single call with
multple SQL commands work just as succesfully? Is there any way to do what I
asked in my original post?
Thanks again for the reply.
Paul DuBois [EMAIL PROTECTED] 4/23/05 12:12:32 PM
At 22:04 -0700 4/22/05, Ed Reed wrote:
Thanks for the reply,
So is there anyway to use User Variables with a single connection.
All my apps are in VB6 and VBA. They all take a query, open a
connection, run query, fill array from query results, close
connection and pass back the array. Because of backward
compatibility there's no way I can change them to do otherwise.
User variables disappear when the connection closes.
Thanks again.
Chris [EMAIL PROTECTED] 04/22/05 7:56 PM
Ed Reed wrote:
If I run the following in MySQLFront v3.1
Set @A='Test';
Select @A;
I get back same result
+--+
| @A |
+--+
| Test |
+--+
If I run the same query in MySQL Query Browser v1.1.6 I get this,
ErrNo 1060, You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax
to use near ';
select @A' at line 1
and If I run the same query in my application I get the same error
as the Query Browser.
Anyone know how I can get my application to give me what I'm looking for?
The command line interface allows you to run multiple commands at once.
The Query Browser and PHP interfaces allow only one query per function
call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
each query separately.
This is certainly the case in your application, even if it's not PHP.
If you ran the queries separately in the Query Browser, you wouldn't get
the results you expect. It would forget the value of @A because it
closes the connection each time. It's possible to keep the connection
open by Starting a transaction (even if you're using MyISAM tables).
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
User Variables
I'm having trouble using user variables and I hope someone can help, My test environment is MySQL 4.1.11 on WindowsXP with MyODBC 3.51.11 If I open a command line client, I can do this mysql SET @A='Test'; Query OK, 0 rows affected (0.00 sec) mysql Select @A; +--+ | @A | +--+ | Test | +--+ 1 row in set (0.00 sec) If I run the following in MySQLFront v3.1 Set @A='Test'; Select @A; I get back same result +--+ | @A | +--+ | Test | +--+ If I run the same query in MySQL Query Browser v1.1.6 I get this, ErrNo 1060, You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; select @A' at line 1 and If I run the same query in my application I get the same error as the Query Browser. Anyone know how I can get my application to give me what I'm looking for? Thanks!
Re: User Variables
Ed Reed wrote:
If I run the following in MySQLFront v3.1
Set @A='Test';
Select @A;
I get back same result
+--+
| @A |
+--+
| Test |
+--+
If I run the same query in MySQL Query Browser v1.1.6 I get this,
ErrNo 1060, You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ';
select @A' at line 1
and If I run the same query in my application I get the same error as the Query
Browser.
Anyone know how I can get my application to give me what I'm looking for?
The command line interface allows you to run multiple commands at once.
The Query Browser and PHP interfaces allow only one query per function
call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
each query separately.
This is certainly the case in your application, even if it's not PHP.
If you ran the queries separately in the Query Browser, you wouldn't get
the results you expect. It would forget the value of @A because it
closes the connection each time. It's possible to keep the connection
open by Starting a transaction (even if you're using MyISAM tables).
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Variables
Thanks for the reply,
So is there anyway to use User Variables with a single connection. All my apps
are in VB6 and VBA. They all take a query, open a connection, run query, fill
array from query results, close connection and pass back the array. Because of
backward compatibility there's no way I can change them to do otherwise.
Thanks again.
Chris [EMAIL PROTECTED] 04/22/05 7:56 PM
Ed Reed wrote:
If I run the following in MySQLFront v3.1
Set @A='Test';
Select @A;
I get back same result
+--+
| @A |
+--+
| Test |
+--+
If I run the same query in MySQL Query Browser v1.1.6 I get this,
ErrNo 1060, You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ';
select @A' at line 1
and If I run the same query in my application I get the same error as the
Query Browser.
Anyone know how I can get my application to give me what I'm looking for?
The command line interface allows you to run multiple commands at once.
The Query Browser and PHP interfaces allow only one query per function
call. So mysql_query('SET @A; SELECT @A;') would fail. You need to run
each query separately.
This is certainly the case in your application, even if it's not PHP.
If you ran the queries separately in the Query Browser, you wouldn't get
the results you expect. It would forget the value of @A because it
closes the connection each time. It's possible to keep the connection
open by Starting a transaction (even if you're using MyISAM tables).
Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: User variables
[snip] This does not work. It appears that the user variable is not picked up in the WHERE clause - the query works fine if I have: [/snip] You have a fundamental lack of understanding of user variables. A quick read of http://dev.mysql.com/doc/mysql/en/Variables.html will reveal User variables may be used where expressions are allowed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables
I am having a problem with a query: SET @SoftwareID = 7; SELECT s.softwareID, s.softwareName, s.softwareVersion, s.softwareCreated, s.softwareChanged, b.buildName, s.supportFlag, s.softwareDesc, s.softwareLicense, s.softwareLocations, s.softwareProductKey, p.platformName, v.vendorName, v.vendorURL FROM software s INNER JOIN platform_groups pg on s.softwareID = pg.softwareID INNER JOIN platforms p on pg.platformID = p.platformID INNER JOIN vendors v on s.vendorID = v.vendorID INNER JOIN builds b on s.buildTypeID = b.buildTypeID WHERE s.softwareID = @SoftwareID This does not work. It appears that the user variable is not picked up in the WHERE clause - the query works fine if I have: WHERE s.softwareID = 7 Is what I am trying to do not supported? I am using version 4.1.7 on FreeBSD 5.3. Best regards, Erich Beyrent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: User variables
Hi, Set the value as SET @SoftwareID:=7; Now, use WHERE s.softwareID = @SoftwareID at the end of your query. Thanks, Narasimha -Original Message- From: Erich Beyrent [mailto:[EMAIL PROTECTED] Sent: Monday, December 13, 2004 8:30 PM To: [EMAIL PROTECTED] Subject: User variables I am having a problem with a query: SET @SoftwareID = 7; SELECT s.softwareID, s.softwareName, s.softwareVersion, s.softwareCreated, s.softwareChanged, b.buildName, s.supportFlag, s.softwareDesc, s.softwareLicense, s.softwareLocations, s.softwareProductKey, p.platformName, v.vendorName, v.vendorURL FROM software s INNER JOIN platform_groups pg on s.softwareID = pg.softwareID INNER JOIN platforms p on pg.platformID = p.platformID INNER JOIN vendors v on s.vendorID = v.vendorID INNER JOIN builds b on s.buildTypeID = b.buildTypeID WHERE s.softwareID = @SoftwareID This does not work. It appears that the user variable is not picked up in the WHERE clause - the query works fine if I have: WHERE s.softwareID = 7 Is what I am trying to do not supported? I am using version 4.1.7 on FreeBSD 5.3. Best regards, Erich Beyrent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
user variables and regexp
User variables do not work with REGEXP under MySQL 4.0.21 4.1.5. Is this a bug or a feature? -- ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user variables and regexp
At 3:12 +0200 10/8/04, Przemyslaw Popielarski wrote: User variables do not work with REGEXP under MySQL 4.0.21 4.1.5. Is this a bug or a feature? It's difficult to provide an answer to this because you're providing no information about what do not work means. Can you be more specific? mysql set @x = 'abc'; Query OK, 0 rows affected (0.13 sec) mysql select @x like 'a%'; +--+ | @x like 'a%' | +--+ |1 | +--+ 1 row in set (0.08 sec) mysql select @x like 'b%'; +--+ | @x like 'b%' | +--+ |0 | +--+ 1 row in set (0.01 sec) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user variables and regexp
Paul DuBois [EMAIL PROTECTED] wrote: User variables do not work with REGEXP under MySQL 4.0.21 4.1.5. Is this a bug or a feature? It's difficult to provide an answer to this because you're providing no information about what do not work means. Can you be more specific? Sure. I didn't want to write to not mess in case this is a feature. So here goes my test case: SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE @a REGEXP [0-9] - Empty set (0.03 sec) SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE FIRMLEGALZIPCODE REGEXP [0-9]; - 2803 rows in set (0.03 sec) -- ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user variables and regexp
At 3:47 +0200 10/8/04, Przemyslaw Popielarski wrote: Paul DuBois [EMAIL PROTECTED] wrote: User variables do not work with REGEXP under MySQL 4.0.21 4.1.5. Is this a bug or a feature? It's difficult to provide an answer to this because you're providing no information about what do not work means. Can you be more specific? Sure. I didn't want to write to not mess in case this is a feature. So here goes my test case: SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE @a REGEXP [0-9] - Empty set (0.03 sec) You're expecting the value to be selected first so that you then can test it with the WHERE clause later. SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE FIRMLEGALZIPCODE REGEXP [0-9]; - 2803 rows in set (0.03 sec) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user variables and regexp
Paul DuBois [EMAIL PROTECTED] wrote: SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE @a REGEXP [0-9] - Empty set (0.03 sec) You're expecting the value to be selected first so that you then can test it with the WHERE clause later. Of course you're right. Thanks. According to your suggestion this one works okey: SELECT @a FROM tCustomers WHERE @a:=FIRMLEGALZIPCODE REGEXP [0-9] -- ./ premax ./ [EMAIL PROTECTED] ./ koniec i bomba, a kto czytal ten traba. w.g. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: user variables and regexp
At 4:01 +0200 10/8/04, Przemyslaw Popielarski wrote: Paul DuBois [EMAIL PROTECTED] wrote: SELECT @a:=FIRMLEGALZIPCODE FROM tCustomers WHERE @a REGEXP [0-9] - Empty set (0.03 sec) You're expecting the value to be selected first so that you then can test it with the WHERE clause later. Of course you're right. Thanks. According to your suggestion this one works okey: SELECT @a FROM tCustomers WHERE @a:=FIRMLEGALZIPCODE REGEXP [0-9] True, although in this case you don't need a user variable at all: SELECT FIRMLEGALZIPCODE FROM tCustomers WHERE FIRMLEGALZIPCODE REGEXP [0-9] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
assigning values to user variables in the mysql client
I would like to do something like this in the mysql client Select CourseId Into @CourseId From Course Where CourseCd='ENGL'; I also tried @CourseId = select CourseId from Course where CourseCd = 'ENGL'; Neither syntax works. So I am wondering if there is a way to assign values using SQL in the mysql client. This would be _very_ handy for scripting. Here is what I produced to get around the problem. It is a bit convoluted but if you are wishing for this functionality (and it does not yet exist) it will do the trick: insert into Course values(null, 21, 14, '', 99, 'Not Assigned', 0, 0) ; select concat( 'set sql_auto_is_null = 1; ', 'set @CourseId = ', CourseId,'; ', 'update Test set CourseId = @UniCourseId where Title like \'SAT%\'; ' ) into outfile 'update_test.sql' from Course where CourseId is null ; source /var/lib/mysql/db_name/update_unitest.sql ; set sql_auto_is_null = 0 ; Be sure to clean up your data directory by deleting the file created as select ... into outfile places files into the diretory of the db on which the query is entered Boyd E. Hemphill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: assigning values to user variables in the mysql client
You have the wrong syntax. You need SELECT @CourseId:= CourseId FROM Course WHERE CourseId='ENGL'; See the manual http://dev.mysql.com/doc/mysql/en/Variables.html for more. Michael Boyd E. Hemphill wrote: I would like to do something like this in the mysql client Select CourseId Into @CourseId From Course Where CourseCd='ENGL'; I also tried @CourseId = select CourseId from Course where CourseCd = 'ENGL'; Neither syntax works. So I am wondering if there is a way to assign values using SQL in the mysql client. This would be _very_ handy for scripting. Here is what I produced to get around the problem. It is a bit convoluted but if you are wishing for this functionality (and it does not yet exist) it will do the trick: insert into Course values(null, 21, 14, '', 99, 'Not Assigned', 0, 0) ; select concat( 'set sql_auto_is_null = 1; ', 'set @CourseId = ', CourseId,'; ', 'update Test set CourseId = @UniCourseId where Title like \'SAT%\'; ' ) into outfile 'update_test.sql' from Course where CourseId is null ; source /var/lib/mysql/db_name/update_unitest.sql ; set sql_auto_is_null = 0 ; Be sure to clean up your data directory by deleting the file created as select ... into outfile places files into the diretory of the db on which the query is entered Boyd E. Hemphill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables optimization of IF()
Hi everybody. I generally try to parametrize out my queries as much as possible with user-variables. So, say you have a general query for all users: --- SELECT ... FROM users --- It's nice to do this: SET @USER_ID:= NULL; SELECT ... FROM users WHERE IF(@USER_ID IS NOT NULL,[EMAIL PROTECTED],1) --- This *works*, but when you set @USER_ID to a number, the 4.1 optimizer doesn't optimize it very well: it does a table-scan, instead of using the index on user_id. Has anyone got a good alternative that will do less table-scans? Or, if a developer is listening, can you give us details on how the optimizer deals with IF()? Thanks. Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables optimization of IF()
Hi everybody. I often try to parametrize out my queries as much as possible with user-variables. So, say you have a general query for all users: --- SELECT ... FROM users --- It's nice to do this: SET @USER_ID:= NULL; SELECT ... FROM users WHERE IF(@USER_ID IS NOT NULL,[EMAIL PROTECTED],1) --- This *works*, but when you set @USER_ID to a variable, the 4.1 optimizer doesn't optimize it very well: it does a table-scan, instead of using the index on user_id. Has anyone got a good alternative that will do less table-scans? Or, if a developer is listening, can you give us details on how the optimizer deals with IF()? Thanks. Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User variables + SUM + GROUP BY = strange behavior
well, it seems to be fine without SUM and GROUP BY... E.g., SELECT @a:=Charge, @b:=Cost, @[EMAIL PROTECTED] as Margin ... produces expected results. Emmett Bishop wrote: Vadim, if I'm not mistaken, you can't set a variable then use it in the same statement. See http://dev.mysql.com/doc/mysql/en/Variables.html A little ways down the page... The general rule is to never assign and use the same variable in the same statement. -- Tripp --- Vadim P. [EMAIL PROTECTED] wrote: Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT -LEFT(CallTime,10) AS CallDate, -@a := SUM(Charge), -@b := SUM(Cost), -@a - @b, -@a, -@b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User variables + SUM + GROUP BY = strange behavior
Not sure on how exactly variables work in MySQL but I do know that according to ANSI SQL group bys are done before other things in the query. So your query would perform the group by then it would do the actual select. This could be one reason for strange results. Thanks, Andrew From: Vadim P. [EMAIL PROTECTED] To: Emmett Bishop [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: User variables + SUM + GROUP BY = strange behavior Date: Fri, 16 Apr 2004 05:50:12 -0400 well, it seems to be fine without SUM and GROUP BY... E.g., SELECT @a:=Charge, @b:=Cost, @[EMAIL PROTECTED] as Margin ... produces expected results. Emmett Bishop wrote: Vadim, if I'm not mistaken, you can't set a variable then use it in the same statement. See http://dev.mysql.com/doc/mysql/en/Variables.html A little ways down the page... The general rule is to never assign and use the same variable in the same statement. -- Tripp --- Vadim P. [EMAIL PROTECTED] wrote: Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT -LEFT(CallTime,10) AS CallDate, -@a := SUM(Charge), -@b := SUM(Cost), -@a - @b, -@a, -@b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Get rid of annoying pop-up ads with the new MSN Toolbar FREE! http://toolbar.msn.com/go/onm00200414ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables + SUM + GROUP BY = strange behavior
Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT - LEFT(CallTime,10) AS CallDate, - @a := SUM(Charge), - @b := SUM(Cost), - @a - @b, - @a, - @b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++---+-+-++- -+ | CallDate | @a := SUM(Charge) | @b := SUM(Cost) | @a - @b | @a | @b | ++---+-+-++- -+ . | 2004-03-01 | 621.059 | 249.310 | 30.882 | 39.512 | 8.63 | | 2004-02-29 |54.620 | 17.660 | 30.882 | 39.512 | 8.63 | | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | | 2004-02-27 | 622.282 | 248.920 | 30.882 | 39.512 | 8.63 | | 2004-02-26 | 607.274 | 277.100 | 30.882 | 39.512 | 8.63 | | 2004-02-25 | 709.698 | 308.580 | 30.882 | 39.512 | 8.63 | | 2004-02-24 | 783.210 | 298.560 | 30.882 | 39.512 | 8.63 | | 2004-02-23 | 799.764 | 252.890 | 30.882 | 39.512 | 8.63 | .
User variables + SUM + GROUP BY = strange behavior
Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT - LEFT(CallTime,10) AS CallDate, - @a := SUM(Charge), - @b := SUM(Cost), - @a - @b, - @a, - @b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User variables + SUM + GROUP BY = strange behavior
Vadim, if I'm not mistaken, you can't set a variable then use it in the same statement. See http://dev.mysql.com/doc/mysql/en/Variables.html A little ways down the page... The general rule is to never assign and use the same variable in the same statement. -- Tripp --- Vadim P. [EMAIL PROTECTED] wrote: Sorry, the message got garbled, here is a more digestible look: -Original Message- Hello all, Could anyone comment on User Variable behavior in the example below? Thanks, Vadim. = mysql SELECT -LEFT(CallTime,10) AS CallDate, -@a := SUM(Charge), -@b := SUM(Cost), -@a - @b, -@a, -@b - FROM Calls - GROUP by CallDate - ORDER BY CallDate DESC; ++--++-++- | CallDate | @a:= SUM(Charge) | @b:= SUM(Cost) | @a - @b | @a | @b ++--++-++- ... | 2004-03-01 | 621.059 |249.310 | 30.882 | 39.512 | 8.63 | 2004-02-29 | 54.620 | 17.660 | 30.882 | 39.512 | 8.63 | 2004-02-28 | 205.581 | 17.460 | 30.882 | 39.512 | 8.63 | 2004-02-27 | 622.282 |248.920 | 30.882 | 39.512 | 8.63 | 2004-02-26 | 607.274 |277.100 | 30.882 | 39.512 | 8.63 | 2004-02-25 | 709.698 |308.580 | 30.882 | 39.512 | 8.63 | 2004-02-24 | 783.210 |298.560 | 30.882 | 39.512 | 8.63 | 2004-02-23 | 799.764 |252.890 | 30.882 | 39.512 | 8.63 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Variables in SELECT statement and LIKE syntax
These are the statements that are currently giving me the problems. SELECT @main_id:=ID, @FCSecID:=FCSecID, @Periodicity:=Periodicity, @EOP:=EOP FROM main_data_temp WHERE checked=0 ORDER BY fcsecid ASC LIMIT 1; SELECT * FROM actuals WHERE [EMAIL PROTECTED] AND Period LIKE '[EMAIL PROTECTED]'; # AND EOP LIKE '[EMAIL PROTECTED]' ORDER BY n8 ASC; The first statement works exactly as it should returning 1 row of data that has not been checked, but the second statement does not return any data when it really should be. If I chop off the statement to be only SELECT * FROM actuals WHERE [EMAIL PROTECTED] then it works just fine returning data but as soon as I add any LIKE clause into the statement nothing is returned. Am I missing something easy here or is this just not possible? Bobby
User Variables
Hi Guys, Do you know what version user variables were introduced? I can't seem to get them working in 3.22 and yet they work in 3.23. Best regards, Bruno Mustone
Re: User Variables
At 12:05 PM + 11/17/03, Bruno Mustone wrote: Hi Guys, Do you know what version user variables were introduced? I can't seem to get them working in 3.22 and yet they work in 3.23. They were introduced in 3.23.6. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: User Variables
Hmmm they seem to work in version 3.23.57. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 18:38 To: Bruno Mustone; [EMAIL PROTECTED] Subject: Re: User Variables At 12:05 PM + 11/17/03, Bruno Mustone wrote: Hi Guys, Do you know what version user variables were introduced? I can't seem to get them working in 3.22 and yet they work in 3.23. They were introduced in 3.23.6. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: User Variables
At 6:45 PM + 11/17/03, Bruno Mustone wrote: Hmmm they seem to work in version 3.23.57. Given that they were introduced in 3.23.6 (as stated below), that is not surprising. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: 17 November 2003 18:38 To: Bruno Mustone; [EMAIL PROTECTED] Subject: Re: User Variables At 12:05 PM + 11/17/03, Bruno Mustone wrote: Hi Guys, Do you know what version user variables were introduced? I can't seem to get them working in 3.22 and yet they work in 3.23. They were introduced in 3.23.6. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables not working
Hi group, Is there anything I need to set in MySQL in order to use USER variables? Thanks, __ NZEYIMANA Emery Fabrice NEFA Computing Services, Inc. P.O. Box 5078 Kigali Office Phone: +250-51 11 06 Office Fax: +250-50 15 19 Mobile: +250-08517768 Email: [EMAIL PROTECTED] http://www.nefacomp.net/
Re: User variables not working
Director General: NEFACOMP [EMAIL PROTECTED] wrote: Is there anything I need to set in MySQL in order to use USER variables? No. What is wrong with user variables for you? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange behavior -- user variables in 4.0.14b
I get the following strange behavior with a user variable. @T has the value 0 to start; after adding 1 to @T a few times, it ends up with a clearly incorrect value. I'd expect it to have a value of 280 after the second select. -- SELECT @T -- +--+ | @T | +--+ | 0| +--+ 1 row in set (0.00 sec) -- UPDATE contown_svr SET contown_id=(@T:[EMAIL PROTECTED]) -- Query OK, 280 rows affected (0.05 sec) Rows matched: 280 Changed: 280 Warnings: 280 -- SELECT @T -- +--+ | @T | +--+ | 1.8669204411349e+021 | +--+ 1 row in set (0.00 sec) --- More data: I'm using Windows 2000 with MySQL 4.0.14b, and connected through localhost. It appears to work correctly on MySQL 4.0.13 and MySQL 3.23.57. I also tried it on Linux with MySQL 4.0.13, and it worked correctly. It continues to fail with the 3.23.57 client and the 4.0.14b server. I get various values for @T, sometimes with a negative exponent. Sometimes it gives the correct value once, then twice the correct value on the second try, etc., despite @T being reset to zero. Sometimes, when I select the values in contown_svr, contown_id (which is an int) prints as something like 561.1. This happens when I have the mysql client read a file. When I cut and paste the content of the file to console, it appears to give the correct result. Any help would be appreciated. It sure sounds to me like a bug in thread synchronization within the server. Here's the smallest program I've gotten to fail. It still fails (gives wrong value to @T) even if the select returns 0 rows, but it doesn't fail if I remove the insert...select. I'll try to cut it down some more and post an example that's not missing the data--but it may take a while to get to it. - select @t:=0; drop table if exists contown_svr; create table contown_svr select * from contown where 0; insert into contown_svr select -999,pw.owner,pc.contact_id,0 from fundown pw inner join fundcont pc using (funding_id) left join contown cw on cw.contact_id = pc.contact_id left join grouptree on pw.owner=subject and cw.owner=target where subject is null and pc.contact_id 0; select @t; update contown_svr set contown_id=(@t:=(@t+1)); select @t; exit -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Variables on Startup
I would like to be able to set a user variable on startup of MySQL. I have review the documentation and searched the archives and I haven't seen anything about being able to do, or not do this. Can this be done? Since I'm unable to find reference to it, my guess is no, but I thought I'd give the list a try to see if could get some input on this. We are currently running version 4.0.7 in production. Thanks, Ray Elenteny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Variables doesn't work?
Thanks for your info, I d/l SQLyog and it seems really cool. MySQLFront 2.5 had so many bugs that I had to leave it. I prefer SQLyog now. Its FREE !!! and has all the features ( plus some realy cool extra features ). You can try SQLyog at http://www.webyog.com/sqlyog Karam --- Peter Brawley [EMAIL PROTECTED] wrote: Not just MySQLFront. PhpMyAdmin chokes on it, too. - - Original Message - From: ML To: [EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 9:08 AM Subject: Re: User Variables doesn't work? You are right, I tried from command line and it works, the problem occurs with MySQL Front v2.5. This is strange because I love this software and this is a strange bug... Regards. I'm using User Variables, I tried it with the query found in the mysql manul: SELECT @t1:=(@t2:=1)[EMAIL PROTECTED]:=4,@t1,@t2,@t3; But I receive this error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(@t2NULL1)[EMAIL PROTECTED],@t1,@t2,@t3' at line 1 I have MySQL v4.0.12 Worked perfect for me. Did you execute the above query from command-line client or what client did you use? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Variables doesn't work?
Not just MySQLFront. PhpMyAdmin chokes on it, too. - - Original Message - From: ML To: [EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 9:08 AM Subject: Re: User Variables doesn't work? You are right, I tried from command line and it works, the problem occurs with MySQL Front v2.5. This is strange because I love this software and this is a strange bug... Regards. I'm using User Variables, I tried it with the query found in the mysql manul: SELECT @t1:=(@t2:=1)[EMAIL PROTECTED]:=4,@t1,@t2,@t3; But I receive this error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(@t2NULL1)[EMAIL PROTECTED],@t1,@t2,@t3' at line 1 I have MySQL v4.0.12 Worked perfect for me. Did you execute the above query from command-line client or what client did you use? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: User Variables doesn't work?
MySQLFront 2.5 had so many bugs that I had to leave it. I prefer SQLyog now. Its FREE !!! and has all the features ( plus some realy cool extra features ). You can try SQLyog at http://www.webyog.com/sqlyog Karam --- Peter Brawley [EMAIL PROTECTED] wrote: Not just MySQLFront. PhpMyAdmin chokes on it, too. - - Original Message - From: ML To: [EMAIL PROTECTED] Sent: Tuesday, May 27, 2003 9:08 AM Subject: Re: User Variables doesn't work? You are right, I tried from command line and it works, the problem occurs with MySQL Front v2.5. This is strange because I love this software and this is a strange bug... Regards. I'm using User Variables, I tried it with the query found in the mysql manul: SELECT @t1:=(@t2:=1)[EMAIL PROTECTED]:=4,@t1,@t2,@t3; But I receive this error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(@t2NULL1)[EMAIL PROTECTED],@t1,@t2,@t3' at line 1 I have MySQL v4.0.12 Worked perfect for me. Did you execute the above query from command-line client or what client did you use? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. http://search.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
user variables in ODBC
It seems like MyODBC (or ODBC in general ?) cannot accept user variables. The following query works OK when quering directly through the API but doesnt work via MyODBC Any suggestions to some workaround allowing the use of user variables??? SELECT id, @s:=lang lang=da, @o:=objekter.overskrift, @p0:=POSITION(@s IN @o), @l:=length(@s), if (@p0=0, @p1:=POSITION(lang lang= IN @o), @p1:=LOCATE(/lang,@o,@p0+@l) ), if (@p0=0, if (@p1=0,@o,LEFT(@o,@p1)), SUBSTRING(@o,@p0,@p1-@p0) ) AS overskrift , ... and a lot more... It's testet with windows MyODBC-version 2.50.27 in an delphi-application using modbc-component. klavs - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Transactions, Deadlocks, User Variables and Replication
Hi, I'm developing a system which will use transactions with InnoDB. In principle, when you use transactions, there is some risk that a deadlock will happen and MySQL will abort the transaction. In this situation, you should generally try to do the transaction again. I'm wondering * Which error codes are an indication that a transaction should be retried? (Just a deadlock, or anything else? What about a failure on COMMIT?) * Is there some easy sequence of transactions I can do that will make MySQL deadlock for testing purposes? (Or should I just screw around with it until I can make it deadlock?) * In section 4.10.4 it says Update queries that use user variables are not replication-safe (yet) -- Is this still the case? -- If so, will this be fixed in the near future? My plan is to store a list of update statements that together will form a transaction. Since I want to centralize the logic for redoing failed transactions, I'd rather like to pass a list of queries to a function which will do all the updates in a transaction and redo them. The only kind of read access to the database that these transactions will need will be getting the LAST_INSERT_ID(). User variables would be a convenient way to do this, although I could certainly find another way to do it if that's not an option. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: User Variables
At 12:19 -0400 5/9/02, Luc Foisy wrote: hmm. ok i see what you are getting at but you do that every time you use a where clause, do you not? No, not really SELECT value FROM table WHERE value = 5 In this case value is used in the WHERE clause to determine which records to select. Once selected, value can be listed in the column output list. SELECT IF( value 0, value2, value3 ) AS 'value4' FROM table WHERE value4 = 5 In this case, the query is illegal. Aliases cannot be used in WHERE clauses. (You didn't actually try this query, right?) This is the same kind of problem that I *think* you're having with SQL variables: Trying to select a value at the same time that you use it to determine which values to select. Maybe it would help to see this same problem in another context. This is from the Cookbook; it illustrates the same phenomenon of trying to use a value two ways at once: MIN() and MAX() find the endpoints of a range of values, but sometimes when find a minimum or maximum value, you're also interested in other values from the row in which the value occurs. For example, you can find the largest state population like this: mysql SELECT MAX(pop) FROM states; +--+ | MAX(pop) | +--+ | 29760021 | +--+ But that doesn't show you which state has this population. The obvious way to try to get that information is like this: mysql SELECT name, MAX(pop) FROM states WHERE pop = MAX(pop); ERROR at line 1: Invalid use of group function Probably everyone attempts something like that sooner or later, but it doesn't work, because aggregate functions like MIN() and MAX() cannot be used in WHERE clauses. The intent of the statement is to determine which record has the maximum population value, then display the associated state name. The problem is that while you and I know perfectly well what we'd mean by writing such a thing, it makes no sense at all to MySQL. The query fails because MySQL uses the WHERE clause to determine which records to select, but it knows the value of an aggregate function only after selecting the records from which the function's value is determined! So, in a sense, the statement is self-contradictory. In the conditional join, the variable select does not require any information from the joined table, so I wouldn't think it would matter [the above is just because I like to argue and i think too much :)] The single select statement issues no error, and it does retrieve a value (just a little too late), it must be how the select statement is followed through (as in order of operations) Are all joins completed first? (hmm, that would make sense wouldn't it) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: User Variables
At 11:57 -0400 5/9/02, Luc Foisy wrote: It may not make any sense to you, but it makes perfect sense to me Okay. Then it would seem that either: - I understand what you want to do, in which case you can't do it, because you're using SQL variables in a self-contradictory way. - I don't understand what you want to do, which is perfectly possible, since even the broken up queries below are pretty impenetrably formatted. Maybe it would look better to you if written as so: SELECT @AddressJoinID := IF (CUSTOMER.ID_ADDRESS_BILLTO 0, CUSTOMER.ID_ADDRESS_BILLTO, CUSTOMER.ID_ADDRESS) AS 'Junk' FROM INVOICEHEADER LEFT JOIN CUSTOMER ON INVOICEHEADER.ID_CUSTOMER = CUSTOMER.ID WHERE INVOICEHEADER.ID = [param:Invoice ID] SELECT INVOICEHEADER.ID AS 'ID_INVOICEHEADER', DATE_FORMAT(INVOICEHEADER.Date,'%Y/%m/%d') AS 'Invoice_Date', INVOICEHEADER.ID_CUSTOMER AS 'ID_CUSTOMER', INVOICEHEADER.SubTotal, INVOICEHEADER.Tax1Total AS 'Tax1Total', INVOICEHEADER.GrandTotal AS 'GrandTotal', CUSTOMER.Company AS 'Cust_Company', CUSTOMER.*, COMPANY.*, TRIM(CONCAT(IF(ADDRESS.UnitNumber IS NULL,'',ADDRESS.UnitNumber),' ',ADDRESS.CivicNumber,' ',STREET.Name,' ',IF(STREETTYPE.Abv IS NULL,'',STREETTYPE.Abv))) AS 'Address_1', ADDRESS.Line2 AS 'Address_2', CONCAT(CITY.Name,', ',PROVINCE.Name) AS 'Address_3', ADDRESS.PostalCode AS 'Cust_PostalCode' FROM INVOICEHEADER LEFT JOIN CUSTOMER ON INVOICEHEADER.ID_CUSTOMER = CUSTOMER.ID LEFT JOIN ADDRESS ON ADDRESS.ID = @AddressJoinID LEFT JOIN STREET ON ADDRESS.ID_STREET = STREET.ID LEFT JOIN STREETTYPE ON ADDRESS.ID_STREETTYPE = STREETTYPE.ID LEFT JOIN CITY ON ADDRESS.ID_CITY = CITY.ID LEFT JOIN PROVINCE ON ADDRESS.ID_PROVINCE = PROVINCE.ID LEFT JOIN COUNTRY ON ADDRESS.ID_COUNTRY = COUNTRY.ID LEFT JOIN COMPANY ON CUSTOMER.ID_COMPANY = COMPANY.ID WHERE INVOICEHEADER.ID = [param:Invoice ID] Can't use an IF() on the join, but we want to join conditionally (customer has two address fields, if one is blank, then use the other one) ( the query does not print out anything directly, values are grabbed from the result set and formatted into a printable form ) Since all the joins in the first query exist in the second query, I should be able to create and use the same variable in the second query ( or the documentation needs to be revamped stating that you can't use variables in the same select statement at all) You *can* use them in the same statement, but as the manual states, if you access a variable that is set in the same statement, the value you access for one row is the value that was assigned from the previous row. And this does not seem to fit what you're trying to accomplish. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
User Variables and Previous Row Question
I have been trying to use 'user variables' to keep track of the previous row for use in a calculation of the present row. Is there a way I can do this? Or is there a better way in trying to use a previous rows value in the present row. For example; SELECT number as current_day, (number - prevnumber) as change_from_prev_day FROM TABLE ORDER BY DATE prevnumber is the reference that I need from the previous row. Am I missing something simple or do I have to do this outside of MYSQL? Any help would be appreciated. thanks, jasmin. __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: User Variables and Previous Row Question
Jasmin, Thursday, May 02, 2002, 5:00:14 PM, you wrote: JB I have been trying to use 'user variables' to keep JB track of the previous row for use in a calculation of JB the present row. Is there a way I can do this? Or JB is there a better way in trying to use a previous JB rows value in the present row. JB For example; JB SELECT number as current_day, (number - prevnumber) JB as change_from_prev_day FROM TABLE ORDER BY DATE JB prevnumber is the reference that I need from the JB previous row. JB Am I missing something simple or do I have to do this JB outside of MYSQL? You can do something like that: SELECT @a:=0; SELECT (number-@a) AS change_from_prev_day, @a:=number FROM your_table; Note: you should set value to variable _after_ calculation. You can find more info about user variables at: http://www.mysql.com/doc/V/a/Variables.html http://www.mysql.com/doc/e/x/example-user-variables.html JB Any help would be appreciated. thanks, jasmin. -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: User Variables and Previous Row Question
Hi, Jasmin Bertovic wrote: cut For example; SELECT number as current_day, (number - prevnumber) as change_from_prev_day FROM TABLE ORDER BY DATE prevnumber is the reference that I need from the previous row. Am I missing something simple or do I have to do this outside of MYSQL? 1. Yes you are missing something very basic (not simple). Tables in Relational Database Model are defined as Sets of Items. I.e. there is no Internal order of table. Server is free to store and retrieve rows in any order. ORDER BY clause is applied After rows are retrieved and processed (row by row) If you try to tranlsate your Query into Unordered Set terms it will sounds like: For each member of set do something with it and After that sort the result according to ... But since Set has no order you can not say For each member use Prev member 2. In your case there might be solution :) But it is very specific. Assuming date column is Primary Key you can just join table to it self SELECT t1.date_col, t1.num, t2.date_col, t2.num) FROM yourtable AS t1, yourtable AS t2 WHERE t1.date_col = t2.date_col + INTERVAL 1 DAY; Above is just for your info - to see what is happening. Having all these columns you can: SELECT t1.num as current, (t1.num - t2.num) as diff FROM yourtable AS t1, yourtable AS t2 WHERE t1.date_col = t2.date_col + INTERVAL 1 DAY; The only trick is to deal with 1st date because there is no previous, but I will let this excercise to you :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: User Variables and Previous Row Question
This makes sence, now that I have had some time to think about it. If I self join, I have to make sure that my data is continuous to shift all the rows by 1. Some of the things could have missing dates or maybe I could self join on an incremental counter using a tmp table. I have do go back to 'drawing board' and do some more thinking. This is great info. Thanks again. --- Alexander Keremidarski [EMAIL PROTECTED] wrote: Hi, Jasmin Bertovic wrote: cut For example; SELECT number as current_day, (number - prevnumber) as change_from_prev_day FROM TABLE ORDER BY DATE prevnumber is the reference that I need from the previous row. Am I missing something simple or do I have to do this outside of MYSQL? 1. Yes you are missing something very basic (not simple). Tables in Relational Database Model are defined as Sets of Items. I.e. there is no Internal order of table. Server is free to store and retrieve rows in any order. ORDER BY clause is applied After rows are retrieved and processed (row by row) If you try to tranlsate your Query into Unordered Set terms it will sounds like: For each member of set do something with it and After that sort the result according to ... But since Set has no order you can not say For each member use Prev member 2. In your case there might be solution :) But it is very specific. Assuming date column is Primary Key you can just join table to it self SELECT t1.date_col, t1.num, t2.date_col, t2.num) FROM yourtable AS t1, yourtable AS t2 WHERE t1.date_col = t2.date_col + INTERVAL 1 DAY; Above is just for your info - to see what is happening. Having all these columns you can: SELECT t1.num as current, (t1.num - t2.num) as diff FROM yourtable AS t1, yourtable AS t2 WHERE t1.date_col = t2.date_col + INTERVAL 1 DAY; The only trick is to deal with 1st date because there is no previous, but I will let this excercise to you :) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do You Yahoo!? Yahoo! Health - your guide to health and wellness http://health.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
User-variables weirdness... initialized or not?
You have this table: id data 3 2 2 1 1 3 This query: SELECT @var, data, @var:=data+(IFNULL(@var,0)) FROM table What would result would you expect the first time you execute the query? I would expect this: NULL, 2, 2 2, 1, 3 3, 3, 6 But this is what you get the first time: NULL, 2, 2 NULL, 1, 1 NULL, 3, 3 and next execution: 3, 2, 5 5, 1, 6 6, 3, 9 Can you explain this? Greetings from Denmark /Stig - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL - user variables
Perhaps I should have split this up into two mails, but I saw them related
in the difference of databases
On mysql Ver 11.12 Distrib 3.23.32, for pc-linux-gnu (i686)
this works
SELECT SQL_BUFFER_RESULT DATE_FORMAT(ORDERHEADER.AvailableAt,'%y/%m/%d') AS
'Date', (@tl:=SUM(IF(DISPATCHLOG.ID_STATUS = 30 AND ORDERLINE.Step =
1,1,0))) AS 'Total', (@wb:=SUM(IF(DISPATCHLOG.Details LIKE 'Web O/E%' AND
DISPATCHLOG.ID_STATUS = 30 AND ORDERLINE.Step = 1,1,0))) AS 'Web',
(@sys:=SUM(IF(DISPATCHLOG.Details = 'O/E by System' AND
DISPATCHLOG.ID_STATUS = 30 AND ORDERLINE.Step = 1,1,0))) AS 'System',
(@can:=(SUM(IF(DISPATCHLOG.ID_STATUS = 43 AND ORDERLINE.Step =
1,1,0))-SUM(IF(DISPATCHLOG.ID_STATUS = 73 AND ORDERLINE.Step = 1,1,0 AS
'Cancelled', (@tl-@wb-@sys-@can) AS 'Balance' FROM ORDERHEADER LEFT JOIN
ORDERLINE ON ORDERHEADER.ID = ORDERLINE.ID_ORDERHEADER LEFT JOIN DISPATCHLOG
ON ORDERLINE.ID = DISPATCHLOG.ID_ORDERLINE WHERE (ORDERHEADER.AvailableAt
BETWEEN '[param:Start Date]' AND '[param:End Date]') GROUP BY 'Date' ORDER
BY 'Date' DESC
but on mysql Ver 11.13 Distrib 3.23.36, for pc-linux-gnu (i686) it doesnt
calculate the balance column
--
SELECT @com:=SUM(PAYROLL.Amount) AS 'Commissions', '0.00' AS 'Other',
@com+'' AS 'Total', DATE_FORMAT('[param:StartDate]', '%y/%m/%d') AS
'StartDate', DATE_FORMAT('[param:EndDate]', '%y/%m/%d') AS 'EndDate' FROM
CONTRACTOR LEFT JOIN PAYROLL ON CONTRACTOR.ID = PAYROLL.ID_CONTRACTOR WHERE
Aka = '[param:Aka]' AND (PAYROLL.CreateStamp BETWEEN '[param:StartDate]' AND
DATE_FORMAT('[param:EndDate]', '%Y%m%d235959'))
This works fine on the older version(3.23.32), but on the newer(3.23.36), I
needed a GROUP BY Aka
SQLException: General error: Mixing of GROUP columns
(MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no
GROUP BY clause
==
SQLState: S1000
VendorError: 1140
--
I do not thing the data is relative to this question.
Note: about the use of [param:Variable], these select statements are stored
in the database, they are put through a custom parser that prompts for these
variables when the statement is used to generate forms
1. How could I get the first statement return a value for 'Balance' in the
newer version?
2. What is that SQL Exception telling me?
3. And out of curiosity, why is there now a requirement for the GROUP BY
when there wasnt before?
Luc Foisy
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL - user variables
Perhaps I should have split this up into two mails, but I saw them related
in the difference of databases
On mysql Ver 11.12 Distrib 3.23.32, for pc-linux-gnu (i686)
this works
SELECT SQL_BUFFER_RESULT DATE_FORMAT(ORDERHEADER.AvailableAt,'%y/%m/%d') AS
'Date', (@tl:=SUM(IF(DISPATCHLOG.ID_STATUS = 30 AND ORDERLINE.Step =
1,1,0))) AS 'Total', (@wb:=SUM(IF(DISPATCHLOG.Details LIKE 'Web O/E%' AND
DISPATCHLOG.ID_STATUS = 30 AND ORDERLINE.Step = 1,1,0))) AS 'Web',
(@sys:=SUM(IF(DISPATCHLOG.Details = 'O/E by System' AND
DISPATCHLOG.ID_STATUS = 30 AND ORDERLINE.Step = 1,1,0))) AS 'System',
(@can:=(SUM(IF(DISPATCHLOG.ID_STATUS = 43 AND ORDERLINE.Step =
1,1,0))-SUM(IF(DISPATCHLOG.ID_STATUS = 73 AND ORDERLINE.Step = 1,1,0 AS
'Cancelled', (@tl-@wb-@sys-@can) AS 'Balance' FROM ORDERHEADER LEFT JOIN
ORDERLINE ON ORDERHEADER.ID = ORDERLINE.ID_ORDERHEADER LEFT JOIN DISPATCHLOG
ON ORDERLINE.ID = DISPATCHLOG.ID_ORDERLINE WHERE (ORDERHEADER.AvailableAt
BETWEEN '[param:Start Date]' AND '[param:End Date]') GROUP BY 'Date' ORDER
BY 'Date' DESC
but on mysql Ver 11.13 Distrib 3.23.36, for pc-linux-gnu (i686) it doesnt
calculate the balance column
--
SELECT @com:=SUM(PAYROLL.Amount) AS 'Commissions', '0.00' AS 'Other',
@com+'' AS 'Total', DATE_FORMAT('[param:StartDate]', '%y/%m/%d') AS
'StartDate', DATE_FORMAT('[param:EndDate]', '%y/%m/%d') AS 'EndDate' FROM
CONTRACTOR LEFT JOIN PAYROLL ON CONTRACTOR.ID = PAYROLL.ID_CONTRACTOR WHERE
Aka = '[param:Aka]' AND (PAYROLL.CreateStamp BETWEEN '[param:StartDate]' AND
DATE_FORMAT('[param:EndDate]', '%Y%m%d235959'))
This works fine on the older version(3.23.32), but on the newer(3.23.36), I
needed a GROUP BY Aka
SQLException: General error: Mixing of GROUP columns
(MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no
GROUP BY clause
==
SQLState: S1000
VendorError: 1140
--
I do not thing the data is relative to this question.
Note: about the use of [param:Variable], these select statements are stored
in the database, they are put through a custom parser that prompts for these
variables when the statement is used to generate forms
1. How could I get the first statement return a value for 'Balance' in the
newer version?
2. What is that SQL Exception telling me?
3. And out of curiosity, why is there now a requirement for the GROUP BY
when there wasnt before?
Luc Foisy
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with UPDATE and User Variables
At 18:32 +0100 1/24/02, Peter Bremer wrote: Hi, Can anybody explain why the following does not work? SELECT @variable := value1; UPDATE table SET field1 = @variable WHERE field2 = value2; The result is always that field1 is set to NULL... Works for me, if I substitute a real value for value1. Your first statement as shown above isn't actually legal. How do you establish what value1 is? Regards, Peter Bremer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problem with UPDATE and User Variables
Ooops!! This seems to be a bug in EMS MySQL Manager... Sorry to bother you all... Regards Peter -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: do 24 jan 2002 18:51 To: Peter Bremer; Mysql (E-mail) Subject: Re: Problem with UPDATE and User Variables At 18:32 +0100 1/24/02, Peter Bremer wrote: Hi, Can anybody explain why the following does not work? SELECT @variable := value1; UPDATE table SET field1 = @variable WHERE field2 = value2; The result is always that field1 is set to NULL... Works for me, if I substitute a real value for value1. Your first statement as shown above isn't actually legal. How do you establish what value1 is? Regards, Peter Bremer - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
When were user variables introduced ?
Hi I'm trying to use user variables such as @myvar within a query which I've got running perfectly on a 3.23.35a server , however, i can't get the same query to run on 3.22.32 server :( Does anyone know if 3.22.32 supports user variables ? Thanks Girish - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: When were user variables introduced ?
Hi I'm trying to use user variables such as @myvar within a query which I've got running perfectly on a 3.23.35a server , however, i can't get the same query to run on 3.22.32 server :( Does anyone know if 3.22.32 supports user variables ? 3.23.6 was the first version to support UDVs. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Pb with user variables
By reading the online manual-split.tar, I try to do some examples but one doesn't work. (For this time, I didn't configure mysql). To (re)-product this error: CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer)); INSERT INTO shop VALUES (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69), (3,'D',1.25),(4,'D',19.95); my query was: select @min_price:=min(price),@max_price:=max(price) from shop; and the error was: ERROR 1064: You have an error in your SQL syntax near '@min_price:=min(price),@max_price:=max(price) from shop' at line 1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
user variables
Hello, My problem is about users variables. I want to do a script to create a database so: SET @dbname:=mydbname; CREATE DATABASE @dbname;# -- Don´t work CREATE DATABASE (SELECT @dbname);# -- Don´t Work I have tried to define @dbname=mydbname directly but it blocks. Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: user variables
At 3:16 AM +0200 6/13/01, Luis A. Fdez. de Retana Aguirre wrote: Hello, My problem is about users variables. I want to do a script to create a database so: SET @dbname:=mydbname; CREATE DATABASE @dbname;# -- Don´t work CREATE DATABASE (SELECT @dbname);# -- Don´t Work As the manual says, user-defined variables may be used wherever an expression is legal. However, expressions aren't legal database names, so what you're trying to do isn't legal. If you're trying to write a script, perhaps you can use variable substitution at the level of your scripting language, rather than at the SQL level. For example: #! /bin/sh DB_NAME=mydbname mysql EOF CREATE DATABASE $DB_NAME; EOF I have tried to define @dbname=mydbname directly but it blocks. Thanks -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Why can't I set user variables from a SHOW statement?
Basil Hussain writes: Hi all, Is there any reason why I can't do something like any of the following? SET @blah := SHOW VARIABLES LIKE 'server_id'; SHOW @blah := VARIABLES LIKE 'server_id'; It doesn't seem to work no matter which combinations of syntax I try. Is setting user variables only supported via the SET and SELECT statements? I think it's a shame that setting variables from SHOW results is missing. I think that this functionality should be added to MySQL as it could be handy in some places. (For those who are wondering, I'm attempting to come up with a neat solution to avoid using auto-increment columns with co-replication between two MySQL servers. My idea was to have the server itself come up with a unique ID consisting of something like the current timestamp plus a random number plus the server's ID, rolled up into a concise hash value. My PHP script would perform a query like above when it first connects and then uses the saved user-variable in all subsequent queries where a unique ID is required.) Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] Hi! You can set variables to expressions only. Setting them to queries will be possible when stored procedures are implemented in about one year time. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: User variables
Hi, Are there any issues with user variables ( SET @variable:=) overflowing memory on the server? At any one time I wouldn't need most of them, but I don't see any way of removing them once set. Might this be an issue, or is there some garbage collection and/or memory limit on the variables. Should I clear them after using them by setting them to NULL? No, you don't need to clean up your variables after use. To quote from the manual: All variables for a thread are automatically freed when the thread exits. So, when you close the connection to the MySQL server, your variables are unset automatically - but not for other connections, just your own (bear in mind that user variables set in one thread are not available server-wide). As for memory limits on variables, this may be one question for the developers to answer, is it mentions nothing about limits in the manual. Regards, Basil Hussain --- Internet Developer, Kodak Weddings E-Mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
User variables
Are there any issues with user variables ( SET @variable:=) overflowing memory on the server? I am considering using user variables to implement a simple psuedo stored procedure API whereby an API user can set variables for a query in one function, and call the query later on in another function. If I do this the way I am planning, the variable names will be controlled outside of the API and in theory a large number of unique names could build up. At any one time I wouldn't need most of them, but I don't see any way of removing them once set. Might this be an issue, or is there some garbage collection and/or memory limit on the variables. Should I clear them after using them by setting them to NULL? Thanks, eRic _ Get your FREE download of MSN Explorer at http://explorer.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT, LEFT JOIN, and user variables
Gary Shea writes:
cut
Sorry, this is my fault for not describing the problem better.
Here's the result I get with DISTINCT (I've given the columns
names to make the table smaller, but not changed the SQL from what
I sent in the bug report):
+---+
| i |
+---+
| 1 |
| 2 |
+---+
+---+-+-+-++
| i | vv1 | vv2 | vv3 | @vv1+@vv2+@vv3 |
+---+-+-+-++
| 1 | 1 | 0 | 1 | 1 |
| 2 | 1 | 0 | 0 | 1 |
+---+-+-+-++
Note that the sum is not correct: the last column values
should be 2 and 1, not 1 and 1. With DISTINCT, the sum
value is always correct for the very last row, and the
sum value for all other rows is the same as the
sum value in the last row!
Here's the result without DISTINCT:
+---+
| i |
+---+
| 1 |
| 2 |
+---+
+---+-+-+-++
| i | vv1 | vv2 | vv3 | @vv1+@vv2+@vv3 |
+---+-+-+-++
| 1 | 1 | 0 | 1 | 2 |
| 2 | 1 | 0 | 0 | 1 |
+---+-+-+-++
As you can see, the sums are now correct.
After I posted the bug report, I noticed that the same problem
occurs if I used ORDER BY. Probably a hint in there somewhere ;)
Sorry for the poor report!
Gary
Thank you for your report.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT, LEFT JOIN, and user variables
[EMAIL PROTECTED] writes:
Description:
It appears that if a column generated by a select is
a sum of variables generated in other columns of the
select (see example below!), the column will have a
bogus result if the select is 'DISTINCT'. If the
DISTINCT keyword is dropped, the column is generated
properly, even in a case where the output is no changed
by specifying DISTINCT.
How-To-Repeat:
CREATE TABLE source (
i int not null,
v int not null,
index (i)
);
insert into source values (1,1);
insert into source values (1,3);
insert into source values (2,1);
create table joinkeys (
i int not null,
unique (i)
);
insert into joinkeys
select
distinct
i
from source;
select * from joinkeys;
# This select is actually an "insert into X select" in the
# production code, but that part isn't necessary to see the
# problem.
select
distinct
joinkeys.i,
@vv1:=if(sv1.i,1,0),
@vv2:=if(sv2.i,1,0),
@vv3:=if(sv3.i,1,0),
@vv1+@vv2+@vv3
from
joinkeys
left join source as sv1 on sv1.i=joinkeys.i and sv1.v=1
left join source as sv2 on sv2.i=joinkeys.i and sv2.v=2
left join source as sv3 on sv3.i=joinkeys.i and sv3.v=3
;
Fix:
Sorry, I haven't a clue! In the SQL I'm attempting to
optimize (inherited code) I don't actually need the DISTINCT,
but had tried using it and noticed this problem.
Submitter-Id: submitter ID
Originator: Gary Shea
Organization:
GTS Design Consulting
MySQL support: licence
Synopsis: Problem with variable-generated SELECT column with DISTINCT
Severity:
Priority:
Category: mysql
Class: sw-bug
Release:mysql-3.23.33 (Source distribution)
Environment:
System: FreeBSD eggther.videoaxs.com 4.2-STABLE FreeBSD 4.2-STABLE #1: Tue Jan 30
14:13:24 GMT 2001 [EMAIL PROTECTED]:/usr/src/sys/compile/EGGTHER i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.2 19991024 (release)
Compilation info: CC='cc' CFLAGS='-O -pipe' CXX='c++' CXXFLAGS='-O -pipe
-felide-constructors -fno-rtti -fno-exceptions' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1169390 Jan 30 05:11 /usr/lib/libc.a
lrwxr-xr-x 1 root wheel 9 Jan 30 05:11 /usr/lib/libc.so - libc.so.4
-r--r--r-- 1 root wheel 559484 Jan 30 05:11 /usr/lib/libc.so.4
Configure command: ./configure --localstatedir=/var/db/mysql --without-perl
--without-debug --without-readline --without-bench --with-mit-threads=no
--with-libwrap --with-low-memory --enable-assembler --prefix=/usr/local
i386--freebsd4.2
Perl: This is perl, version 5.005_03 built for i386-freebsd
Hi!
This query worked perfectly for me.
I have got result set with 5 int columns.
Regards,
Sinisa
__ _ _ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|
/*/^^^\*\^^^
/*/ \*\Developers Team
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
user variables length
How many Bytes can I save in a user variable? Because I have the following strategy: I want to save all PrimaryKeys in a user variable SELECT @a:=' '; SELECT @a := CONCAT(@a, ', ', TPrimKey) FROM table WHERE condition; so it is very important to know how many bytes can be stored. regards Philipp Hasenfratz
