Solved

Case Statement with less than or greater than date

Posted on 2013-10-24
7
648 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
'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 …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

777 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