Help with SQL in Crystal Reports 2011

[G_Auto]

Good day

Could anyone please help us if possible. We are struggling with one section of our SQL in Crystal Reports 2011.

We are adding in commands into Crystal Reports to populate our custom report with commands such as:

SELECT TOP(5)
Name, RecordTime
FROM 
CDBPoint JOIN CDBHistoric ON (CDBPoint.Id = CDBHistoric.Id) 
WHERE 
CDBPoint.Name LIKE 'M10%'

which gives us the result:

Row  | Name          | RecordTime                
=================================================
   0 | M10 Available | 19/03/2013 13:54:42.679   
   1 | M10 Available | 19/03/2013 13:56:58.479   
   2 | M10 Available | 19/03/2013 13:57:35.031   
   3 | M10 Available | 19/03/2013 13:58:54.335   
   4 | M10 Available | 19/03/2013 14:01:22.910

So that part of it works fine. But now we want to add onto the WHERE such that we only get results between a certain time on a certain date and are not able to get the correct syntax.



Hopefully somebody is able to give some insight into our problem.


Thanks in advance

 

[InvisibleJim]

Something like:

SELECT TOP(5)
Name, RecordTime
FROM 
CDBPoint JOIN CDBHistoric ON (CDBPoint.Id = CDBHistoric.Id) 
WHERE 
(CDBPoint.Name LIKE 'M10%')
AND
(CDBHistoric.RecordTime BETWEEN time1 AND time2)

Where time1 and time2 are your time parameters.

 

[G_Auto]

Hi. Thanks for the reply. Unfortunately that was unsuccessful as well. It gave us the error:

SQL Command As Typed:

SELECT TOP(5)

Name, RecordTime
FROM 
CDBPoint JOIN CDBHistoric ON (CDBPoint.Id = CDBHistoric.Id) 
WHERE 
(CDBPoint.Name LIKE 'M10%')
AND
(CDBHistoric.RecordTime BETWEEN '19/03/2013 13:54:42.679' AND '19/03/2013 14:01:22.910')

*** Prepare failed - Query error. ***
*** Invalid operand ***

 

[stoymigo]

Hi. Thanks for the reply. Unfortunately that was unsuccessful as well. It gave us the error:

SQL Command As Typed:

SELECT TOP(5)

Name, RecordTime
FROM 
CDBPoint JOIN CDBHistoric ON (CDBPoint.Id = CDBHistoric.Id) 
WHERE 
(CDBPoint.Name LIKE 'M10%')
AND
(CDBHistoric.RecordTime BETWEEN '19/03/2013 13:54:42.679' AND '19/03/2013 14:01:22.910')

*** Prepare failed - Query error. ***
*** Invalid operand ***

What happens if you put only this after the AND
CDBHistoric.RecordTime > '19/03/2013 13:54:42.679'

 

[G_Auto]

What happens if you put only this after the AND
CDBHistoric.RecordTime > '19/03/2013 13:54:42.679'

Hi

Unfortunately the error I get with that is:

SQL Command As Typed:

SELECT TOP(5)
Name, RecordTime
FROM 
CDBPoint JOIN CDBHistoric ON (CDBPoint.Id = CDBHistoric.Id) 
WHERE 
(CDBPoint.Name LIKE 'M10%')
AND
(CDBHistoric.RecordTime > '19/03/2013 13:54:42.679')

*** Prepare failed - Query error. ***
*** Greater than operation '>' cannot be used with Timestamp and String. ***

 

[nfbs]

The error message explains it. Either your parameter is a string and not datetime field or your db field is a string and not datetime.
So either change it so both are the same data type or use a conversion method to convert to datetime the value that is a string.

 

[G_Auto]

The error message explains it. Either your parameter is a string and not datetime field or your db field is a string and not datetime.
So either change it so both are the same data type or use a conversion method to convert to datetime the value that is a string.

Yes we saw that and have tried many conversion methods without success. We are looking for the correct syntax to any solution. All our solutions havn't worked mainly due to syntax.

 

[Drake2007]

Try it without the Top(5) statement

 

[stoymigo]

Yes we saw that and have tried many conversion methods without success. We are looking for the correct syntax to any solution. All our solutions havn't worked mainly due to syntax.

Can you show the table structure of cbhistoric here?

 

[nfbs]

Yes we saw that and have tried many conversion methods without success. We are looking for the correct syntax to any solution. All our solutions havn't worked mainly due to syntax.

Is recordtime the string or is the parameter the string? the conversion method will depend what database you have on the backend

 

[InvisibleJim]

It looks to me like your RDBMS might store the datatime field in a different format to the date format you are using. Might be something like DDMMYYYY HH:MM:SS.XXX or YYYYMMDD HH:MM:SS.XXX.

 

[stoymigo]

The error message explains it. Either your parameter is a string and not datetime field or your db field is a string and not datetime.
So either change it so both are the same data type or use a conversion method to convert to datetime the value that is a string.

This makes sense, but op not posting table structure.

 

[InvisibleJim]

The error message explains it. Either your parameter is a string and not datetime field or your db field is a string and not datetime.
So either change it so both are the same data type or use a conversion method to convert to datetime the value that is a string.

I'm going to make the rash assumption that whoever designed the database probably didn't use a string datatype for the datetime field which probably means that the date format being used in the query is not the one you need (and is being treated as a string.)

I had a similar problem a while ago with querying our ERP system (SQL Server backend.) The date format in the application was neatly formatted in an easily readable manner like DD/MM/YYYY but the actual data in the field was stored in a format like YYYYMMDD HH:MM:SS

Queries only worked if you used the SQL table format

 

[G_Auto]

Thanks for all the help. We have managed to get the correct syntax for our SQL. We thought we'd share our solution as reference for anybody else.

We are using Crystal Reports 2011 with a ClearSCADA ODBC Database.

SELECT TOP(5)
Name, RecordTime
FROM 
CDBPoint JOIN CDBHistoric ON (CDBPoint.Id = CDBHistoric.Id) 
WHERE 
CDBPoint.Name LIKE 'M10%'
AND 
CDBHistoric.RecordTime >= TIMESTAMP ('2013-03-19 13:57:10')
AND 
CDBHistoric.RecordTime <= TIMESTAMP ('2013-03-19 14:01:10')

It accepts that syntax and now we can use it to get our history values from any time. Eg:

SELECT TOP(5)
Name, RecordTime
FROM 
CDBPoint JOIN CDBHistoric ON (CDBPoint.Id = CDBHistoric.Id) 
WHERE 
CDBPoint.Name LIKE 'M10%'
AND 
CDBHistoric.RecordTime <= (CURRENT_TIMESTAMP - INTERVAL '5' MINUTE)

or any combination of that.

Hope that can maybe help somebody else one day in the future. Thanks again for all your help.

Cheers