Page 1 of 1
Help with SQL in Crystal Reports 2011
Posted: 24 Mar 2013, 14:59
by 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:
Code: Select all
SELECT TOP(5)
Name, RecordTime
FROM
CDBPoint JOIN CDBHistoric ON (CDBPoint.Id = CDBHistoric.Id)
WHERE
CDBPoint.Name LIKE 'M10%'
which gives us the result:
Code: Select all
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
Re: Help with SQL in Crystal Reports 2011
Posted: 24 Mar 2013, 15:43
by hamin_aus
Code: Select all
SELECT TOP(5)
Name, RecordTime
FROM
CDBPoint JOIN CDBHistoric ON (CDBPoint.Id = CDBHistoric.Id)
WHERE
CDBPoint.Name LIKE 'M10%'
AND
CDBPoint.RecordTime BETWEEN '19/03/2013 13:54:42.679' AND '19/03/2013 14:01:22.910'
No offense, but how did Google fail you in this?
Re: Help with SQL in Crystal Reports 2011
Posted: 24 Mar 2013, 17:58
by G_Auto
Hi
Thanks for the reply. Google unfortunately failed us because everything it suggested hasn't worked. Seems our SQL is displeased with your try aswell. It gives us the error:
Code: Select all
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 ***
Re: Help with SQL in Crystal Reports 2011
Posted: 24 Mar 2013, 20:45
by doo_much
Haven't touched Crystal in ages, but let's give this a try.
What database are you using and how are you connecting to it?
Crystal limits the functions available to a SQL Expression to certain database-specific functions (which is why most people prefer to use the common Crystal or Basic Syntax functions in a formula.)
And then not even all of those - the exact database functions available are determined by the database AND the database driver.
HOWEVER
Something 'stupid' along these lines should work just about everywhere...
Code: Select all
WHERE
TO_CHAR(CDBHistoric.RecordTime,'MM/DD/YYYY') >= '03/01/2013' AND
TO_CHAR(CDBHistoric.RecordTime,'MM/DD/YYYY') <= '03/07/2013'
Re: Help with SQL in Crystal Reports 2011
Posted: 25 Mar 2013, 02:36
by hamin_aus
Good point Doo.
What is the underlying SQL RDMS - MySQL, MSSQL, Oracle
Re: Help with SQL in Crystal Reports 2011
Posted: 25 Mar 2013, 10:06
by 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.
Code: Select all
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:
Code: Select all
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
Posted: 25 Mar 2013, 18:12
by doo_much
ClearSCADA? Sterkte.
Glad if our efforts helped at all.
Now don't desert the forums, stick around!
Re: Help with SQL in Crystal Reports 2011
Posted: 25 Mar 2013, 18:35
by G_Auto
Loving ClearSCADA. Much easier than Citect. I'm on the forums here in my personal capacity already, so I'll still be here. This is just for business.
Thanks again for the help.
Re: Help with SQL in Crystal Reports 2011
Posted: 26 Mar 2013, 03:53
by hamin_aus
Doesn't look like ANSI/ISO SQL to me... never heard of ClearSCADA either ... is it some type of in-memory, nosql or one of those non-relational variants
Re: Help with SQL in Crystal Reports 2011
Posted: 26 Mar 2013, 10:50
by Ron2K
hamin_aus wrote:Doesn't look like ANSI/ISO SQL to me... never heard of ClearSCADA either ... is it some type of in-memory, nosql or one of those non-relational variants
It's some form of SCADA software -- and given that this type of software typically uses specialized protocols and proprietary interfaces (there's a lot of vendor lock-in in that sector), it's to be expected that the data access layer isn't ANSI/ISO SQL compliant. In fact, I'm genuinely surprised that it can be interfaced with via ODBC.
Of course, because of the proprietary nature of the whole thing, you're not going to find any information on what data storage model it uses, be it relational, nosql, or whatever. In fact, other than a very sparse product page, I couldn't find any useful information on ClearSCADA itself. Though, from reading through the rest of this thread, doo_much probably knows more about it than the rest of us.
Re: Help with SQL in Crystal Reports 2011
Posted: 26 Mar 2013, 11:22
by hamin_aus
Whats particularly perplexing in retrospect is that the OP didn't lead with the fact that his SQL question was for a proprietary system.
That would have - at worst - saved him from my snarky Google comment...
Re: Help with SQL in Crystal Reports 2011
Posted: 26 Mar 2013, 19:36
by doo_much
Ron2K wrote:Though, from reading through the rest of this thread, doo_much probably knows more about it than the rest of us.
Not really, I'm just an average Google user.
Although.
Scarily enough - and I didn't know it was relevant - I have created created Crystal reports from SCADA systems before. Go figure.
*edit
Serious quote fail