DB2 Date Range in Crystal Reports Command

Mike McCracken
Mike McCracken used Ask the Experts™
on
I am helping fix a Crystal Report that uses a command for the data source.  The database is DB2

The command uses 2 date parameters for the begin and end dates of a range for record selection.

The current report runs correctly but when they schedule the report in Crystal Enterprise, they have to reschedule it or change the parameters since they want last month.  They also want to be able to run it ad hoc for a date range they select.

The current command is like
SELECT table1.field1, table1.field2
FROM table1
WHERE table1.datefield BETWEEN  {?begdt} AND {?enddt}

with this command it takes 45-60 seconds to run

I modified it to


SELECT table1.field1, table1.field2
FROM table1
WHERE
(
    {?begdt} = '1990-01-01'
    AND
    table1.datefield BETWEEN
      ((CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS ) - 1 ) MONTH)
      AND
     (CURRENT DATE - (DAY(CURRENT DATE)) DAYS)
)
OR
(
table1.datefield BETWEEN  {?begdt} AND {?enddt}
)
\with this it takes 3 minutes to run when the 01-01-1990 date is entered as the parameter.  if 1 Mar - 31 Mar 2012 is entered as the values then it take the same as the original

I tried adding variables to the command so the date is calculated 1 time rather than for each record.  The command actually has 2 subselects that also to the same date range so the date range is calculated 3 times for each record.

I tried

DECLARE begdt date;
DECLARE enddt date;
SET begdt = '01-01-1990';
Set enddt = '01-01-1990';

SELECT table1.field1, table1.field2
FROM table1
WHERE
(
    {?begdt} = '1990-01-01'
    AND
    table1.datefield BETWEEN
      begdt
      AND
     enddt
)
OR
(
table1.datefield BETWEEN  {?begdt} AND {?enddt}
)

I get unexpected token begdt as the error

If I don't use begdt or enddt in the SQL  just add the declaration and SET, it is parsed and accepted and runs the report.

I tried adding BEGIN END around the code, same result

WHat am I missing about DB2 procedures?
Or is there an easier way to get the date ranges I want?


mlmcc
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
db2 is not calculating that date range for every record
it will only calculate it once
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Author

Commented:
So you are saying it calculates it when it evaluates the SQL then runs the SQL?

DO you know can a Crystal command use DB2 variables?

mlmcc
I don't have any experience with DB2.  It does seem odd that the first version would take so much longer if the dates are only being calculated once, as momi_sabag said, but if the db is doing any kind of optimizing, it really _should_ only be calculating them once, so I assume that he's correct.

 FWIW, your last version wouldn't actually work, because you just set the variables to 01/01/1990, so you'd just get the records for that date, but I don't think that has anything to do with the error.

 As far as the error goes, this is just a shot in the dark, but you could take a look at this post from last month:

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27605109.html#a37723548

 It was a similar situation, with someone trying to SET some variables in a Command, but using MySQL instead of DB2.  It seemed that CR didn't like the SET commands.  My suggestion, which apparently worked, was to create a dummy SELECT that set the variables, and UNION that with the main SELECT.  Maybe you can do something similar in DB2.

 James
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Kent OlsenData Warehouse / Database Architect
Commented:
Hi mlmcc,

>> WHat am I missing about DB2 procedures?

Are you really running the code from within a stored procedure?  The Procedure Language has a lot of extensions beyond ANSI SQL, including the the DECLARE and SET statements that you're using.

If you're not running a stored procedure, all of the variables should be local to Crystal Reports.  It would then be up to Crystal Reports to manage the interaction of getting the variable values into the SQL.  The {?begdt} syntax does that, referencing begdt without the {?} syntax won't.

The error message that you're seeing, (I get unexpected token begdt as the error) suggests that you're not running a stored procedure.

Regarding computing the date range more than once, that shouldn't be a big performance issue.  DB2 stores dates (internally) as 10000 * year + 100 * month + day no matter what the output format is.  The year 1990-01-01 is stored as 19900101(10).  It takes just a couple of instructions to extract the year, month, or day.  Trival on a processor with multi-gigahertz speed.  (Though Momi's correct that DB2 should compute it only once.)

I'm a little concerned about your date formats.  There doesn't seem to be a consistency in the format.  That'll drive DB2 nuts.


Kent
Theo KouwenhovenApplication Consultant

Commented:
you gave the answer yourself:

"with this it takes 3 minutes to run when the 01-01-1990 date is entered as the parameter.  if 1 Mar - 31 Mar 2012 is entered as the values then it take the same as the original"

The same query with an other date takes longer to select data,could it be the reason that more data is selected?

How many records do you get on selection 01-01-1990 and how many on 1 Mar - 31 Mar 2012?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
We got the same number of records from both.  in the Crystal command the

I solved the speed issue to some degree.  I had the user enter 01-01-1900 for both the start and end date parameters.  They said it actually ran faster than the original.

All dates in the actual Crystal command have the same format.  I was working from memory and messed the one up.

We are not running a stored procedure, we are running a command from Crystal.  My suspicion is that Crystal commands cannot use variables.  I was hoping someone could either confirm that or tell me how to use them.  Crystal is supposed to pass the command to the database for processing.  The only thing Crystal is supposed to do is substitue the parameters. So, the parameters {?begdt} and {?enddt} get the values passed in.

I don't have access to the system to test anything so at this point it may be pointless to pursue an answer.  I am willing to keep this open for further discussion if anyone has ideas on making it work.

mlmcc
Sorry.  All I've got is that similar situation with MySQL that I mentioned.  It does seem to indicate that there are some instructions (maybe only in certain db's) that can't be used in a CR Command, but I don't know why you can't use them.  Or maybe it's as Kent suggested and it has something to do with how the code is being run and variable scope, or something like that.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial