Print Page | Close Window

Cant do math on a parameter in sql command?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=4708
Printed Date: 02 May 2024 at 10:36pm


Topic: Cant do math on a parameter in sql command?
Posted By: umniza
Subject: Cant do math on a parameter in sql command?
Date 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"


Replies:
Posted By: DavidSmith
Date 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, mailto:%7b@start_date%7d,103%29 - '{?end_date}',103) - (?weeks * 7) )
AND
fld_date <= (convert(datetime, mailto:%7b@start_date%7d,103 - '{?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

Posted By: umniza
Date 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.


Print Page | Close Window