MySQL Forums
Forum List  »  Oracle

Re: SELECT..INTO...NO_DATA_FOUND and TOO_MANY_ROWS
Posted by: B. Campbell
Date: October 23, 2008 11:29PM

Perhaps this will help...

http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html

MySQL doesn't support the "SELECT * INTO tblA FROM tblB" syntax.

Instead the syntax is

INSERT INTO tblB (fld1)
SELECT tblA.fld1
FROM tblA WHERE tblA.fld1 > 100;

It looks like you've got a stored procedure which you might treat like this:

DELIMITER $$ <-- change overall delimiter so you can use ; within the sproc

DROP PROCEDURE IF EXISTS FetchAuthors$$

CREATE PROCEDURE FetchAuthors
(
param1 paramtype1, <-- if you're using params for the sproc
_TooManyRows int <-- as an example you could set your # for Too many rows here
)
BEGIN
DECLARE _MaxRows int; <-- if vars needed for using within sproc (like max rows?)

IF (SELECT COUNT(author_last_name) FROM authors
WHERE author_state = v_auth_state) > 0 THEN
BEGIN;<-- BEGIN & END required when more than one SQL statement for IF...
INSERT INTO v_authName(author_last_name)
SELECT author_last_name
FROM authors
WHERE author_state = v_auth_state;

// work with v_authname <-- more SQL statements here

END;
ELSE
BEGIN
-- no data --> When NO_DATA_FOUND
-- // do something
END;
END IF;

IF (SELECT COUNT(author_last_name) FROM authors
WHERE author_state = v_auth_state) > _MaxRows THEN
BEGIN;
-- When TOO_MANY_ROWS then
-- // do something
END;
END IF;

END$$
DELIMITER ; <-- change delimiter back
GRANT EXECUTE ON PROCEDURE FetchAuthor TO someuser; <-- grant perms

Of course anything to the right of the "<--" above is a comment. And I've probably put in a bunch of overkill comments, but included them just in case they might be helpful.

Generally I use a SELECT COUNT(col) FROM tbl WHERE colA = _somevalue sort of IF ELSE statement to separate out the various cases.

If you weren't looking at a sproc, then maybe something like a CASE statement is needed in the INSERT statement...

INSERT INTO v_authName(author_last_name)
SELECT CASE a.author_last_name
WHEN (SELECT COUNT(*) FROM authors a1 WHERE WHERE a1.author_state = a1.v_auth_state)> 0 THEN 'abc'
WHEN (SELECT COUNT(*) FROM authors a2 WHERE a2.author_state = a2.v_auth_state)> 0 THEN 'xyz'
END
FROM authors a
WHERE a.author_state = a.v_auth_state;

I haven't tried any of these and am going off the top of my head, but hopefully they provide enough to get you rolling (in case you hadn't already dispatched the task!). There are probably other ways of getting to what you need as well...

Apologies if I've misread your question.

HTH

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: SELECT..INTO...NO_DATA_FOUND and TOO_MANY_ROWS
7990
October 23, 2008 11:29PM


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.