-
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
-
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?
-
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
-
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?
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|