SQL Code in Crystal Reports
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20697
Printed Date: 05 May 2024 at 2:21am
Topic: SQL Code in Crystal Reports
Posted By: TrevShand
Subject: SQL Code in Crystal Reports
Date Posted: 01 May 2014 at 7:08am
I am trying to use the Add Command ability on the Report Wizard for Crystal Report XI to write SQL. I have this code:
Select *
From CustomerInvoice
Where CustomerInvoice.OrderDate Between '01/01/2014' and '01/15/2014'
I am getting an error that says "Invalid operand for operator: between."
Can anyone let me know what I am doing wrong? This is my first experience with Crystal, most of my SQL has been with SQL Server and Terradata.
|
Replies:
Posted By: kostya1122
Date Posted: 01 May 2014 at 7:12am
looks fine
maybe try an alternative
Select *
From CustomerInvoice
Where CustomerInvoice.OrderDate >= '01/01/2014' and
CustomerInvoice.OrderDate <= '01/15/2014'
|
Posted By: TrevShand
Date Posted: 01 May 2014 at 8:14am
|
I get an error that says: "Invalid operand for operator: >=" when I try that method.
|
Posted By: kostya1122
Date Posted: 01 May 2014 at 8:39am
it could be that your field is not a date, but a string.
Select *
From CustomerInvoice
Where cast(CustomerInvoice.OrderDate as date) Between '01/01/2014' and '01/15/2014'
|
Posted By: TrevShand
Date Posted: 01 May 2014 at 8:58am
Changed code slightly to:
Select *
From CustomerInvoice
Where cast(CustomerInvoice.OrderDate as date) >= '01/01/2014'
and got an error that said:
"Expected lexical element not found: =, <>, <, <=, >, or >="
|
Posted By: kostya1122
Date Posted: 01 May 2014 at 9:06am
try instead of pasting >=
type it in manually.
|
Posted By: TrevShand
Date Posted: 01 May 2014 at 9:28am
Typed the entire thing manually, got the same error. The entire error is "Failed to Retrieve data from the database. Details: 37000:[ProvideX][ODBC Driver] Expected lexical element not found: =, <>, <, <=, >, or >= [Database Vendor Code: 1015]" if that changes anything.
|
Posted By: lockwelle
Date Posted: 02 May 2014 at 5:10am
While everything looks correct, SAP help basically says that the database is returning the error...have you tried running the same query in something like SQL Server...or whatever your backend is. Perhaps it is there. I know that Oracle would choke on the syntax as it doesn't handle dates that way.
For SQL Server, your code looks fine (all of them)
Another thought is to create another command that is simpler, say:
select CustomerInvoice.OrderDate from CustomerInvoice
and then check the data type that Crystal 'sees' it as.
That would at least help. CAST is ANSI standard, so any database should understand it. The last option would be to have the command and add a Selection Formula to it in Crystal that would filter the date correctly...but the command should take care of it.
Just to set you mind at ease, I believe that Crystal takes the command and passes it to the backend. Which means that the syntax in the Command has to be correct for the backend (Crystal doesn't modify the SQL that you enter) at least this is what I think...and I have been wrong before.
HTH
|
Posted By: kevlray
Date Posted: 05 May 2014 at 6:13am
I cannot speak of Oracle (do not use it much). But with MS-SQL, I would have to do something like this.
Select *
From CustomerInvoice
Where cast(CustomerInvoice.OrderDate as date) >= cast('2014-01-01' as date)
|
Posted By: hilfy
Date Posted: 06 May 2014 at 11:16am
Lockwelle, you are correct. Crystal makes no changes to the SQL of a command when it passes it to the database. This also means that any formula entered in the Select Expert will be processed in memory after the data is returned instead of being pushed down to the database.
Kevlray, for Oracle it might look like this:
Select *
From CustomerInvoice
Where cast(CustomerInvoice.OrderDate as date) >= '01-JAN-2014'
(the default date format in Oracle is dd-MMM-yyyy.)
However, one of TrevShand's posts show that he is using what looks like a non-standard ODBC driver.
TrevShand,
What type of database are you connecting to? Do you have any way of testing the SQL outside of Crystal prior to adding it to the Command? That's the best way to make sure that your SQL is working correctly. It looks like you may be dealing with something that doesn't recognize many "standard" SQL operators and/or expressions.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
|