Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Cant do math on a parameter in sql command? Post Reply Post New Topic
Author Message
umniza
Newbie
Newbie


Joined: 03 Nov 2008
Location: United States
Online Status: Offline
Posts: 3
Quote umniza Replybullet Topic: Cant do math on a parameter in sql command?
    Posted: 03 Nov 2008 at 9:15pm

I have a report that calculates and displasy data per week.  User should be able to enter {?Week_ending_dat} and {?weeks} .  For example, if they enter 10/30 and 3, it should display data for 3 weeks prior to 10/30.  In my command, I do something like

select ... from...
where {?Week_ending_dat} <= Column.Date + ({?weeks} -1) * 7 days
 
I set {?weeks} type to Number, but CR doesnt like me doing anything to it, ie  - 1 * 7 days.  There are no issues if I put hardcoded number there instead, so sql itself is fine, parameter is the issue :(
 
Error is : "CLI0118E Invalid SQL syntax. SQLSTATE=37000"
IP IP Logged
DavidSmith
Newbie
Newbie


Joined: 06 Jun 2008
Location: United Kingdom
Online Status: Offline
Posts: 5
Quote DavidSmith Replybullet Posted: 04 Nov 2008 at 2:21am
Are these parameters ones that get used in the actual SQL command or are they just used by CR to filter data already retrieved by a previous SQL command?
For instance, if these parameters are in the SQL command, then something like this will work:
 
SELECT * FROM my_table
WHERE
fld_date >= (convert(datetime, '{?end_date}',103) - (?weeks * 7) )
AND
fld_date <= (convert(datetime, '{?end_date}',103))
 
Note that ?end_date parameter is a string in the form '01/10/2008' (the 103 is for UK formatted dates) and the ?weeks parameter is an integer.
 
Hope this helps - it certainly works on my database with CR X1.
David Smith
IP IP Logged
umniza
Newbie
Newbie


Joined: 03 Nov 2008
Location: United States
Online Status: Offline
Posts: 3
Quote umniza Replybullet Posted: 04 Nov 2008 at 7:16am

Thanks for speed reply!

Apparently it is perfectly possible, I just have to do sql command perfect from scratch :)  What I was doing is creating sql with hardcoded values first, testing and then modifying sql by substituting hardcoded with params one by one (date, weeks, i had a few more).  Well, CR would throw this error, even if I deleted command and started from scratch.  Anyway, after making new report with sql command having all parameters at once, it finally stopped complaining.  Looks like CR is very sensitive with params.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.