Query from Collection Table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Query from Collection Table

  1. #1
    Join Date
    Jan 2002
    Posts
    148

    Unhappy Query from Collection Table

    Hi there,

    I require help here...

    declare
    TYPE BLK_dt IS TABLE OF DATE;
    DT_VAR BLK_DT;
    t date;
    begin

    select createdate bulk collect into DT_var from busrep;

    < NOW I NEED TO GET MAX() from DT_VAR table
    How can I get it ??? >

    end;
    /


    Thankyou
    Jr

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,479
    Do this:

    declare
    TYPE BLK_dt IS TABLE OF DATE;
    DT_VAR BLK_DT;
    t date;
    i pls_integer;
    begin

    select createdate bulk collect into DT_var from busrep;

    t := to_date('01-JAN-1900');
    for i in 1..DT_var.count
    loop
    t := GREATEST(t,DT_VAR(i));
    end loop;

    end;
    /

    If you need the index also, then add:

    max_i pls_integer;

    and replace the 'for' with:

    for i in 1..DT_var.count
    loop
    if DT_VAR(i)) > t then
    max_i := i;
    t := DT_var;
    end if;
    end loop;
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Jan 2002
    Posts
    148
    Thanks for the response,
    but was wondering if anything possible without LOOP construct.

    Jr.

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