Solved

MySQL Function with WHILE loop not working

Posted on 2011-09-13
6
546 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:John_2357
  • 4
  • 2
6 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36533819
Hi.

It appears -- without looking deeper at the code -- your error is in the assignment of variable d.
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, fk_tdate into f, d 
    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 n = n - 1;
end while;
return d;
end

Open in new window


If you are only interested in fk_tdate, would it not also work to simply do this:
select min(fk_tdate) into d 
    from prices 
    where fk_tdate between dfrom and dto
        and fk_curr = 'USD'
        and fk_tfid = 1
        and fk_sym = dsym
        and low < dval;

Open in new window

0
 
LVL 1

Accepted Solution

by:
John_2357 earned 500 total points
ID: 36535471
mwvisa1:

Thank you for the reply, either one works. I do have a couple questions, if you have the time.

1) select 1, fk_tdate into f, d  -- am I correct in assuming that "into" is assigning the string on the left into the variables on the right?

2) Am I correct in assuming that using your 2nd variation (no while loop) would be faster?

3) Is there a way I can write a while loop in mysql and have mysql print out the values of each variable as it loops through the while loop? Something similiar to:
while $i>0 do
i = i - 1
print i
end while;

Again thank you for your speedy reply and help.

john
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36536348
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
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:John_2357
ID: 36540308
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
0
 
LVL 1

Author Comment

by:John_2357
ID: 36540304
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
0
 
LVL 1

Author Comment

by:John_2357
ID: 36540309
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now