Hello all.

I have several stocked functions in my database. All the functions
have been tested individually and all work correctly.

I want to create a view which uses the functions. Here is the code of
the view :
<<
CREATE VIEW systemplus.V_ACTIONS
(
ROWID,
NUMLOCAL,
NUMTELLOCAL,
NUMTELQUIRAPPELLE,
RAPPELDATEHEURE,
RESETTYPE,
RESETDATEHEURE,
MOTIF,
MOTIFDATEHEURE,
DATEHEURE,
MODULECODE,
MODULEZONE,
DUREERAPPEL,
DUREERESET
)
AS
SELECT
events_backups.id,
fct_FindRoomNumberFromRoomId (room_id),
fct_FindRoomPhoneNumber (room_id),
events_backups.event_data,
events_backups.time,
fct_FindResetType (time, room_id),
fct_FindResetDateHeure (time, room_id),
fct_FindMotif (time, room_id),
fct_FindMotifDateHeure (time, room_id),
fct_FindTimeAppelModule (time, room_id),
modules.code,
modules.zone,
fct_CalculDureeRappel (time, room_id),
fct_CalculDureeReset (time, room_id)
FROM events_backups, rooms, modules
WHERE event like 'ROOM-CALLED'
AND events_backups.room_id = rooms.id
AND rooms.module_id = modules.id;
>>

However, when I try <SELECT * FROM V_ACTIONS;> I get the following
error message :
<ERROR 1172 (42000): Result consisted of more than one row>

I did some research, and found this is because the functions contain
SELECT ... INTO ... statements, which return the error message when a
query returns more than a single row, which is the case with the
current view condition.
However, I need this view to work with multiple rows.

Does anyone have an idea as to how I can do this ?

I though about using another function with a cursor, but this would
really not be ideal. If possible, I would like to be able to only use
a view, so as to be able to chose which columns I would display.

Thanks for the help.

Mad Ant

Mad Ant wrote:
> Hello all.
>
> I have several stocked functions in my database. All the functions
> have been tested individually and all work correctly.
>
> I want to create a view which uses the functions. Here is the code of
> the view :
> <<
> CREATE VIEW systemplus.V_ACTIONS
> (
> ROWID,
> NUMLOCAL,
> NUMTELLOCAL,
> NUMTELQUIRAPPELLE,
> RAPPELDATEHEURE,
> RESETTYPE,
> RESETDATEHEURE,
> MOTIF,
> MOTIFDATEHEURE,
> DATEHEURE,
> MODULECODE,
> MODULEZONE,
> DUREERAPPEL,
> DUREERESET
> )
> AS
> SELECT
> events_backups.id,
> fct_FindRoomNumberFromRoomId (room_id),
> fct_FindRoomPhoneNumber (room_id),
> events_backups.event_data,
> events_backups.time,
> fct_FindResetType (time, room_id),
> fct_FindResetDateHeure (time, room_id),
> fct_FindMotif (time, room_id),
> fct_FindMotifDateHeure (time, room_id),
> fct_FindTimeAppelModule (time, room_id),
> modules.code,
> modules.zone,
> fct_CalculDureeRappel (time, room_id),
> fct_CalculDureeReset (time, room_id)
> FROM events_backups, rooms, modules
> WHERE event like 'ROOM-CALLED'
> AND events_backups.room_id = rooms.id
> AND rooms.module_id = modules.id;
>
> However, when I try <SELECT * FROM V_ACTIONS;> I get the following
> error message :
> <ERROR 1172 (42000): Result consisted of more than one row>
>
> I did some research, and found this is because the functions contain
> SELECT ... INTO ... statements, which return the error message when a
> query returns more than a single row, which is the case with the
> current view condition.
> However, I need this view to work with multiple rows.
>
> Does anyone have an idea as to how I can do this ?
>
> I though about using another function with a cursor, but this would
> really not be ideal. If possible, I would like to be able to only use
> a view, so as to be able to chose which columns I would display.
>
> Thanks for the help.
>
> Mad Ant
>

i think what is happening is that one of the functions is returning more
than one row of data to the view which becomes problematic for the view
code.

--
lark -- EMAIL REMOVED
To reply to me directly, delete "despam".

Just a thought :
If I use a function which creates a table, fetches the appropriate
data and inserts it into the table, and then create a view which will
simply fetch the data from that table instead, is this a feasable
solution ?

Mad Ant

<
i think what is happening is that one of the functions is returning
more
than one row of data to the view which becomes problematic for the
view
code.

--
lark -- ham...@sbcdeglobalspam.net
To reply to me directly, delete "despam".
>

As far as I can gather, it's more a case of the WHERE condition
returning several lines of results, because the view works fine when I
ad an addition line to only have one single result returned from the
WHERE condition.
However, if I generalize the WHERE, to have several lines returned, it
gives the error message stated above.
The behaviour must be that the WHERE gets several lines as a result
set, and calls <function_1(result1, result2, result3, ...),
function_2(result1, result2, result3, ...), ...> instead of calling
<function _1(result1), function_2(result1), function_3(result1);
function _1(result2), function_2(result2), function_3(result2); ...>
I would like to have the second behaviour, but it seems that it is the
first behaviour that appears.
If anyone has an idea as to HOW I can get the latter behaviour, the
help is much appreciated.

Mad Ant

My mistake, the problem was with the functions and not the view.