Memo Field

[ali1227]

Crystal Report 8.5
Oracle 9.2.0.7.0

In my table i have a field varchar2(4000) which is een by crystal as memo field. My criteria is based on memo field which i can not see. I tried to create sql expression like {fn SUBSTRING(PERSONCSTMDATA."PERSONCSTMDATATXT", 1, 3)} but it is giving me invalid field error message compiliation error. I tried with oracle odbc driver than crystal report oracle driver but none of them working.

Any help is really appricated at this time. I wrote this Qyery and it is working fine. Can i use this query directly in Crystal report 8.5

/* Pass p_Date Parameter value as of Jan 1 of the year for example, for seeing vaction
balance of dec 31, 2006 and Jan 1 2007 then pass the value as 2007/01/01*/
Select emp.PERSONNUM
,emp.EMPLOYMENTSTATUS
,emp.PAYRULENAME
,emp.PERSONFULLNAME
,emp.PERSONCSTMDATATXT paycode
,acb.PERSONNUM
,acb.ACCRUALCODESHORTNM
,sum(Decode(acb."Type", 1, acb.Balance, '')) "Dec_31"
,sum(Decode(acb."Type", 2, acb.Balance, '')) "Jan_01"
From
(
select ve.PERSONNUM, ve.EMPLOYMENTSTATUS, ve.PAYRULENAME, ve.PERSONFULLNAME, pcd.PERSONCSTMDATATXT
from vp_EMPLOYEEv42 ve, personcstmdata pcd
where ve.PERSONID = pcd.PERSONID
and (pcd.PERSONCSTMDATATXT in ('TA4', 'TA6') or substr(ve.PAYRULENAME, 1, 3) = 'TA6')
and ve.EMPLOYMENTSTATUS = 'Active'
) emp,
(
select va.PERSONFULLNAME
,va.PERSONNUM
,va.ACCRUALCODESHORTNM
,1 "Type"
,sum(va.ACCRUALTRANAMOUNT/3600) Balance
from vp_accrual va
where va.EFFECTIVEDATE >= (select max(act.effectivedate)
from accrualtran act
where act.effectivedate <= _date - 1
and act.ACCRUALCODEID in (514, 516)
and act.TYPE in (3,11)
and act.EMPLOYEEID = va.EMPLOYEEID
)
and va.EFFECTIVEDATE <= _date - 1
and
va.ACCRUALCODESHORTNM in ('VAC', 'VTC')
group by
va.PERSONFULLNAME
,va.PERSONNUM
,va.ACCRUALCODESHORTNM
UNION
select va.PERSONFULLNAME
,va.PERSONNUM
,va.ACCRUALCODESHORTNM
,2 "Type"
,sum(va.ACCRUALTRANAMOUNT/3600) Balance
from vp_accrual va
where va.EFFECTIVEDATE >= (select max(act.effectivedate)
from accrualtran act
where act.effectivedate <= _date
and act.ACCRUALCODEID in (514, 516)
and act.TYPE in (3,11)
and act.EMPLOYEEID = va.EMPLOYEEID
)
and va.EFFECTIVEDATE <= _date
and
va.ACCRUALCODESHORTNM in ('VAC', 'VTC')
group by
va.PERSONFULLNAME
,va.PERSONNUM
,va.ACCRUALCODESHORTNM
) acb
Where emp.PERSONNUM = acb.PERSONNUM(+)
group by
emp.PERSONNUM
,emp.EMPLOYMENTSTATUS
,emp.PAYRULENAME
,emp.PERSONFULLNAME
,emp.PERSONCSTMDATATXT
,acb.PERSONNUM
,acb.ACCRUALCODESHORTNM

 

[synapsevampire]

For the SQL Expression try:

SUBSTR(PERSONCSTMDATA.PERSONCSTMDATATXT,1,3)

If the syntax works in Oracle (SQLPlus or whatever), it should work here.

Not sure if these are two different questions???

You could create a Stored Procedureand use that, or a View, but you cannot use that SQL AND pass a parameter to it from within Crystal 8.5.

-k

 

[ali1227]

Thanks for your reply.

Substr syntax is working from todd but not able to get data in crysatl report.

 

[synapsevampire]

Does this mean that it errors out?

Perhaps you could take the time to post what you tried instead of saying "not able to get data"

-k

 

[ali1227]

The SUBSTR(PERSONCSTMDATA.PERSONCSTMDATATXT,1,3) you suggested i tested it from todd and it return the result which i am looking for but when i use the {fn SUBSTRING(PERSONCSTMDATA."PERSONCSTMDATATXT", 1, 3)} sql expression in crystal 8.5 i got error
"Error in compiling sql expression: Invalid field found here" but when i used the other fields it works fine. It is just this memo field which is giving error message.

 

[synapsevampire]

Well I didn't suggest that you use a fn formula, you decided to ignore my suggestion.

Try what you used in Toad...

-k

 

[ali1227]

synapsevampire can you please tell me how i can use SUBSTR(PERSONCSTMDATA.PERSONCSTMDATATXT,1,3) in crystal report. When i copy the above substr in sql expression, i got invalid function found here error message. I changed the substr to substring then i got the invalid string identifier error message.

 

[bmarks]

ali1227,

I think the issue here is that in versions prior to 9, you can't use a SQL command. If you use 9 or above, you can use "Add Command" to input your SQL expression.

Since you are using 8.5, you need to to do the work prior to Crystal. synapsevampire suggested a stored procedure or a view and either of these should work fine.

However, if you can't do the work in the database, I have read that you can use the subtr function in a formula to parse it out. The tricky part is that the memo field does not show up in the field list. I found a reference on a website that might help you. Haven't tried it myself (and not on Oracle and it may be database dependant). The site is:

http://www.kenhamady.com/news0206.shtml

and it's near the bottom of the page.

 

[synapsevampire]

bmarks:

We're speaking of SQL Expressions, hit F1 and read up. So the real issue is people not understanding SQL Expressions, now two of you, though Ali seems to understand them, he just has a buglet.

Ali:

It may be a driver issue, or something about the data itself, but the syntax should be SUBSTR(field,start,length)

In essence, Crystal places what you add into the select line of it's query.

So this doesn't return an ORA: error number?

And when yousay in your table, you mean a table, right, not a stored procedure.

I've used SQL Expressions for years, so there's something else afoot here.

-k

 

[ali1227]

I tried the same thing with crystal report 9 and it looks the field as varchar and working fine with sql expression formula but crystal 8.5 see this field as memo and i can not create a sql expression formula aginst the field.

For the crystal report 8.5 and 9 i used the same oracle odbc driver this time.

I never understand how i use substr(field,start,length)
in sql expression in crystal report 8.5. When you go to sql expreesion editior, it does not give you substr function. I tried it but it did not work. the only string function available under sql expression builder is {fn SUBSTRING(field,start,length)}

 

[synapsevampire]

A SQlExpfression is passed through, so rather than using the built in functions that get translated to the language, you can use the proper SQL, which has been mypoint here.

Not sure why it doesn't work for you,works here...

Ofcourse you're NOT supposed to use the Oracle ODBC driver in CR 8.5, and using CR native connectivity for Oracle is faster than either ODBC methods.

-k