Re: Load-Lookup and return_values of long datatype

[Scott Hitchcock <sbhitchc@HOTMAIL.COM>]

Anne-Marie,

You might try using the function below from Ken Atkins' Oracle tip page.
Change the varchar2(2000) to varchar2(60) and substitute your
columns/tables.  I use it often...works great. Is also great for searching a
long data type, first 2000 characters anyway, without the oracle context
package.


CREATE OR REPLACE FUNCTION comments (employee_id IN NUMBER) RETURN VARCHAR2
IS

c_desc VARCHAR2(2000);
c_Long LONG;

BEGIN
  SELECT comments INTO c_Long
    FROM comment_table
   WHERE long_id = employee_id;
  v_Desc := substr(c_long,1,2000);
  RETURN(c_desc);
END;


Usage.....

SELECT comments(emplid)
FROM comment_table
where....



>From: Anne-Marie Matula <amatula@OIT.UMASS.EDU>
>Reply-To: sqr-users@list.iex.net
>To: SQR-USERS@list.iex.net
>Subject: Load-Lookup and return_values of long datatype
>Date: Thu, 6 Jul 2000 16:45:08 -0400
>
>Hi there SQR gurus and geeks,
>
>I was hoping to use LOAD-LOOKUP to return a value stored in a LONG datatype
>(field=comments).  We are using Oracle 8.0.3 database last I checked.
>Unfortunately LOAD-LOOKUP selects the value using the distinct command
>which
>is illegal with LONG datatypes:
>
>ORA-00997: illegal use of LONG datatype
>SQL:  select distinct Emplid, Comments from PS_PERSON_COMMENT umexgtrm1
>where
>       umexgtrm1.CMNT_CATEGORY='GSOL' and umexgtrm1.COMMENT_DTTM = (select
>       MAX(COMMENT_DTTM) from PS_PERSON_COMMENT umexgtrm2 where
>       umexgtrm1.EMPLID = umexgtrm2.EMPLID and
>umexgtrm2.CMNT_CATEGORY='GSOL')
>Error at:  Comments
>
>My Load-Lookup command is:
>
>LET $where = 'umexgtrm1.CMNT_CATEGORY=''GSOL'' and '||
>              'umexgtrm1.COMMENT_DTTM = (select MAX(COMMENT_DTTM) from
>PS_PERSON_COMMENT umexgtrm2 where '||
>              'umexgtrm1.EMPLID = umexgtrm2.EMPLID and
>umexgtrm2.CMNT_CATEGORY=''GSOL'')'
>
>LOAD-LOOKUP
>  Name=Um-SOL-Comments
>  Table='PS_PERSON_COMMENT umexgtrm1'
>  Key=Emplid
>  Return_value=Comments
>  where=$where
>
>----------------
>Does anyone have any suggestions on how to get what I want (the first 60
>characters or so from the LONG field) using LOAD-LOOKUP?  I guess I could
>link this query with my main one using an outer join but I wanted to see if
>I could get this to work first.
>
>Thanks!
>
>Have a good one,
>
>Anne-Marie
>
>PS:  I get SQR-Users in digest format so if you could cc me in on any
>response I'd greatly appreciate it
>
>
>
>
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>Anne-Marie Matula
>Student Information System Project
>Office of Information Technologies
>University of Massachusetts
>161 Whitmore Administration Building
>Amherst Massachusetts, 01003-8165
>Phone: 413/577-0685
>Fax: 413/545-2150

________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com