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]
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]
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]
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]
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]
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]
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]
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]
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]
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]
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
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
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: 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
