Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

Pass variable to Oracle Query form Crystal Reports

I want to pass a date variable to a sql statement from Crystal
Reports to limit the scope of the report.  The part of the query
accepting the variables looks like:

"Calendar_Date between To_Date('2003/01/01','yyyy/mm/dd') and
To_Date('2003/01/28','yyyy/mm/dd')"

And I'd be wanting to replace the date part of the query with a
user-entered date.  As I'm new to Oracle and Crystal, I don't know
where to start.  Is there a way to replace the '1-JUN-03' part with a
variable in Oracle, then modify that variable when I'm designing the
report?

Grabbing all the results and then filtering them in Crystal won't work
as this table is very large.  Apologies in advance if this question
has been asked a million times.
0
wayne_baswell
Asked:
wayne_baswell
1 Solution
 
AnnetteHarperCommented:
Just create two date parameter fields, Start_Date and End_Date, and put them in your selection formula like this.

Calendar_Date between {?Start_Date} and {?End_Date}

Assuming there aren't any other complications, Crystal will convert the date parameters to date literals with the proper TO_DATE function when it creates the query to send to Oracle. You can check this by looking at the menu Database | Show SQL Query.

Crystal will not, however, create Oracle bind variables for you. If you need that, you'll have to use a stored procedure. (You'd need that only if you are concerned about the amount of parsing going on.)
0
 
wayne_baswellAuthor Commented:
Thanks, I'm new at Oracle/Crystal and that's what I needed to know.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now