Solved

Change date range criteria in WHERE statement using DECODE

Posted on 2009-05-12
9
534 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
  • 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now