| Author |
Message |
acertik
Newbie
Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
|
   Topic: datepart() or totext() in SQL command?
Posted: 18 Jan 2012 at 3:04pm |
|
I'm using Crystal Reports 2008.
Is there a way to use the totext() function or the datepart() functions inside an SQL command? No matter what I try, I seem to get the syntax wrong and I cannot find any help information anywhere that addresses this.
Anybody know if this is possible?
I know this is possible inside a record selection formula; however, I need to be able to get unwanted records out before I get to the record selection stage within the report. Hopefully this makes sense.
Thank you to anyone who might have the answer.
|
 IP Logged |
|
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 19 Jan 2012 at 4:07am |
You'll need to use the SQL variations of those functions for them to work in a command.
DATEPART works pretty much identically;
Crystal Syntax (For Days)
DATEPART("d", {command.datefield})
SQL Syntax
DATEPART(dd, table.datefield)
TOTEXT however will be different;
Crystal Syntax
TOTEXT(command.field)
SQL Syntax
CAST(table.field as varchar)
or
CONVERT(varchar, table.field)
Regards,
Ryan.
Edited by rkrowland - 19 Jan 2012 at 4:11am
|
|
IP Logged |
|
acertik
Newbie
Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
|
Posted: 19 Jan 2012 at 3:08pm |
|
Ryan:
Thanks for your reply. Unfortunately, this did not work. I still get a syntax error. Maybe I should have been more specific and mentioned that I am trying to link this function to a parameter, which I created successfully within the command.
Here's what I currently have in my command:
select * from rateplan where
roomtype not in ('MASTER', 'ALL') and rateset =
'RK12'
I would like the "rateset" part to be comprised of the words "RK" plus the last two digits of whatever year the user types in. (RK12 for 2012, RK13 for 2013, etc...)
I imagine in order to do this properly, I would need to use either cast() or convert(), since the "rateset" field is set as a string field in the database. I'm pretty sure datepart() converts the data to an integer, which might cause further problems; but maybe I'm wrong.
Any suggestions? Or is this even possible to do with a parameter?
Thank you,
Alexander Certik
Edited by acertik - 19 Jan 2012 at 3:08pm
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 19 Jan 2012 at 10:44pm |
Do the users enter just a year in your paramter? Or a date?
If they just enter a year, make your parameter datatype string and the following should work;
select *
from rateplan
where roomtype not in ('MASTER', 'ALL')
and rateset = 'RK' & right('{?parameter}',2)
If they enter a full date (datatype datetime), the following should work;
select *
from rateplan
where roomtype not in ('MASTER', 'ALL')
and rateset = 'RK' & right(cast(year({?parameter}) as varchar(4)),2)
I've haven't actually tested any of these with tables on my database, they should work but let me know if not and I'll try them against tables in my database.
Regards,
Ryan.
Edited by rkrowland - 19 Jan 2012 at 10:47pm
|
|
IP Logged |
|
acertik
Newbie
Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
|
Posted: 20 Jan 2012 at 12:47pm |
Looks like we're on the right track! Thank you! There is still a problem, however... instead of getting a syntax error, I am now getting an error that reads "Incompatible type in expression." I am assuming it's because of a data type mismatch, but I'm not sure where? By the way, the user types in a full date, so there is a date parameter in place. I just need it to extract the last two digits of the year from that date, so I used example 2 in your last post.
Thanks for all your help,
Alexander Certik
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 22 Jan 2012 at 9:58pm |
Try the following;
select *
from rateplan
where roomtype not in ('MASTER', 'ALL')
and rateset like ('RK' + right(cast(year({?parameter}) as varchar(4)),2))
Regards,
Ryan.
|
|
IP Logged |
|
acertik
Newbie
Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
|
Posted: 23 Jan 2012 at 6:37pm |
|
Thanks Ryan. I'll be back in the office tomorrow so I'll try that and let you know what happens.
Alexander Certik
Edited by acertik - 23 Jan 2012 at 6:38pm
|
|
IP Logged |
|
acertik
Newbie
Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
|
Posted: 25 Jan 2012 at 12:01pm |
|
Ryan:
Unfortunately I'm now getting a syntax error again with this latest suggestion.
Any other advice?
Thank you for all your help,
Alexander Certik
|
|
IP Logged |
|
rkrowland
Senior Member
Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
|
Posted: 26 Jan 2012 at 12:56am |
Hmm I'm unsure what the syntax error could be, the above still works fine for me.
Where does it say the syntax error is near?
Which management system are you using? I only have experience with MS SQL Server, if you're using Oracle or something else it could be due to that.
A couple of things to try on the above which may work, replace the "+" with "&" and/or replace " {?parameter} " with " '{?parameter}' ".
|
|
IP Logged |
|
acertik
Newbie
Joined: 16 Jan 2012
Online Status: Offline
Posts: 12
|
Posted: 26 Jan 2012 at 12:03pm |
|
Hmm. I didn't know the syntax was different between the different systems. I thought it was all internal within Crystal Reports. We use Pervasive as out database management tool.
The syntax error I get appears to be right after like and just before the (.
Maybe it's just a matter of getting the SQL syntax correct for Pervasive? If that's the case, you've given me an idea of something to look into, unless you have any other thoughts.
I tried all your suggestions and they didn't work.
Thank you.
Edited by acertik - 26 Jan 2012 at 12:03pm
|
|
IP Logged |
|
|
|
