Load-Lookup and return_values of long datatype

[Anne-Marie Matula <amatula@OIT.UMASS.EDU>]

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