Blob or Memo Field Cannot Be Used In A Formula

[tahir]

One field in my Oracle view for my report uses a function.

I can insert the field without a problem in my report. However, when I try to use that field in a formula I get the following error message:

"Blob or Memo Field Cannot Be Used In A Formula"

Does anyone know how to either convert that field in the view or in Crystal so that I can use it in my Formula.

Thanks.

 

[dgillz]

The error message is correct, there is no solution in crystal.

You could build a SQL stored procedure and use it as your data source for your report. Then you could manipulate the blob field in the SP.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[synapsevampire]

There may be a solution in Crystal, use a SQL Expression, or you might alter the View to make it another data type.

Let's see the View.

-k

 

[tahir]

Here is the View:
CREATE OR REPLACE VIEW VWPOHEADER AS SELECT PO_NUMBER,P.PO_REVISION,P.PO_RELEASE,P.PO_CODE, P.PO_DATE,P.SHIP_VIA ,B.BUYER_CODE AS BCODE, B.R_NAME AS BNAME,B.ADDR1 AS BADDR1,B.ADDR2 AS BADDR2,B.ADDR3 AS BADDR3,B.ADDR4 AS BADDR4,B.CITY_ADDR5 AS CITYADDR5, B.POSTAL_CODE AS BPOSTALCODE,B.COUNTRY AS COUNTRY,B.PHONE_PREFIX AS BPHONEPREFIX,B.PHONE AS BPHONE ,A.VENDOR_VNAME AS VNAME, V.VENDOR AS VNUMBER, V.LOCATION_CODE AS VLOCATION, V.VENDOR_CONTCT AS VCONTACT, V.PHONE_NUM AS VPHONENUMBER, V.FAX_NUM AS VFAXNUMBER ,M.ADDR1 AS VADDR1,M.ADDR2 AS VADDR2,M.ADDR3 AS VADDR3,M.ADDR4 AS VADDR4,M.CITY_ADDR5 AS VCITY, M.STATE_PROV AS VSTATE, M.POSTAL_CODE AS VPOSTALCODE,M.COUNTRY AS VCOUNTRY ,I.PO_NAME, I.PO_ADDR1, I.PO_ADDR2, I.PO_ADDR3, I.PO_ADDR4, I.PO_CITY_ADDR5, I.PO_STATE_PROV, I.PO_POSTAL_CD, I.PO_COUNTRY, I.ADDR_CONTACT ,T.R_DESC_01 AS PAY_TERMS, F.DESCRIPTION AS FOB, FRT.FRT_TERM_DESC AS FREIGHT_TERMS, C.R_NAME AS COMPANY_NAME
FROM
LAWSON.PURCHORDER P
,LAWSON.BUYER B
,LAWSON.APVENLOC V
,LAWSON.APVENADDR M
,LAWSON.APVENMAST A
,LAWSON.ICLOCATION I
,LAWSON.TERMS T
,LAWSON.FOBCODE F
,LAWSON.POFRTTERM FRT
,LAWSON.POCOMPANY C
WHERE
P.BUYER_CODE=B.BUYER_CODE AND
P.VENDOR=V.VENDOR AND
P.VENDOR=M.VENDOR AND
P.PURCH_FR_LOC=V.LOCATION_CODE AND
V.VENDOR_GROUP=M.VENDOR_GROUP AND
V.VENDOR=M.VENDOR AND
V.LOCATION_CODE=M.LOCATION_CODE AND
M.CUR_ADDR='Y' AND
V.VENDOR_GROUP=A.VENDOR_GROUP AND
V.VENDOR=A.VENDOR AND
P.COMPANY=I.COMPANY AND
P.LOCATION=I.LOCATION AND
P.TERM_CODE=T.TERMS_CD AND
P.FOB_CODE=F.FOB_CODE(+) AND
P.FREIGHT_TERMS=FRT.FREIGHT_TERMS(+) AND
P.COMPANY=C.COMPANY

Here is the Function:

CREATE OR REPLACE function get_contract_detail (
p_activity in varchar2,
p_element in varchar2)

return VARCHAR2 is

v_primecontract lawson.acaddlcntr.prime_cnt_nbr%type;
v_subcontract lawson.acaddlcntr.sub_cnt_nbr%type;
v_refnbr lawson.acaddlcntr.ref_nbr%type;
v_activity_grp lawson.acaddlcntr.activity_grp%type;
begin

select trim(a.prime_cnt_nbr),trim(a.sub_cnt_nbr),trim(a.ref_nbr),a.activity_grp
into v_primecontract, v_subcontract, v_refnbr,v_activity_grp
from lawson.acaddlcntr a,
(select activity_grp, contract
from lawson.acactivity
where activity = get_summary_activity(p_activity,'C')) b
where a.activity_grp = b.activity_grp
and a.contract = b.contract;

select nvl(v_primecontract,nvl(prime_cnt_nbr,' ')),
nvl(v_subcontract,nvl(sub_cnt_nbr,' ')),
nvl(v_refnbr,nvl(ref_nbr,' '))
into v_primecontract, v_subcontract, v_refnbr
from lawson.acaddlcntr
where activity_grp = v_activity_grp
and trim(contract) is null;



if p_element = 'P' then
return v_primecontract;
else if p_element = 'S' then
return v_subcontract;
else if p_element = 'R' then
return v_refnbr;
else
return ' ';
end if;
end if;
end if;

exception
when others then
return null;
end;
/