Solved

Change date range criteria in WHERE statement using DECODE

Posted on 2009-05-12
9
543 Views
Last Modified: 2012-05-06
I am attempting to define a date range within the WHERE portion of my query statement in  Crystal Reports and am having difficulty altering the date range using DECODE.  I believe it is a syntax issue.  I have tried a couple options in the WHERE statement:

Option 1:
s.recd_date between DECODE('{?Pm-@genMethod}', 'BATCHNAME', '01-JAN-2000' and '31-DEC-2999', '{?Pm-@startDate}' and '{?Pm-@endDate}')

Option 2:
s.recd_date between DECODE('{?Pm-@genMethod}', 'BATCHNAME', '01-JAN-2000', '{?Pm-@startDate}') and DECODE('{?Pm-@genMethod}', 'BATCHNAME', '31-DEC-2999', '{?Pm-@endDate}')

Basically, if parameter Pm-@genMethod = 'BATCHNAME', I don't want to restrict by date range.  If parameter Pm-@genMethod equals anything else, I want to restrict the date range between (inclusive) Pm-@startDate and Pm-@endDate.

I've accomplished similar things with non-date fields, just can't get a handle on the date syntax.  

I am querying against an Oracle 9 database.

Thanks for the help.
0
Comment
Question by:kenagy18
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 24371377
Hi kenagy18,

Your second option is the right syntax if you ignore the parameters.  You will most likely need to do a TO_DATE() on all four date values.

What error are you getting on Option 2?

lwadwell
0
 

Author Comment

by:kenagy18
ID: 24371407
Thanks.  I'm getting the following error with Option 2.

ORA-01858: a non-numeric character was found where a numeric was expected.

The query resides in a subreport and parameters Pm-@startDate and Pm-@endDate are both set based on a formula in the parent report using the formula:
datetime({@minDate})
datetime({@maxDate})

Is a TO_DATE() still required within the query?

Again...here is what I currently have.

s.recd_date between DECODE('{?Pm-@genMethod}', 'BATCHNAME', '01-JAN-2000', '{?Pm-@startDate}') and DECODE('{?Pm-@genMethod}', 'BATCHNAME', '31-DEC-2999', '{?Pm-@endDate}')



0
 

Author Comment

by:kenagy18
ID: 24371423
Also, in case it helps...

Simply adding s.recd_date = '01-JAN-2000' works without error.

Adding s.recd_date = '{?Pm-@startDate}' produces the same error I mentioned above.  Appears to be an issue with the parameter, not with the DECODE.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Expert Comment

by:lwadwell
ID: 24371511
kenagy18,

I think you may need the TO_DATE as it is seeing/returning the dates in the DECODE as strings and not dates - hence the error.  With the s.recd_date = '01-JAN-2000' it is doing an implied TO_DATE().  I do not know what format the '{?Pm-@startDate}' will be interpreted as.  In any case, it is safe practice to do explicit date conversions.

Try this to see if this removes the error:
s.recd_date = TO_DATE('{?Pm-@startDate}','DD-Mon-YYYY')

If this is fine, then something like:
s.recd_date between DECODE('{?Pm-@genMethod}', 'BATCHNAME', TO_DATE('01-JAN-2000','DD-Mon-YYYY'), TO_DATE('{?Pm-@startDate}','DD-Mon-YYYY')) and DECODE('{?Pm-@genMethod}', 'BATCHNAME', TO_DATE('31-DEC-2999','DD-Mon-YYYY'), TO_DATE('{?Pm-@endDate}','DD-Mon-YYYY'))

lwadwell
0
 

Author Comment

by:kenagy18
ID: 24371611
I have been trying what you proposed and can not get the error removed.  I have been focused particularly on the following statement:

s.recd_date = TO_DATE('{?Pm-@startDate}','DD-Mon-YYYY')

I think you are on the right track.  I've been trying to change the expected date format but haven't had any luck yet.
0
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 500 total points
ID: 24371674
kenagy18,

Are you still getting the same error of a date conversion error?

Do you know what format the date will be in the parameter? ... actually, what does the datetime({@minDate}) do?  ... does this do a Crystal Rpts conversion - maybe you shouldn't do that?

lwadwell
0
 

Author Comment

by:kenagy18
ID: 24371731
Yes, the error is still the same as earlier.

I have dropped the DateTime formula in Crystal.   The new parameters are now {?Pm-@minDate} and {?Pm-@maxDate}.

The minDate and maxDate fields are strings in the format of DD-MON-YYYY HH24:MI:SS
For example: 15-FEB-2007 00:00:00

I have just  tried the following:
s.recd_date = TO_DATE('15-FEB-2007 00:00:00', 'DD-MON-YYYY HH24:MI:SS')  - NO ERROR
s.recd_date = TO_DATE('{?Pm-@minDate}', 'DD-MON-YYYY HH24:MI:SS') - ERROR

The value of the parameter is 15-FEB-2007 00:00:00 so I'm confused why this isn't working.  This value is set in the parent report and I've dragged the parameter field on to the actual report to confirm this.
0
 

Accepted Solution

by:
kenagy18 earned 0 total points
ID: 24371790
I think I got it figured out.  Here is what I ended with.

s.recd_date between DECODE('{?Pm-@genMethod}', 'BATCHNAME', '01-JAN-2000', TO_DATE('{?minDate1}', 'DD-MON-YYYY HH24:MI:SS')) and DECODE('{?Pm-@genMethod}', 'BATCHNAME', '31-DEC-2999', TO_DATE('{?maxDate1}', 'DD-MON-YYYY HH24:MI:SS'))

To accomplish this I created the parameters within the command dialog (minDate1 and maxDate1) and set them as string variables.  I created the parameters manually instead of using the parameters that are auto-created when making the subreport links.  The formulas in the parent report are then linked to these new parameters.

Thanks for all your help.  Not sure I would have made it down this path without your comments.  
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24371799
kenagy18,

That is OK.  I would still put a TO_DATE around the 'fixed' dates as well.  Interpretation of strings like '01-JAN-2000' as dates can change based on environmental settings and it safest to get into the habit of always doing an explicit conversion.

lwadwell
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question