John_2357
asked on
MySQL Function with WHILE loop not working
I am trying to create a function that will accept two dates and some other criteria. Then I need to increment the date "dfrom" then test if a condition is true if so then it should return the date that the condition becomes true.
If the condition is not true then the date which is returned is the default.
Here is the function:
CREATE function stop_hit_date_d ( dfrom date, dto date, dsym varchar(10), dval DECIMAL(10,4) ) returns DATE
READS SQL DATA
begin
declare d DATE;
declare x INT;
declare f INT;
declare n INT;
set d = '1111-11-11';
set x = 1;
set f = 0;
set n = DATEDIFF(dto,dfrom);
while f = 0 OR n > 0 DO
select 1 into f
from prices
where fk_tdate = ADDDATE(dfrom, INTERVAL x DAY)
and fk_curr = 'USD'
and fk_tfid = 1
and fk_sym = dsym
and low < dval
limit 1;
set x = x + 1;
set d = fk_tdate;
set n = n - 1;
end while;
return d;
end
When I run this query (I get the following error):
mysql> SELECT stop_hit_date_d ( '2011-08-23', '2011-09-09', 'AEP', 36.76);
ERROR 1054 (42S22): Unknown column 'fk_tdate' in 'field list'
In the above query I want to increment '2011-08-23' by 1 day, then see if the low price in the 'prices' table for symbol 'AEP' is below 36.76 if so then the function returns that incremented data, but if not the while loop adds another day to the dfrom date and re-test, if the dto date is hit and the condition is never true then the date which is returned is '1111-11-11'
Any help is apprecaited.
If the condition is not true then the date which is returned is the default.
Here is the function:
CREATE function stop_hit_date_d ( dfrom date, dto date, dsym varchar(10), dval DECIMAL(10,4) ) returns DATE
READS SQL DATA
begin
declare d DATE;
declare x INT;
declare f INT;
declare n INT;
set d = '1111-11-11';
set x = 1;
set f = 0;
set n = DATEDIFF(dto,dfrom);
while f = 0 OR n > 0 DO
select 1 into f
from prices
where fk_tdate = ADDDATE(dfrom, INTERVAL x DAY)
and fk_curr = 'USD'
and fk_tfid = 1
and fk_sym = dsym
and low < dval
limit 1;
set x = x + 1;
set d = fk_tdate;
set n = n - 1;
end while;
return d;
end
When I run this query (I get the following error):
mysql> SELECT stop_hit_date_d ( '2011-08-23', '2011-09-09', 'AEP', 36.76);
ERROR 1054 (42S22): Unknown column 'fk_tdate' in 'field list'
In the above query I want to increment '2011-08-23' by 1 day, then see if the low price in the 'prices' table for symbol 'AEP' is below 36.76 if so then the function returns that incremented data, but if not the while loop adds another day to the dfrom date and re-test, if the dto date is hit and the condition is never true then the date which is returned is '1111-11-11'
Any help is apprecaited.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are very welcome!
1) Yes, it assigns whatever values -- literal or from column -- to variables on the right based on the order, i.e., variables need to be listed in same order as column values.
2) That is the theory. It is a set based approached versus row-by-row analysis. The first is acting like a cursor.
3) I do not believe so. You can emulate it, by concatenating the values then doing a SELECT after the loop is done. No PRINT like T-SQL, though. At least none when I last checked.
Kevin
1) Yes, it assigns whatever values -- literal or from column -- to variables on the right based on the order, i.e., variables need to be listed in same order as column values.
2) That is the theory. It is a set based approached versus row-by-row analysis. The first is acting like a cursor.
3) I do not believe so. You can emulate it, by concatenating the values then doing a SELECT after the loop is done. No PRINT like T-SQL, though. At least none when I last checked.
Kevin
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for John_2357's comment http:/Q_27306818.html#36535471
for the following reason:
Thank you
Accepted answer: 0 points for John_2357's comment http:/Q_27306818.html#36535471
for the following reason:
Thank you
ASKER
I did not close or accept the solution until right now 18:15PST - I want to award the 500 points to "mwvisa1:" his solution was correct. I also do not want to make my question and its solution available to the public.
Thank you for fixing this.
John
Thank you for fixing this.
John
ASKER
I did not close or accept the solution until right now 18:15PST - I want to award the 500 points to "mwvisa1:" his solution was correct. I also do not want to make my question and its solution available to the public.
Thank you for fixing this.
John
Thank you for fixing this.
John
It appears -- without looking deeper at the code -- your error is in the assignment of variable d.
Open in new window
If you are only interested in fk_tdate, would it not also work to simply do this:
Open in new window