Solved

Case Statement with less than or greater than date

Posted on 2013-10-24
7
696 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 49

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

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.  …
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 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…

691 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