Solved

Case Statement with less than or greater than date

Posted on 2013-10-24
7
617 Views
Last Modified: 2013-10-29
Hello,

I am having trouble with the statement below.  It was working fine before I added the date portions  trunc(pay.dos) < '04/01/13' .

(case 
when trunc(pay.dos) < '04/01/13' and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is not null THEN mfs.ruralfee*.8
when trunc(pay.dos) < '04/01/13' and bbd.procedure in ('A0435','A0436') and zip.ruralind is not null THEN mfs.ruralfee*bbd.units*.8
when trunc(pay.dos) < '04/01/13' and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is null THEN mfs.urbanfee*.8
when trunc(pay.dos) < '04/01/13' and bbd.procedure in ('A0435','A0436','A0425') and zip.ruralind is null THEN mfs.urbanfee*bbd.units*.8
when trunc(pay.dos) < '04/01/13' and bbd.procedure in ('A0425') and bbd.units > 17 and zip.ruralind is not null THEN (mfs.ruralgrmil1_17 *17 + (bbd.units-17)*mfs.ruralfee)*.8
when trunc(pay.dos) < '04/01/13' and bbd.procedure in ('A0425') and bbd.units <= 17 and zip.ruralind is not null THEN mfs.ruralgrmil1_17 * bbd.units*.8
when trunc(pay.dos) >= '04/01/13' and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is not null THEN mfs.ruralfee*.784
when trunc(pay.dos) >= '04/01/13' and bbd.procedure in ('A0435','A0436') and zip.ruralind is not null THEN mfs.ruralfee*bbd.units*.784
when trunc(pay.dos) >= '04/01/13' and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is null THEN mfs.urbanfee*.784
when trunc(pay.dos) >= '04/01/13' and bbd.procedure in ('A0435','A0436','A0425') and zip.ruralind is null THEN mfs.urbanfee*bbd.units*.784
when trunc(pay.dos) >= '04/01/13' and bbd.procedure in ('A0425') and bbd.units > 17 and zip.ruralind is not null THEN (mfs.ruralgrmil1_17 *17 + (bbd.units-17)*mfs.ruralfee)*.784
when trunc(pay.dos) >= '04/01/13' and bbd.procedure in ('A0425') and bbd.units <= 17 and zip.ruralind is not null THEN mfs.ruralgrmil1_17 * bbd.units*.784
end) MCRPortion,

Open in new window

0
Comment
Question by:jvera524
7 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39599268
If pay.dos is a date you may need to explicitly convert your string to a date:

to_date('04/01/13','MM/DD/YY')

If that isn't it, what problems is it causing?  Errors, incorrect results, what?
0
 

Author Comment

by:jvera524
ID: 39599283
pay.dos is a date field formatted as MM/DD/YY HH:MM:SS AM(PM)

I tried to_date(pay.dos,'MM/DD/YY') and this did not work either.  It states ORA-00198 I think.  I will get specifics.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39599288
Sorry but ORA-00198 is not a valid code.

At least based on the docs:
http://docs.oracle.com/cd/E11882_01/server.112/e17766/e0.htm

What is the exact code?

>>pay.dos is a date field formatted as MM/DD/YY HH:MM:SS AM(PM)

In Oracle dates don't have formats until they are displayed.  Then they are sort of converted to strings based on configuration settings.
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:jvera524
ID: 39599307
ORA-01898
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 39599329
Based on the error code:
http://ora-01898.ora-code.com/

There is a date format issue.  This is common when letting Oracle do implicit string to date conversions or messing up the format mask given the string you provided.

There is a simple example to generate that error here:
http://www.dbmotive.com/ora-01898-too-many-precision-specifiers/

select trunc(sysdate, 'DDDD') from dual;

Double check that pay.dos is a date and not a 'string' in date format.

From sqlplus:
desc pay.dos


If that isn't it, the syntax error may be elsewhere in the statement.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 150 total points
ID: 39599391
ORA-01898: too many precision specifiers
Cause:       While trying to truncate or round dates, extra data was found in the date format picture
Action:       Check the syntax of the date format picture and retry.

Please re-check carefully how you inserted the to_date() function into your code, I think you might have done something like this:

(case
when trunc(pay.dos) < to_date('04/01/13','MM/DD/YYYY') and ...

Note that the date string only contains 2 digits as the year,
but the "format picture" has 4 placeholders (YYYY) and is expecting digits for each one of those; this mismatch would produce that error message I believe

I would generally recommend always using 4 digit years, so the way I would go about solving this would be to change 13 to 2013 and ensure that all "format pictures" used YYYY
(case 
when trunc(pay.dos) < to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is not null THEN mfs.ruralfee*.8
when trunc(pay.dos) < to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0435','A0436') and zip.ruralind is not null THEN mfs.ruralfee*bbd.units*.8
when trunc(pay.dos) < to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is null THEN mfs.urbanfee*.8
when trunc(pay.dos) < to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0435','A0436','A0425') and zip.ruralind is null THEN mfs.urbanfee*bbd.units*.8
when trunc(pay.dos) < to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0425') and bbd.units > 17 and zip.ruralind is not null THEN (mfs.ruralgrmil1_17 *17 + (bbd.units-17)*mfs.ruralfee)*.8
when trunc(pay.dos) < to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0425') and bbd.units <= 17 and zip.ruralind is not null THEN mfs.ruralgrmil1_17 * bbd.units*.8
when trunc(pay.dos) >= to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is not null THEN mfs.ruralfee*.784
when trunc(pay.dos) >= to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0435','A0436') and zip.ruralind is not null THEN mfs.ruralfee*bbd.units*.784
when trunc(pay.dos) >= to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0431','A0430','A0426','A0427','A0428','A0429','A0433','A0434') and zip.ruralind is null THEN mfs.urbanfee*.784
when trunc(pay.dos) >= to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0435','A0436','A0425') and zip.ruralind is null THEN mfs.urbanfee*bbd.units*.784
when trunc(pay.dos) >= to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0425') and bbd.units > 17 and zip.ruralind is not null THEN (mfs.ruralgrmil1_17 *17 + (bbd.units-17)*mfs.ruralfee)*.784
when trunc(pay.dos) >= to_date('04/01/2013','MM/DD/YYYY') and bbd.procedure in ('A0425') and bbd.units <= 17 and zip.ruralind is not null THEN mfs.ruralgrmil1_17 * bbd.units*.784
end) MCRPortion,

Open in new window

By the way, it confuses us when we hear "xyz is a date field stored in format ..."e.g.
>>"pay.dos is a date field formatted as MM/DD/YY HH:MM:SS AM(PM)"

IF  pay.dos is a date field or a timestamp field, then "format" is not relevant to storage
(date/timestamp information is NOT stored in a format at all,
they are stored in a binary manner made-up of numbers,
"format" is only required by humans so that we can input them and read them at output)

IF pay.dos was a string field (e.g. varchar/varchar2) THEN format really does matter

& the confusion is we then don't know if we need to advise for a date or for a string.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39602806
Can you do a describe of the pay table so we can see the datatype of the dos attribute?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

929 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

11 Experts available now in Live!

Get 1:1 Help Now