Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    18

    Unanswered: calling a function

    Hi guys,

    I have this function that is giving me prior college 1,2,3 etc
    i tried calling in directly like this F_CONCAT_SORPCOL
    (id number)
    and i got some numbers with sign how do i call this function. eg

    i tried select F_CONCAT_SORPCOL(id number) but still got what i did the first time




    create or replace FUNCTION "F_CONCAT_SORPCOL"
    (id number)
    return varchar2
    as

    delim varchar2(1) := '{';
    ctr number := 0;
    seq_no number := 0;
    sbgi_code varchar2(6) := null;
    trans_recv_date varchar2(11) := null;
    trans_rev_date varchar2(11) := null;
    majr_code_major varchar2(4) := null;
    degc_code varchar2(6) := null;
    /* 6.0 Remove specific size references. */
    gpa_transferred sordegr.sordegr_gpa_transferred%TYPE := null;
    attend_from varchar2(11) := null;
    attend_to varchar2(11) := null;
    sbgi_code1 varchar2(6) := null;
    trans_recv_date1 varchar2(11) := null;
    trans_rev_date1 varchar2(11) := null;
    majr_code_major1 varchar2(4) := null;
    degc_code1 varchar2(6) := null;
    /* 6.0 Remove specific size references. */
    gpa_transferred1 sordegr.sordegr_gpa_transferred%TYPE := null;
    attend_from1 varchar2(11) := null;
    attend_to1 varchar2(11) := null;
    sbgi_code2 varchar2(6) := null;
    trans_recv_date2 varchar2(11) := null;
    trans_rev_date2 varchar2(11) := null;
    majr_code_major2 varchar2(4) := null;
    degc_code2 varchar2(6) := null;
    /* 6.0 Remove specific size references. */
    gpa_transferred2 sordegr.sordegr_gpa_transferred%TYPE := null;
    attend_from2 varchar2(11) := null;
    attend_to2 varchar2(11) := null;
    sbgi_code3 varchar2(6) := null;
    trans_recv_date3 varchar2(11) := null;
    trans_rev_date3 varchar2(11) := null;
    majr_code_major3 varchar2(4) := null;
    degc_code3 varchar2(6) := null;
    /* 6.0 Remove specific size references. */
    gpa_transferred3 sordegr.sordegr_gpa_transferred%TYPE := null;
    attend_from3 varchar2(11) := null;
    attend_to3 varchar2(11) := null;
    return_field varchar2(234) := null;
    cursor get_sorpcol is
    select sorpcol_sbgi_code,
    to_char(sorpcol_trans_recv_date,'DD-MON-YYYY'),
    to_char(sorpcol_trans_rev_date,'DD-MON-YYYY')
    from sorpcol
    where sorpcol_pidm = pidm
    order by sorpcol_trans_recv_date desc;
    cursor get_sordegr is
    select sordegr_degc_code, sordegr_degr_seq_no,
    sordegr_gpa_transferred,
    to_char(sordegr_attend_from,'DD-MON-YYYY'),
    to_char(sordegr_attend_to,'DD-MON-YYYY')
    from sordegr
    where sordegr_pidm = pidm
    and sordegr_sbgi_code = sbgi_code
    order by sordegr_degr_seq_no asc, sordegr_attend_from desc;
    cursor get_sormajr_no_degr is
    select sormajr_majr_code_major
    from sormajr
    where sormajr_pidm = pidm
    and sormajr_sbgi_code = sbgi_code;
    cursor get_sormajr_degr is
    select sormajr_majr_code_major
    from sormajr
    where sormajr_pidm = pidm
    and sormajr_sbgi_code = sbgi_code
    and sormajr_degc_code = degc_code
    and sormajr_degr_seq_no = seq_no
    order by sormajr_degr_seq_no;
    begin
    ctr := 0;
    open get_sorpcol;
    loop
    fetch get_sorpcol into sbgi_code, trans_recv_date, trans_rev_date;
    exit when get_sorpcol%notfound;
    ctr := ctr + 1;
    if ctr > 3 then
    exit;
    end if;
    degc_code := null;
    gpa_transferred := null;
    attend_from := null;
    attend_to := null;
    majr_code_major := null;
    open get_sordegr;
    fetch get_sordegr into degc_code, seq_no,
    gpa_transferred, attend_from, attend_to;
    if get_sordegr%found then
    open get_sormajr_degr;
    fetch get_sormajr_degr into majr_code_major;
    close get_sormajr_degr;
    else
    open get_sormajr_no_degr;
    fetch get_sormajr_no_degr into majr_code_major;
    close get_sormajr_no_degr;
    end if;
    close get_sordegr;
    if ctr = 1 then
    sbgi_code1 := sbgi_code;
    trans_recv_date1 := trans_recv_date;
    trans_rev_date1 := trans_rev_date;
    degc_code1 := degc_code;
    gpa_transferred1 := gpa_transferred;
    attend_from1 := attend_from;
    attend_to1 := attend_to;
    majr_code_major1 := majr_code_major;
    elsif ctr = 2 then
    sbgi_code2 := sbgi_code;
    trans_recv_date2 := trans_recv_date;
    trans_rev_date2 := trans_rev_date;
    degc_code2 := degc_code;
    gpa_transferred2 := gpa_transferred;
    attend_from2 := attend_from;
    attend_to2 := attend_to;
    majr_code_major2 := majr_code_major;
    elsif ctr = 3 then
    sbgi_code3 := sbgi_code;
    trans_recv_date3 := trans_recv_date;
    trans_rev_date3 := trans_rev_date;
    degc_code3 := degc_code;
    gpa_transferred3 := gpa_transferred;
    attend_from3 := attend_from;
    attend_to3 := attend_to;
    majr_code_major3 := majr_code_major;
    end if;
    end loop;
    close get_sorpcol;
    return_field := sbgi_code1 || delim || trans_recv_date1 || delim ||
    trans_rev_date1 || delim || degc_code1 || delim ||
    gpa_transferred1 || delim || attend_from1 || delim ||
    attend_to1 || delim || majr_code_major1 || delim ||
    sbgi_code2 || delim || trans_recv_date2 || delim ||
    trans_rev_date2 || delim || degc_code2 || delim ||
    gpa_transferred2 || delim || attend_from2 || delim ||
    attend_to2 || delim || majr_code_major2 || delim ||
    sbgi_code3 || delim || trans_recv_date3 || delim ||
    trans_rev_date3 || delim || degc_code3 || delim ||
    gpa_transferred3 || delim || attend_from3 || delim ||
    attend_to3 || delim || majr_code_major3 || delim;
    return return_field;
    end;


    Thx

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    unformatted code is difficult to comprehend.
    dBforums - BB Code List
    without your tables and data, we can only look at it.
    I suggest that you do not hold your breath for any solution from here.
    Last edited by anacedent; 07-08-11 at 12:54.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2011
    Posts
    18
    ok thx anyways

Posting Permissions

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