×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL Expression Error in Seagate Info 7.0

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.

RE: SQL Expression Error in Seagate Info 7.0

Can you please post your entire Record Selection Criteria?  

RE: SQL Expression Error in Seagate Info 7.0

(OP)
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"),
    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

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?

RE: SQL Expression Error in Seagate Info 7.0

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

RE: SQL Expression Error in Seagate Info 7.0

(OP)
Rhinok,
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

(OP)
Synapsevampire,

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

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

RE: SQL Expression Error in Seagate Info 7.0

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close