Professional Web Applications Themes

Error 1172 with stocked functions in view - MySQL

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 ...

Sponsored Links
  1. #1

    Default Error 1172 with stocked functions in view

    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; [/ref]

    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

    Sponsored Links
    Mad Guest

  2. #2

    Default Re: Error 1172 with stocked functions in view

    Mad Ant wrote: 

    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 -- net
    To reply to me directly, delete "despam".
    lark Guest

  3. #3

    Default Re: Error 1172 with stocked functions in view

    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

    Mad Guest

  4. #4

    Default Re: Error 1172 with stocked functions in view

    <
    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 -- 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

    Mad Guest

  5. #5

    Default Re: Error 1172 with stocked functions in view

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

    Mad Guest

Similar Threads

  1. #46142 [NEW]: Error in ID3 functions
    By kp2356 in forum PHP Bugs
    Replies: 2
    Last Post: September 21st, 08:27 AM
  2. [PHP] Error when using built in functions
    By Raditha Dissanayake in forum PHP Development
    Replies: 0
    Last Post: September 7th, 06:17 AM
  3. View functions I'd like to see [WISH]
    By dave milbut in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 3
    Last Post: August 9th, 04:37 PM
  4. Wierd error when going to Design View from HTML view
    By VB Programmer in forum ASP.NET General
    Replies: 1
    Last Post: July 10th, 03:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139