MySQL Forums :: Stored Procedures :: Error " #1241 - Operand should contain 1 column(s)" when calling a function


Advanced Search

Re: Error " #1241 - Operand should contain 1 column(s)" when calling a function
Posted by: Jay Alverson ()
Date: August 24, 2009 11:27AM

That's one complex function.

You might try copying it into a PROCEDURE and then put SELECT statements
every so often so that you can debug it from top to bottom.

Then call the procedure and see if you can isolate the problem. Once you
get a chunk of code secure, just comment out the SELECT and move to the
next block.

That's usually what I do with complex functions; that way I can see how far
a variable or piece of code gets. I also often create temp tables so that
I can monitor the progress of the executing code:

mysql> 
mysql> use test;
Database changed
mysql> 
mysql> DELIMITER $$
mysql> 
mysql> DROP PROCEDURE IF EXISTS `test`.`GS` $$
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE PROCEDURE `test`.`GS` (str VARCHAR(500), delim VARCHAR(10))
    -> BEGIN
    ->   DECLARE pos SMALLINT;
    ->   DECLARE dastr VARCHAR(100);
    ->   DECLARE daCount SMALLINT;
    -> 
    ->   SET daCount = 0;
    -> 
    -> 
    -> 
    ->   drop table if exists test_getstring;
    ->   create table test_getstring (pos smallint, dastr varchar(500), str varchar(500), dapos smallint);
    -> 
    ->   label1: LOOP
    ->     SET pos = instr(str, delim);
    ->     SET dastr = SUBSTRING_INDEX(str, delim, 1);
    ->     IF pos > 0 THEN
    ->       SET daCount = daCount + 1;
    ->       SET str = mid(str, pos+1, CHAR_LENGTH(str));
    ->       ELSE LEAVE label1;
    ->     END IF;
    -> 
    ->     insert into test_getstring values (pos, dastr, str, dapos);
    -> 
    -> 
    ->     if daCount > 10000 then LEAVE label1; end if;
    ->   END LOOP label1;
    ->   SET daCount = daCount + 1;
    -> 
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> DELIMITER ;
mysql> 
mysql> call GS("One;Two;Three;Four;Five;Six;Seven", ";");
Query OK, 1 row affected (0.08 sec)

mysql> 
mysql> select * from test_getstring;
+------+-------+-------------------------------+-------+
| pos  | dastr | str                           | dapos |
+------+-------+-------------------------------+-------+
|    4 | One   | Two;Three;Four;Five;Six;Seven |  NULL | 
|    4 | Two   | Three;Four;Five;Six;Seven     |  NULL | 
|    6 | Three | Four;Five;Six;Seven           |  NULL | 
|    5 | Four  | Five;Six;Seven                |  NULL | 
|    5 | Five  | Six;Seven                     |  NULL | 
|    4 | Six   | Seven                         |  NULL | 
+------+-------+-------------------------------+-------+
6 rows in set (0.00 sec)

mysql> 
mysql> notee

>

Thanks, Jay

Options: ReplyQuote


Subject Views Written By Posted
Error " #1241 - Operand should contain 1 column(s)" when calling a function 3684 Geraint Torrington 08/24/2009 04:06AM
Re: Error " #1241 - Operand should contain 1 column(s)" when calling a function 2700 Jay Alverson 08/24/2009 11:27AM
Re: Error " #1241 - Operand should contain 1 column(s)" when calling a function 1896 Geraint Torrington 08/26/2009 09:37AM
Re: Error " #1241 - Operand should contain 1 column(s)" when calling a function 1464 Jay Alverson 08/26/2009 10:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.