Finding MAX of PL/SQL table element
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Finding MAX of PL/SQL table element

  1. #1
    Join Date
    Dec 2000
    Posts
    28

    Question

    Hi,

    Is there a way to get Maximum/Minimum value in a PL/SQL table ? For instance to get the number of records in a table called "mytable" I can use mytable.COUNT

    There is a round about way to sort the table in ASCending order and I can get the last record. But any ideas other than this...would be greatly appreciated ...

    Thanks,
    AD


  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    What do you mean by "last record"? The one with highest index value? If so, you can use mytable.LAST.

    If you mean sorted by some PL/SQL table's field then no, there is no such magical way. You have to read all the records and find the highest value in that field programaticaly.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Apr 2001
    Posts
    118
    Couldn't you query for the MAX value of the PL/SQL table by casting it into a table using the TABLE and CAST operators? Something like


        select MAX(<X.field>)
        from TABLE(CAST(<PL/SQL table> as <PL/SQL table type>)) AS X
        group by <whatever>;


    Heath

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Correct me if I'm wrong, but you can only use CAST() on colection types that are stored in the database (ie nested tables and varrays), not on index-by tables thet exists only inside PL/SQL block.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Apr 2001
    Posts
    118
    I've only successfully used it on tables of object types (a collection, not an index=by table). The type must be defined previously in the database (i.e. create type <whatever> as table of <object type>). Any instances of these types declared and used in the PL/SQL block can be CAST into tables.

    I have been trying to use object types as much as possible because it seems to me that Oracle is nudging us in that direction. That is, the newer cooler stuff that they add (like CAST and TABLE operators) only work on the collection data types and then only work well if the collection is of an object type.

    I think we're in agreement. :)

    Heath

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Heath
    I think we're in agreement.

    Heath
    Yep
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width