Solved

Case Statement with less than or greater than date

Posted on 2013-10-24
7
679 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
[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
7 Comments
 
LVL 77

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 77

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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Comment

by:jvera524
ID: 39599307
ORA-01898
0
 
LVL 77

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

738 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