Help with SQL in Crystal Reports 2011

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
G_Auto
Registered User
Posts: 4
Joined: 24 Mar 2013, 14:20

Help with SQL in Crystal Reports 2011

Post 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
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Help with SQL in Crystal Reports 2011

Post 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?
Image
G_Auto
Registered User
Posts: 4
Joined: 24 Mar 2013, 14:20

Re: Help with SQL in Crystal Reports 2011

Post 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 ***

doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: Help with SQL in Crystal Reports 2011

Post 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'
MOOD - Thirsty

A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Help with SQL in Crystal Reports 2011

Post by hamin_aus »

Good point Doo.

What is the underlying SQL RDMS - MySQL, MSSQL, Oracle :?:
Image
G_Auto
Registered User
Posts: 4
Joined: 24 Mar 2013, 14:20

Re: Help with SQL in Crystal Reports 2011

Post 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
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Post by doo_much »

ClearSCADA? Sterkte.

Glad if our efforts helped at all. :thumbup:

Now don't desert the forums, stick around!
MOOD - Thirsty

A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
G_Auto
Registered User
Posts: 4
Joined: 24 Mar 2013, 14:20

Re: Help with SQL in Crystal Reports 2011

Post 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.
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Help with SQL in Crystal Reports 2011

Post 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 :?:
Image
User avatar
Ron2K
Forum Technical Administrator
Posts: 9050
Joined: 04 Jul 2006, 16:45
Location: Upper Hutt, New Zealand
Contact:

Re: Help with SQL in Crystal Reports 2011

Post 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.
Kia kaha, Kia māia, Kia manawanui.
User avatar
hamin_aus
Forum Moderator
Posts: 18363
Joined: 28 Aug 2003, 02:00
Processor: Intel i7 3770K
Motherboard: GA-Z77X-UP4 TH
Graphics card: Galax GTX1080
Memory: 32GB G.Skill Ripjaws
Location: Where beer does flow and men chunder
Contact:

Re: Help with SQL in Crystal Reports 2011

Post 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...
Image
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: Help with SQL in Crystal Reports 2011

Post 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
MOOD - Thirsty

A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
Post Reply