Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    7

    Unanswered: oracle cursor with 'in' variable

    I am having trouble with this cursor. I am trying to get last payment info for a give custid. My problem seems to be variable substitution (or lack of) in the cursor.

    cursor c1 (
    IN_date_list varchar2,
    IN_cust_id varchar2
    )
    is
    select pmt_amt,pmt_date
    from
    (
    select pmt_amt,pmt_date
    from TableName t1
    where t1.invc_date in ( IN_date_list ) and t1.cust_id =IN_cust_id
    order by t1.invc_date desc, t1.pmt_amt asc
    )
    where rownum = 1;

    Note IN_date_list - this would look like '01-DEC-2002','01-NOV-2002' ... ETC

    Does anyone have any ideas on how to get this to work - I can't use between for invc_date - I must use an 'in' list.

    Thanks for your help

  2. #2
    Join Date
    Jun 2003
    Posts
    34

    Re: oracle cursor with 'in' variable

    Well, you did not mention whether invc_date is date or varchar2 field. Anyways, I think if you convert the invc_date with to_char you could get the desired result, cuz the cursor takes in a varchar variable here and the invc_date may be a date field.... You know the rest I guess;-)

    for eg.

    cursor c1 (
    IN_date_list varchar2,
    IN_cust_id varchar2
    )
    is
    select pmt_amt,pmt_date
    from
    (
    select pmt_amt,pmt_date
    from TableName t1
    where to_char(t1.invc_date,'dd-mon-yyyy') in ( IN_date_list )
    and t1.cust_id =IN_cust_id
    order by t1.invc_date desc, t1.pmt_amt asc
    )
    where rownum = 1;


    Cheers!!

    Originally posted by gtr111
    I am having trouble with this cursor. I am trying to get last payment info for a give custid. My problem seems to be variable substitution (or lack of) in the cursor.

    cursor c1 (
    IN_date_list varchar2,
    IN_cust_id varchar2
    )
    is
    select pmt_amt,pmt_date
    from
    (
    select pmt_amt,pmt_date
    from TableName t1
    where t1.invc_date in ( IN_date_list ) and t1.cust_id =IN_cust_id
    order by t1.invc_date desc, t1.pmt_amt asc
    )
    where rownum = 1;

    Note IN_date_list - this would look like '01-DEC-2002','01-NOV-2002' ... ETC

    Does anyone have any ideas on how to get this to work - I can't use between for invc_date - I must use an 'in' list.

    Thanks for your help

Posting Permissions

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