SQL Expression Error in Seagate Info 7.0 1

[T3leTech]

Hi,

I am trying to schedule a report (CR v 8.0) that uses a number of SQL Expressions, and I get an error messasge when the report first starts processing. I do not get this error when running the report in CR, only when I try to run it in Seagate.

The SQL expression is: [CHARINDEX('ACD', CallDetail."CallEventLog&quot]

This is the particular SQL expression that errors out, probably because it is the first one in the SELECT statement in CR. I am using a total of 4 of these CHARINDEX funtions.

The specific error message I receive in Seagate is:
[Error in compiling SQL Expression. Invalid function found here.]

I need to know if there is a different function name that will do the same thing in Seagate. The reason I am using this function is listed in another thread (Crystal Decisions: Crystal Reports 1 Formulas Forum, #767-709570, listed as ""Blob field" and "memo field" in formula not working&quot and it is a definite must to keep this function in the formula.

Thanks in advance for any help.

 

[rhinok]

Can you please post your entire Record Selection Criteria?

 

[T3leTech]

Here is the SQL Query:

SELECT
CallDetail."CallType", CallDetail."CallDirection", CallDetail."LineId", CallDetail."LocalNumber", CallDetail."LocalName", CallDetail."InitiatedDate", CallDetail."CallDurationSeconds", CallDetail."DNIS", CallDetail."CustomNum1",
CHARINDEX('ACD', CallDetail."CallEventLog&quot,
CHARINDEX('ACD call connected', CallDetail."CallEventLog&quot,
CHARINDEX('/', CallDetail."RemoteNumber&quot,
CHARINDEX('ACD call assigned', CallDetail."CallEventLog&quot
FROM
"i3_eic"."dbo"."CallDetail" CallDetail
WHERE
CallDetail.&quot;CallType&quot; <> 'Intercom'

Something that the SQL doesn't include is the date selection criteria, which looks like this:
[CallDetail.&quot;InitiatedDate&quot; in {?StartDateTime} to {?EndDateTime}]

--Telephony Tech

 

[rhinok]

Thanks for posting the SQL Statement, can you please post the Record Selection Criteria. Based on the fact that your date parameters aren't being passed to the SQL, I'd say that the record selection criteria isn't formed efficiently.

Also, three of your four CharIndex functions are going to return the same value. Is this what you want?

 

[synapsevampire]

CR 7 supports SQL Expressions, or are you customizing the SQL?

If possible, create a View using your SQL, and then use the View in the report and pass parameters to it.

You can use the instr() function in CR to determine if something exists in a field, but you'd need to return a converted field (254 chars or less) to test against it.

-k

 

[T3leTech]

Rhinok,
Here is the record selection criteria:
[{CallDetail.CallType} <> &quot;Intercom&quot; and
If {?RunInterval} = &quot;Yesterday&quot; then
{CallDetail.InitiatedDate} = currentdate - 1
else
{CallDetail.InitiatedDate} in ({?StartDateTime} to {?EndDateTime})]

When I run this report in Crystal Reports 8.0, it runs just fine. I only experience the problem when trying to schedule it in Seagate Info.

Yes, I need all four of the CHARINDEX functions, because each of them is returning values for different formulas. The expressions are similar, but they are not the same, and will not return duplicate values.


--Telephony Tech

 

[T3leTech]

Synapsevampire,

This is the report that you and Vidru helped me create that is looking for information in a &quot;Blob&quot; field (2000 characters). This report runs perfectly within Crystal Reports (8.0) but will not run in Seagate Info (7.0). Any ideas?

--Telephony Tech

 

[synapsevampire]

Did you save the report as a version CR 7 report?

Do you have CR 7? If so, open the report in CR 7 and see what is returned.

Are you using the same connectivity?

Is it a different version of SQL Server?

-k

 

[T3leTech]

Synapsevampire,

Thank you for asking those questions. I saved the report as a Crystal Report version 8.0, but Seagate Info 7 uses CR version 7. So, all I had to do was save the report in version 7 format, and it works great!

Thanks again for your help!

--Telephony Tech