SQL Expression Error in Seagate Info 7.0
SQL Expression Error in Seagate Info 7.0
(OP)
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")]
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") and it is a definite must to keep this function in the formula.
Thanks in advance for any help.
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")]
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") and it is a definite must to keep this function in the formula.
Thanks in advance for any help.
Talk To Other Members
RE: SQL Expression Error in Seagate Info 7.0
RE: SQL Expression Error in Seagate Info 7.0
SELECT
CallDetail."CallType", CallDetail."CallDirection", CallDetail."LineId", CallDetail."LocalNumber", CallDetail."LocalName", CallDetail."InitiatedDate", CallDetail."CallDurationSeconds", CallDetail."DNIS", CallDetail."CustomNum1",
CHARINDEX('ACD', CallDetail."CallEventLog"),
CHARINDEX('ACD call connected', CallDetail."CallEventLog"),
CHARINDEX('/', CallDetail."RemoteNumber"),
CHARINDEX('ACD call assigned', CallDetail."CallEventLog")
FROM
"i3_eic"."dbo"."CallDetail" CallDetail
WHERE
CallDetail."CallType" <> 'Intercom'
Something that the SQL doesn't include is the date selection criteria, which looks like this:
[CallDetail."InitiatedDate" in {?StartDateTime} to {?EndDateTime}]
--Telephony Tech
RE: SQL Expression Error in Seagate Info 7.0
Also, three of your four CharIndex functions are going to return the same value. Is this what you want?
RE: SQL Expression Error in Seagate Info 7.0
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
RE: SQL Expression Error in Seagate Info 7.0
Here is the record selection criteria:
[{CallDetail.CallType} <> "Intercom" and
If {?RunInterval} = "Yesterday" 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
RE: SQL Expression Error in Seagate Info 7.0
This is the report that you and Vidru helped me create that is looking for information in a "Blob" 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
RE: SQL Expression Error in Seagate Info 7.0
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
RE: SQL Expression Error in Seagate Info 7.0
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