List:MySQL on Win32« Previous MessageNext Message »
From:John Bonnett Date:June 30 2008 2:28am
Subject:RE: Offtopic help
View as plain text  
Sounds like a homework question to me, but maybe not.

Here are some tips. I think you will need to use a cursor to do
something like this. Read about them in the MySQL manual.

Here is an example from the manual: -

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur1;
  OPEN cur2;

  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
  CLOSE cur2;
END

The example is using two cursors where you need only one. Your procedure
will need two parameters unlike this one which has none. Instead of the
INSERTs inside the repeat loop you will be concatenating strings as you
process each row.

Any other details you need should be in the manual.

______________

Carl Zeiss Vision
Software Engineer
Asia Pacific

John Bonnett

19 Cooroora Crescent
Lonsdale SA 5160
Australia

Phone: +61 (0) 8 8392 8366
Fax: +61 (0) 8 8392 8400
Email: john.bonnett@stripped
Web: www.vision.zeiss.com

This email, including any attachments, is intended for the sole use of
the intended recipient(s) and may contain 
information that is confidential, privileged and/or the sender's work.
Any review or distribution by any other person is 
prohibited. If you are not an intended recipient, please immediately
contact the sender and delete all copies.



-----Original Message-----
From: Jorge Bastos [mailto:mysql.jorge@stripped] 
Sent: Saturday, 28 June 2008 10:28 PM
To: win32@stripped
Subject: Offtopic help

Hi people,

I wounder if anyone can give me a hand.

I want to create a view or function/procedure that returns a string, and
inside that function I have to pass two arguments, and do a SQL
statement like this:

 

Select * from tbl where field1=art1 and field2=arg2;

Result=concat(field3 ,';')         <= for each record found I need to
concatenate the result, and return it on the end.

 

How can this be possible?

Thread
Offtopic helpJorge Bastos28 Jun
  • RE: Offtopic helpLeif Johnston28 Jun
    • RE: Offtopic helpJorge Bastos28 Jun
  • RE: Offtopic helpJohn Bonnett30 Jun
  • Re: Offtopic helpRandy Clamons30 Jun
    • RE: Offtopic helpJorge Bastos30 Jun
  • Re: group_concat (was: Offtopic help)Markus Grossrieder1 Jul