LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 07-22-2008, 06:44 PM   #1
farmerjoe
Member
 
Registered: Oct 2004
Location: Texas
Distribution: Ubuntu - Home, RHEL4 - Server
Posts: 96

Rep: Reputation: 15
Question Help! SQL User Variable doesn't store decimals when Variables are Reset


I am currently working on a query that makes use of user variables. I notice one small issue (bug?) depending on whether or not I reset a user variable with the "SET" command or don't before executing the query.

When I do reset the user variables, some of my variables will not hold decimal points (7.56734234 gets turned into and stored as 7). However when I run the query afterwards without resetting the user variables, the correct value (with the decimal) is stored.

Example with query:

First Run:
Code:
set @signal = 0,
@roi = 0,
@counter = 0,
@gain_loss = 0,
@prev_symbol = 0;

SELECT date, price, ma_pe_increases, ma_pe_decreases,
@roi := if(@counter = 3, if((price / @signal) > 1.50, 1.50, price / @signal), 0) as roi,
@signal := if(ma_pe_increases >= 6 AND ma_pe_decreases = 2, @signal := price, if(@counter > 0 AND @counter < 3 AND @prev_symbol = symbol, @signal := @signal, 0)) as signal,
@counter := if(@signal != 0, if(@counter = 0, 1, @counter + 1), 0) as counter,
@gain_loss := if(@roi != 0, if(@roi > 1, 1, 0), 0) as gain_loss,
@prev_symbol := symbol as prev_symbol
FROM monthly_data_6monthavg
LIMIT 240\G
First Run Results:

mysql> set @signal = 0,
-> @roi = 0,
-> @counter = 0,
-> @gain_loss = 0,
-> @prev_symbol = 0;
Query OK, 0 rows affected (0.00 sec)

...
*************************** 229. row ***************************
date: 2007-01-01
price: 7.06
ma_pe_increases: 6
ma_pe_decreases: 2
roi: 0
signal: 7.0599999427795
counter: 1
gain_loss: 0
prev_symbol: FLWS
*************************** 230. row ***************************
date: 2007-02-01
price: 7.31
ma_pe_increases: 6
ma_pe_decreases: 3
roi: 0
signal: 7
counter: 2
gain_loss: 0
prev_symbol: FLWS
*************************** 231. row ***************************
date: 2007-03-01
price: 7.78
ma_pe_increases: 6
ma_pe_decreases: 4
roi: 0
signal: 7
counter: 3
gain_loss: 0
prev_symbol: FLWS
...
Signal should equal 7.0599999427795 in all rows I listed, but instead it gets converted to 7 when stored in a user variable.


Second Run (without resetting user variables):
Code:
SELECT date, price, ma_pe_increases, ma_pe_decreases,
@roi := if(@counter = 3, if((price / @signal) > 1.50, 1.50, price / @signal), 0) as roi,
@signal := if(ma_pe_increases >= 6 AND ma_pe_decreases = 2, @signal := price, if(@counter > 0 AND @counter < 3 AND @prev_symbol = symbol, @signal := @signal, 0)) as signal,
@counter := if(@signal != 0, if(@counter = 0, 1, @counter + 1), 0) as counter,
@gain_loss := if(@roi != 0, if(@roi > 1, 1, 0), 0) as gain_loss,
@prev_symbol := symbol as prev_symbol
FROM monthly_data_6monthavg
LIMIT 240\G
Second Run Results:

*************************** 229. row ***************************
date: 2007-01-01
price: 7.06
ma_pe_increases: 6
ma_pe_decreases: 2
roi: 0
signal: 7.0599999427795
counter: 1
gain_loss: 0
prev_symbol: FLWS
*************************** 230. row ***************************
date: 2007-02-01
price: 7.31
ma_pe_increases: 6
ma_pe_decreases: 3
roi: 0
signal: 7.0599999427795
counter: 2
gain_loss: 0
prev_symbol: FLWS
*************************** 231. row ***************************
date: 2007-03-01
price: 7.78
ma_pe_increases: 6
ma_pe_decreases: 4
roi: 0
signal: 7.0599999427795
counter: 3
gain_loss: 0
prev_symbol: FLWS

...

Notice that the correct value of 7.0599999427795 each time, even when passed through a user variable when the user variables ARE NOT reset prior to running the main query.

Does anyone know why this is and what I can do to fix this?
 
Old 07-22-2008, 07:45 PM   #2
farmerjoe
Member
 
Registered: Oct 2004
Location: Texas
Distribution: Ubuntu - Home, RHEL4 - Server
Posts: 96

Original Poster
Rep: Reputation: 15
I figured out what it was from the MySQL documentation (yes I know I am a moron):

"Another issue with setting a variable and using it in the same statement is that the default result type of a variable is based on the type of the variable at the start of the statement...To avoid problems with this behavior, either do not set and use the same variable within a single statement, or else set the variable to 0, 0.0, or '' to define its type before you use it."

I simply changed my SET statement to set the initial values from 0 to 0.0.
 
  


Reply

Tags
mysql, query, sql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
permanently store PATH variables hiteshthappa Linux - Newbie 4 07-15-2008 08:59 PM
Permanently store PATH variables hiteshthappa Linux - Newbie 2 07-15-2008 03:09 AM
Bash store last line from displayed text output in a variable carl0ski Programming 1 01-16-2007 03:38 AM
Best practices on making an environmental variable to store email addresses for each adseligman Linux - General 1 12-27-2004 04:53 AM
Store PATH variable permanently Skute Linux - Software 8 01-15-2004 04:05 PM


All times are GMT -5. The time now is 05:14 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration