| Author |
Message |
TrevShand
Newbie
Joined: 01 May 2014
Online Status: Offline
Posts: 4
|
   Topic: SQL Code in Crystal Reports
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.
|
 IP Logged |
|
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
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'
|
|
IP Logged |
|
TrevShand
Newbie
Joined: 01 May 2014
Online Status: Offline
Posts: 4
|
Posted: 01 May 2014 at 8:14am |
|
I get an error that says: "Invalid operand for operator: >=" when I try that method.
|
|
IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
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'
|
|
IP Logged |
|
TrevShand
Newbie
Joined: 01 May 2014
Online Status: Offline
Posts: 4
|
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 >="
|
|
IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 01 May 2014 at 9:06am |
|
try instead of pasting >=
type it in manually.
|
|
IP Logged |
|
TrevShand
Newbie
Joined: 01 May 2014
Online Status: Offline
Posts: 4
|
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.
|
|
IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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)
Edited by kevlray - 05 May 2014 at 6:14am
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
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
|
|
|
|
IP Logged |
|
|
|
